topic Re: How to convert the SQL update statement to SAS update statement? in SAS Data Management

How to convert the SQL update statement to SAS update statement?

Jade_SAS — Mon, 13 Mar 2017 18:19:02 GMT

Hi All,

I have trouble to convert the following SQL update statement to SAS Update statement, could you please help me with this? Thank you!

SQL update statement:

Proc sql;

update TableA

SET InstituteID = LocationID

FROM TableA as f

LEFT JOIN TableB as d ON f.MainLocation = d.LocationName

WHERE InstituteID is NULL

;

Quit;

Re: How to convert the SQL update statement to SAS update statement?

Kurt_Bremser — Mon, 13 Mar 2017 18:28:27 GMT

proc sort data=tablea;
by mainlocation;
run;

proc sort data=tableb;
by locationname;
run;

data tablea;
merge
  tablea (in=a)
  tableb (in=b rename=(locationname=mainlocation))
;
by mainlocation;
if a;
if b and missing(instituteid ) then instituteid = locationid;
drop locationid;
run;

The success depends on the relationship between the tables with regards to the key variable (1:1, 1:n, m:1, m:n).

Re: How to convert the SQL update statement to SAS update statement?

Jade_SAS — Mon, 13 Mar 2017 18:47:05 GMT

Thanks!

Re: How to convert the SQL update statement to SAS update statement?

PGStats — Mon, 13 Mar 2017 20:51:33 GMT

Hi @Jade_SAS,

Does this update query work in SAS/SQL? The documentation doesn't include the possibility of a from clause in an update query.