topic Re: Updating Values returned from a join only in SAS Procedures

Updating Values returned from a join only

micksom — Mon, 24 Nov 2014 19:33:22 GMT

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

Re: Updating Values returned from a join only

micksom — Tue, 25 Nov 2014 07:29:39 GMT

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

Regards,

Michael

Re: Updating Values returned from a join only

Reeza — Tue, 25 Nov 2014 07:32:05 GMT

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?

Re: Updating Values returned from a join only

micksom — Tue, 25 Nov 2014 07:36:31 GMT

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.

Re: Updating Values returned from a join only

Reeza — Tue, 25 Nov 2014 08:46:55 GMT

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

Re: Updating Values returned from a join only

RW9 — Tue, 25 Nov 2014 09:36:49 GMT

Hi,

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