Merging

[Tal]

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;

[ChrisNZ]

Look at the UPDATE statement instead of the MERGE statement.

[hashman · Accepted solution]

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

[Tal]

thanks,  this worked