topic Re: Many-to-many merge in New SAS User

Many-to-many merge

bkq32 — Sat, 01 Aug 2020 00:48:07 GMT

My apologies if this has already been answered on here and I didn't see it, but I have two tables that I'd like to do a many-to-many merge on.

data one;                  
  input id $ color $ animal $ farm $;      
datalines;                 
a blue . .
a black . .
d green . .
;                          
run;
 
data two;                  
  input id $ animal $;      
datalines;                 
a turtle
a snake
b cat
b cow
c bird
c horse
;      
run; 

data want;                  
  input id $ color $ animal $ farm $;      
datalines;  
a blue turtle .
a blue snake .
a black turtle .
a black snake .
b . cat .
b . cow .
c . bird .
c . horse .
d green . .
;      
run;

I've tried the following, but it doesn't yield the same results as the "want" table.

proc sql;
 create table both
  as select * from one as a
   full join two as b
    on a.id = b.id;
quit;

Any suggestions would be greatly appreciated.

Re: Many-to-many merge

Shmuel — Sat, 01 Aug 2020 00:43:01 GMT

1) Merging many to many, will create line

a black snake .

and not

b black snake .

as shown in wanted data, because snake has ID=a.

2) Your code need a slight change to work:

proc sql;
 create table both
  as select a.* , b.*
      from one as a
     full join two as b
    on a.id = b.id;
quit;

Re: Many-to-many merge

bkq32 — Sat, 01 Aug 2020 00:52:39 GMT

Thank you, you're right. That was a typo - I edited the post.

When I run your code, none of the records where id = "b" or id = "c" are there, and all of the ANIMAL values are blank.

Re: Many-to-many merge

PGStats — Sat, 01 Aug 2020 01:41:46 GMT

You can combine same name columns with COALESCE

proc sql;
 create table both as 
 select 
	coalesce(a.id. b.id) as id,
	color,
	coalesce(a.animal, b.animal) as animal,
	farm
 from 
	one as a full join 
	two as b on a.id = b.id;
quit;

Note that if you have nonmissing animal values in both tables, you will only get the value from table one.

Re: Many-to-many merge

bkq32 — Sat, 01 Aug 2020 01:45:30 GMT

That worked, thank you!