Updating Values returned from a join only

[micksom]

Hi All,

I am trying to run the following piece of code. Basically, I am trying to update the selected values from the where statement with the internal_product value returned from the join.

However, I am getting the error Subquery evaluated to more than one row. I should probably add that each product is linked to an account number in another column. Do I have to do some sort of grouping?

I was wondering if anyone could help restructure my code, or explain what is going on that is incorrect.

Proc Sql;

update Test

Set Product = (select b.product from

Table1 (where = (type = 1 and month = October))

inner join Table2 on

a.criteria1 = b.criteria1 and

a.criteria2 = b.criteria2

)

;

quit

[micksom]

Do any SAS gurus know if such a feat is possible in proc sql?

Regards,

Michael

[Reeza]

micksom wrote:


I should probably add that each product is linked to an account number in another column. 

Have you tried joining on account number as well?

[micksom]

I don;t actually want to join on account number. The account number in table2 is different and not really linked to table1. I just really want to return the first value it finds based on criteria1 and criteria2.

[Reeza]

SQL doesn't really acknowledge order, so unless you have a way to identify the "first" record I'm not sure.

You could take the max or min if you don't actually care about order. The key is to get a query that returns only one value.

You'd also need quotes around October, I'm assuming it's simply a typo here.

Proc Sql;

update Test

Set Product = (select max(b.product) from

Table1 (where = (type = 1 and month = "October"))

inner join Table2 on

a.criteria1 = b.criteria1 and

a.criteria2 = b.criteria2

)

;

quit

[RW9]

Hi,

Can you post a bit of test data for each table, and what you expect out of it.