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.