03-13-2017 02:19 PM
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:
SET InstituteID = LocationID
FROM TableA as f
LEFT JOIN TableB as d ON f.MainLocation = d.LocationName
WHERE InstituteID is NULL
03-13-2017 02:28 PM
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).
03-13-2017 04:51 PM
Does this update query work in SAS/SQL? The documentation doesn't include the possibility of a from clause in an update query.