how to combine multiple observations into one

[chrisgo]

Hi guys, hope you could help me out on this problem:

I have this data set:

COL1               COL2

abc                    def

abc                    xyz

rst                    xyz

what I would want is to have this output:

Summary by COL1:

COL1          COL2

abc               def, xyz

rst                   xyz

Summary by COL2:

COL1          COL2

abc               def

abc,rst            xyz

Thanks and BR,

[JerryLeBreton · Accepted solution]

Try this...

proc transpose data=have out=nearly prefix=x;

   by col1;

   var col2;

run;

data want;

   set nearly;

   col2 = catx(',', of x:);

   keep col1 col2;

run;

... and repeat for the other way round.

[chrisgo]

Program works. Thanks!

[SteveNZ]

Hiya,

Try:

data have  ;

length col1 col2 $3 ;

infile datalines delimiter=',';

input col1 $ col2 $ ;

datalines;

abc,def

abc,xyz

rst,xyz

;

proc sort data = have ;

by col1 col2 ;

run ;

data want ;

set have ;

by col1 col2 ;

retain col1_summary ;

if first.col1 then col1_summary = catx(',',col2) ;

else col1_summary = catx(',',col1_summary,col2) ;

if last.col1 then output ;

run ;

Repeat above logic to get col2 summary.

cheers