Conditional merging of two datasets

[jazzblues24]

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.

[LinusH]

Try SQL full/left join, and use the coalesce() function on your tos1&2 columns (and mspcd if you are doing a full join).

[ballardw]

Are ANY of your MAPCD values in dataset2 ever duplicated?

[jazzblues24]

yes there are several duplicates along with more variables

[jazzblues24]

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;

[Astounding]

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.