Help using Base SAS procedures

Summarizing the data

Reply
Contributor
Posts: 57

Summarizing the data

Hi,

I have a dataset like

anlt lbno

aaa 1
aaa 2
aaa 4
bbb 2
bbb 3
bbb 4
ccc 1
ccc 2
ccc 3
ccc 4

I want it to be printed in excel as

anlt lbno

aaa 1, 2, 4
bbb 2, 3, 4
ccc 1, 2, 3, 4

Thanks in advance,
Sandhya.
Super Contributor
Super Contributor
Posts: 3,174

Re: Summarizing the data

Use PROC TRANSPOSE to create a new SAS file and then use a DATA step to concatenate (using CATX function) the transposed values.

Scott Barry
SBBWorks, Inc.
Respected Advisor
Posts: 3,887

Re: Summarizing the data

The solution below uses SAS/ACCESS for PC Files. In case there is no license for this module: Create a csv file.

HTH
Patrick

data have;
infile datalines;
input anlt $ lbno;
datalines;
aaa 1
aaa 2
aaa 4
bbb 2
bbb 3
bbb 4
ccc 1
ccc 2
ccc 3
ccc 4
;
run;

libname outxls excel 'c:\temp\want.xls' ;
data outxls.wantsheet(keep=anlt lbno_str);
set have;
by anlt;
retain lbno_str;
length lbno_str $ 100;
if first.anlt then
lbno_str=cats(lbno);
else
lbno_str=cats(lbno_str,',',lbno);
if last.anlt then
output;
run;

libname outxls;
Contributor
Posts: 57

Re: Summarizing the data

Thank you. It was very helpful.

Sandhya.
Ask a Question
Discussion stats
  • 3 replies
  • 260 views
  • 0 likes
  • 3 in conversation