×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

SAS PROC SQL UPDATE

SAS PROC SQL UPDATE

SAS PROC SQL UPDATE

(OP)
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.



   
 

RE: SAS PROC SQL UPDATE

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.

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;

RE: SAS PROC SQL UPDATE

Oops, sorry for the typos.

RE: SAS PROC SQL UPDATE

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

RE: SAS PROC SQL UPDATE

Assuming that CUST_ID and ID is enough for identifying unique rows in both tables the below SQL works fine.  

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;                    

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close