LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (July 2010, week 3)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
Date:         Wed, 21 Jul 2010 03:49:50 -0400
Reply-To:     Dee Karmaoui <dkarmaoui@BUSINESSDECISION.CO.UK>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Dee Karmaoui <dkarmaoui@BUSINESSDECISION.CO.UK>
Subject:      Updating tables being used by others

One of my colleagues sent this answer through - which works perfectly. The only glitch being that if the table is open in edit mode, the code hangs until it's switched to browse mode. Otherwise:

/*Prep the data*/ proc sql; create table TEST1 as select NAME, SEX, AGE, HEIGHT * 1000 as HEIGHT, WEIGHT * 1000 as WEIGHT from SASHELP.CLASS where SEX="M"; create table TEST2 as select * from SASHELP.CLASS; quit;

/*Run the update*/ proc sql; update WORK.TEST2 set HEIGHT = CASE WHEN (select HEIGHT from WORK.TEST1 where TEST1.NAME=TEST2.NAME AND TEST1.SEX=TEST2.SEX AND TEST1.AGE=TEST2.AGE)^=. THEN (select HEIGHT from WORK.TEST1 where TEST1.NAME=TEST2.NAME AND TEST1.SEX=TEST2.SEX AND TEST1.AGE=TEST2.AGE) else HEIGHT end, WEIGHT = CASE WHEN (select WEIGHT from WORK.TEST1 where TEST1.NAME=TEST2.NAME AND TEST1.SEX=TEST2.SEX AND TEST1.AGE=TEST2.AGE)^=. THEN (select WEIGHT from WORK.TEST1 where TEST1.NAME=TEST2.NAME AND TEST1.SEX=TEST2.SEX AND TEST1.AGE=TEST2.AGE) else WEIGHT end ; quit;

For characters, the ^=. is replaced by ^="", and of course the conditions within the brackets can be as short or as long as you want them to be! Thanks everyone for your suggestions by the way, I ended up using cntllev=rec (which I didn't know about) for an append as well.


Back to: Top of message | Previous page | Main SAS-L page