## how to combine multiple observations into one

Solved
Occasional Contributor
Posts: 13

# how to combine multiple observations into one

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,

Accepted Solutions
Solution
‎11-25-2013 07:35 PM
Frequent Contributor
Posts: 88

## Re: how to combine multiple observations into one

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.

All Replies
Solution
‎11-25-2013 07:35 PM
Frequent Contributor
Posts: 88

## Re: how to combine multiple observations into one

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.

Occasional Contributor
Posts: 13

## Re: how to combine multiple observations into one

Program works. Thanks!

Frequent Contributor
Posts: 87

## Re: how to combine multiple observations into one

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

🔒 This topic is solved and locked.