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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.