topic Re: Rolling up data in SAS Procedures

Rolling up data

jerry898969 — Tue, 14 Sep 2010 16:48:37 GMT

I'm trying to rollup rows of data into a single row with a variable that holds a value for each row that it rolled up. For example.

1 A
1 B
2 A
2 C
2 G
3 A

I need my end result to be
1 (A, B)
2 (A, C,G)
3 (A)

This is the code i'm using and I can get the single rows back but not the ID with multiple rows.

data z ;
set letter ;
length letters $20 ;
by id ;
letters='';
if first.id = 1 and last.id = 1 then do ;
letters=letter ;
output;
end ;
else if first.id = 0 and last.id = 0 then do ;
letters=letters || ' ' || letter ;
output ;
end ;
else if first.id = 0 and last.id = 1 then do ;
letters=letters || ' ' || letter ;
output ;
end ;
run ;

Thank you for any help.

Re: Rolling up data

sbb — Tue, 14 Sep 2010 16:57:49 GMT

You must code a RETAIN statement when you want to retain an assigned variable across DATA step iterations.

Scott Barry
SBBWorks, Inc.

Suggested Google advanced search argument, this topic / post:

data step retain statement site:sas.com

Re: Rolling up data

chang_y_chung_hotmail_com — Tue, 14 Sep 2010 17:14:49 GMT

Here is one way, which is not most efficient, but easy.

[pre]
/* test data */
data one;
input id letter $ @@;
cards;
1 A 1 B 2 A 2 C 2 G 3 A
;
run;

/* long to wide */
proc transpose data=one out=wide;
var letter;
by id notsorted;
run;

/* roll up */
data two;
set wide;
length letters $100; /* set it to large enough */
letters = catx(catx(",", of col:), "(", ")");
keep id letters;
run;

/* check */
proc print data=two noobs;
run;
/* on lst
id letters
1 (A,B)
2 (A,C,G)
3 (A)
*/
[/pre]

Re: Rolling up data

art297 — Tue, 14 Sep 2010 17:25:18 GMT

Your code needs more than just the retain statement, as you don't capture all conditions and your result wouldn't match that which you said you wanted. You could try something like:

data z (drop=letter);
retain letters;
set letter ;
length letters $20 ;
by id ;
letters=ifc(first.id,letter,catx(',',letters,letter));
if last.id then output;
run ;

HTH,
Art

Re: Rolling up data

jerry898969 — Tue, 14 Sep 2010 17:33:24 GMT

Thanks to everyone.

art297 you were right I wasn't capturing all the cases. I corrected that and added the retain statement and now I got it to work.

Thanks again to everyone for all your help pointing me in the right direction.