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-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 695 views
  • 0 likes
  • 3 in conversation