Many-to-many merge

[bkq32]

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.

[Shmuel]

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;

[bkq32]

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.

[PGStats · Accepted solution]

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.

[bkq32]

That worked, thank you!