Hi, i have 2 tables (one has the entire population and the other some updates which i merge and create another variable "score"
if the person is white and is not in table b then score=score1 otherwise score=score2
table a:
city dob name race score1
---------------------------------
nyc 22jan2001 tom white 66
paris 11feb1994 piere white 33
berlin 11feb1994 mark white 36
milan 22jan1996 Mike black 30
milan 22jan1996 Moe black 40
table b:
city dob name race score2
---------------------------------
nyc 22jan2001 tom white 11
paris 11feb1994 piere white 17
milan 22jan1996 Mike black 35
milan 22jan1996 Moe black 37
the final table will have to look like this:
nyc 22jan2001 tom white 11
paris 11feb1994 piere white 17
berlin 11feb1994 mark white 36
milan 22jan1996 Mike black 35
milan 22jan1996 Moe black 37
is there a better, more efficient way/quicker method i can use to achieve this?
All i could think of was this below but these tables are huge so not sure this is a good approach so if anyone can think of a better approach please share it with me. Thanks
key=cats(city,dob,name,race);
data final1 final2;
merge tablea(in=a) tableb(in=b);
by key;
if a and not b and race='white' then do;score=score1;output final1;end;
else do;score=score2; output final2;end;run;
data final(drop= key score1 score2);
set final1 final2;run;
@Tal:
Sure, you can do more efficiently - id est, without needing to sort, e.g.:
data a ;
input city $ dob :date. name $ race $ score1 ;
cards ;
nyc 22jan2001 tom white 66
paris 11feb1994 piere white 33
berlin 11feb1994 mark white 36
milan 22jan1996 mike black 30
milan 22jan1996 moe black 40
;
run ;
data b ;
input city $ dob :date. name $ race $ score2 ;
cards ;
nyc 22jan2001 tom white 11
paris 11feb1994 piere white 17
milan 22jan1996 mike black 35
milan 22jan1996 moe black 37
;
run ;
data want ;
if _n_ = 1 then do ;
dcl hash h (dataset: "b (rename=score2=score)") ;
h.definekey ("city","dob","name","race") ;
h.definedata ("score") ;
h.definedone () ;
end ;
set a (rename=score1=score) ;
_iorc_ = h.find() ;
format dob yymmdd10. ;
run ;
Kind regards
Paul D.
Look at the UPDATE statement instead of the MERGE statement.
@Tal:
Sure, you can do more efficiently - id est, without needing to sort, e.g.:
data a ;
input city $ dob :date. name $ race $ score1 ;
cards ;
nyc 22jan2001 tom white 66
paris 11feb1994 piere white 33
berlin 11feb1994 mark white 36
milan 22jan1996 mike black 30
milan 22jan1996 moe black 40
;
run ;
data b ;
input city $ dob :date. name $ race $ score2 ;
cards ;
nyc 22jan2001 tom white 11
paris 11feb1994 piere white 17
milan 22jan1996 mike black 35
milan 22jan1996 moe black 37
;
run ;
data want ;
if _n_ = 1 then do ;
dcl hash h (dataset: "b (rename=score2=score)") ;
h.definekey ("city","dob","name","race") ;
h.definedata ("score") ;
h.definedone () ;
end ;
set a (rename=score1=score) ;
_iorc_ = h.find() ;
format dob yymmdd10. ;
run ;
Kind regards
Paul D.
thanks, this worked
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.