Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SAS PROC SQL UPDATE

Status
Not open for further replies.

terrperr

Programmer
Jan 17, 2003
1
US
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.




 
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:


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.

Code:
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;
 
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.

Code:
  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
 
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]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top