topic Re: how to combine multiple observations into one in SAS Procedures

how to combine multiple observations into one

chrisgo — Mon, 25 Nov 2013 23:59:54 GMT

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,

Re: how to combine multiple observations into one

JerryLeBreton — Tue, 26 Nov 2013 00:35:23 GMT

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.

Re: how to combine multiple observations into one

SteveNZ — Tue, 26 Nov 2013 00:49:12 GMT

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

Re: how to combine multiple observations into one

chrisgo — Tue, 26 Nov 2013 00:54:28 GMT

Program works. Thanks!