I need to group values in variable - and Arrive at the following Output
Input -
Header 1 | Header 2 | Header 3 |
---|---|---|
Sub 1 | 101 | 102 |
Sub 1 | 124 | 125 |
Sub 1 | 175 | 176 |
Sub 2 | 201 | 202 |
Sub 2 | 275 | 276 |
Sub 2 | 295 | 296 |
Output -
Header 1 | Header 2 |
---|---|
Sub 1 | 101, 102, 124, 125, 175, 176 |
Sub 2 | 201, 202, 275, 276, 295, 296 |
Thanks in Advance.
Hi madhu,
I am sure there are better ways to solve your problem. I will post my code anyway.
data have;
input header1 $ var1 var2;
cards;
Sub1 101 102
Sub1 124 125
Sub1 175 176
Sub2 201 202
Sub2 275 276
Sub2 295 296
;
run;
proc transpose data=have out=want21 ;
by header1;
var var1;
run;
proc transpose data=have out=want22 prefix=n;
by header1;
var var2;
run;
data want(keep=header1 header2) ;
merge want21 want22;
by header1;
array v(*) col1-col3 n1-n3;
call sortn(of v(*));
header2=catx(',',of v(*));
run;
and in a single pass:
data want (keep= header1 header2 compress=yes ) ;
do until( last.header1 ) ;
set have ;
by header1 ;
length header2 $32760 ;
header2 = catx( ',', header2, var1, var2 ) ;
end ;
run ;
Thank you Peter! have a nice weekend!
Thanks Peter and Linlin
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.