SAS PROC SQL UPDATE

[terrperr]

I have not been able to find the correct syntax for updating multiple fields in my 'master' data set with information from my 'infile' data set. I tried this in vain:

PROC SQL;
UPDATE MASTER
SET CITY = IN.CITY
,AMT = IN.AMT
,etc. more fields
FROM MASTER MS
LEFT JOIN
INFILE IN
ON MS.ID = IN.CUST_ID
AND MS.NAME = IN.CUST_NAME
AND etc...more fields
;
QUIT;

The join variables in INFILE are not the same name as those in MASTER and the user doesn't want the names changed. That prohibits a data step MERGE solution and, besides, isn't this what PROC SQL is here for? Needless to say I've Googled hard and long. Thanks for anyone's help.

 

[mdieckman]

I've read someone (and this might not be accurate) that Proc SQL update only works with one table.

The example given here also only shows one table:

http://www.tau.ac.il/cc/pages/docs/sas8/proc/zlsqlexp.htm

Perhaps you might try case statements instead. Do a left outer join on the table you are trying to update, use your join fields, and update the value only where it is populated on the other table. ' ' is blank for char and . is blank for numberics.

DATA X;
X = '1';
Y = 2;
Z = 3;
RUN;

DATA Y;
X = '3';
Y = 2;
Z = 1;
RUN;

PROC SQL;
     CREATE TABLE Z   AS
     (select 

	CASE WHEN (b.x = ' ') THEN (A.x) else b.x END as X
,   a.y
,   CASE WHEN (b.z =.) THEN (A.z) else b.z END as Z

     FROM X       	as A

left outer join Y  		as B

on 
A.Y = B.Y

     );
QUIT;

 

[mdieckman]

Oops, sorry for the typos.

 

[klaz2002]

Why not use the rename dataset option? This way you don't need to change any name on the master dataset.

I have found (and you may need SAS 9.2) that the HASH element can update in place.

  data lib.master(drop=rc);
 

       declare hash myhash(dataset: 'in.yourdata(rename=(cust_id=id cust_name=name))');*** ADD AS MANY VARNAMES AS YOU NEED ***;
       rc = myhash.definekey('ID','NAME');
       rc = myhash.definedata('CITY','DATAVAR2','DATAVAR3');
       rc = myhash.definedone();

     do until(eof);
       set lib.master end = eof;
       call missing(CITY);
       call missing(DATAVAR2);
       call missing(DATAVAR3);
       rc = myhash.find();
       
       output;
     end;
   stop;
   run;

This will update those records that meet the 'key'.

Klaz

 

[mrJET]

Assuming that CUST_ID and ID is enough for identifying unique rows in both tables the below SQL works fine.
[tt]
PROC SQL;
UPDATE MASTER
SET CITY = (SELECT INFILE.CITY
FROM INFILE
WHERE INFILE.CUST_ID = MASTER.ID)
,AMT = (SELECT INFILE.AMT
FROM INFILE
WHERE INFILE.CUST_ID = MASTER.ID)
WHERE EXISTS (SELECT 1
FROM INFILE
WHERE INFILE.CUST_ID = MASTER.ID)
;
QUIT;

[/tt]