topic Re: many to many merge using proc sql or datasteps? in SAS Procedures

many to many merge using proc sql or datasteps?

michtka — Wed, 17 Apr 2013 13:17:39 GMT

Hi all,'ve got the next dataset (have):

data have;

length subjid 8 color1 color2 $20;

input subjid $ color1 $ color2;

datalines;

1 blue blue

2 red grey

3 yellow blue

4 green green

5 black yellow

6 white pink

7 purple yellow

;

run;

I am interested to create a final dataset (want) like this:

color2 other

blue blue

grey

green green

yellow yellow

pink

as you can see I am interested to know which color in (color2) has been assigned to (color1), and which color in (color2) cant be assigned to (color 1) because are different (blanck values)...in this example would be grey and pink.

Thanks in advance,

Re: many to many merge using proc sql or datasteps?

michtka — Wed, 17 Apr 2013 16:25:18 GMT

maybe using arrays...please some help.

Thanks.

Re: many to many merge using proc sql or datasteps?

Haikuo — Wed, 17 Apr 2013 18:16:40 GMT

How about this:

data have;

input (subjid color1 color2) (:$);

datalines;

1 blue blue

2 red grey

3 yellow blue

4 green green

5 black yellow

6 white pink

7 purple yellow

;

proc sql;

select distinct a.color2, b.color1 as other

from have a

left join

have b

on a.color2=b.color1

;

quit;

Haikuo

Re: many to many merge using proc sql or datasteps?

michtka — Wed, 17 Apr 2013 22:32:00 GMT

Brilliant Hai.Kuo

Many thanks.

Cheers,