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.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.