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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.