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.
Are ANY of your MAPCD values in dataset2 ever duplicated?
yes there are several duplicates along with more variables
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;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.