BookmarkSubscribeRSS Feed
Jade_SAS
Pyrite | Level 9

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;

3 REPLIES 3
Kurt_Bremser
Super User
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).

PGStats
Opal | Level 21

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.

PG

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to connect to databases in SAS Viya

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.

Discussion stats
  • 3 replies
  • 1053 views
  • 0 likes
  • 3 in conversation