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.