topic Re: Merging in SAS Programming

Merging

Tal — Wed, 16 Oct 2019 02:42:06 GMT

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;

Re: Merging

ChrisNZ — Wed, 16 Oct 2019 03:03:18 GMT

Look at the UPDATE statement instead of the MERGE statement.

Re: Merging

hashman — Wed, 16 Oct 2019 04:56:57 GMT

@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.

Re: Merging

Tal — Wed, 04 Dec 2019 00:14:54 GMT

thanks, this worked