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;
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).
Thanks!
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.