topic Re: Conditional merging of two datasets in SAS Programming

Conditional merging of two datasets

jazzblues24 — Mon, 08 Feb 2016 21:36:16 GMT

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.

Re: Conditional merging of two datasets

LinusH — Mon, 08 Feb 2016 21:44:26 GMT

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

Re: Conditional merging of two datasets

ballardw — Mon, 08 Feb 2016 22:54:59 GMT

Are ANY of your MAPCD values in dataset2 ever duplicated?

Re: Conditional merging of two datasets

jazzblues24 — Mon, 08 Feb 2016 23:54:51 GMT

yes there are several duplicates along with more variables

Re: Conditional merging of two datasets

jazzblues24 — Mon, 08 Feb 2016 23:56:56 GMT

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;

Re: Conditional merging of two datasets

Astounding — Tue, 09 Feb 2016 00:11:42 GMT

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.