topic Group Values in a variable in SAS Procedures

Group Values in a variable

madhu — Fri, 11 Nov 2011 16:24:13 GMT

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.

Re: Group Values in a variable

Linlin — Fri, 11 Nov 2011 17:56:53 GMT

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;

Group Values in a variable

Peter_C — Fri, 11 Nov 2011 18:10:15 GMT

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 ;

Re: Group Values in a variable

Linlin — Fri, 11 Nov 2011 18:17:40 GMT

Thank you Peter! have a nice weekend!

Group Values in a variable

madhu — Fri, 11 Nov 2011 18:44:13 GMT

Thanks Peter and Linlin