Help using Base SAS procedures

how to combine multiple observations into one

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 13
Accepted Solution

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: 85

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 xSmiley Happy;

   keep col1 col2;

run;

... and repeat for the other way round.


View solution in original post


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

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 xSmiley Happy;

   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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 243 views
  • 1 like
  • 3 in conversation