topic proc sql update statement in SAS Procedures

proc sql update statement

PhatRam33 — Thu, 12 Mar 2015 21:25:20 GMT

Attempting to update a field in my table that requires a join from another table as such:

proc sql;

update work.table_01 as t1

set t1.region = 'North'

from work.table_01 as t1 inner join work.table_02 as t2 on t1.ID = t2.ID

where

t2.state in ('PA');

quit;

Received errors in the from clause and also read that SAS does not recgonize joins in the update statement. Is there another way to write this proc sql or maybe adding a sub query (not sure where in the code) to get this to work? If someone can provide an example based on above, that would be great as well.

Thanks in advance

Re: proc sql update statement

PGStats — Thu, 12 Mar 2015 22:18:41 GMT

Not always easy to do UPDATEs in SAS/SQL. But in this case, it is quite simple:

proc sql;

update table_01

set region = 'North'

where ID in (select ID from table_02 where state in ('PA'));

quit;

Re: proc sql update statement

PhatRam33 — Fri, 13 Mar 2015 14:29:01 GMT

That did the trick! Thank you very much =D