Rolling up data

[jerry898969]

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.

[sbb]

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

[chang_y_chung_hotmail_com]

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]

[art297]

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

[jerry898969]

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.