BookmarkSubscribeRSS Feed
jazzblues24
Calcite | Level 5

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.

5 REPLIES 5
LinusH
Tourmaline | Level 20
Try SQL full/left join, and use the coalesce() function on your tos1&2 columns (and mspcd if you are doing a full join).
Data never sleeps
ballardw
Super User

Are ANY of your MAPCD values in dataset2 ever duplicated?

 

jazzblues24
Calcite | Level 5

yes there are several duplicates along with more variables

jazzblues24
Calcite | Level 5

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
PROC Star

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.

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 994 views
  • 0 likes
  • 4 in conversation