DATA Step, Macro, Functions and more

Conditional merging of two datasets

Reply
Occasional Contributor
Posts: 5

Conditional merging of two datasets

Hi all,

 

I'm struggling with merging two tables sucessfully.

 

Table 1 looks like this :

mapcd   tos1       tos2

300        facop    surg

355        anc       mater

467        anc       hh/hpc

 

 

Table 2 looks like this:

mapcd   tos1       tos2

274        facip     medic

275        facip     mater

300        

355             

 

I want my resulting table to look like this;

mapcd   tos1       tos2

274        facip     medic

275        facip     mater

300        facop    surg

355        anc       mater

    

 

essentially, i want to update table 2 with what's in table 1 based on the common variable "mapcd". I only want this done where tos1 and tos2 are missing in table 2 and also don't want to lose whats already in table 2.

 

I'm aware that the variables are the same in both tables. Do I one to rename them in one table in order for it?

 

I've tried a simple merge in a data step, update in proc sql and none are working for me.

 

Any help would be appreciated.

Super User
Posts: 5,260

Re: Conditional merging of two datasets

Try SQL full/left join, and use the coalesce() function on your tos1&2 columns (and mspcd if you are doing a full join).
Data never sleeps
Super User
Posts: 10,527

Re: Conditional merging of two datasets

Are ANY of your MAPCD values in dataset2 ever duplicated?

 

Occasional Contributor
Posts: 5

Re: Conditional merging of two datasets

yes there are several duplicates along with more variables

Occasional Contributor
Posts: 5

Re: Conditional merging of two datasets

Thanks. I got it to work. Just in case anyone else refers to this in the future, here's what I used

 


proc sql;
create table want as
select *,
coalesce (a.tos1,b.tos1) as tostos1,
coalesce (a.tos2,b.tos2) as tostos2
from out.table2 A left join out.table1 B
on a.mapcd = b.mapcd;
quit;

Super User
Posts: 5,090

Re: Conditional merging of two datasets

Is this not a straightforward DATA step?  Assuming your data sets are already sorted:

 

data want;

merge table2 (in=in2) table1 (in=in1);

by mapcd;

if in2;

run;

 

Just be sure to mention TABLE2 first, and any TABLE1 values will overwrite the TABLE2 values.  This also depends on the situation being a 1-to-1 merge, not many-to-1.

Ask a Question
Discussion stats
  • 5 replies
  • 226 views
  • 0 likes
  • 4 in conversation