Help using Base SAS procedures

Rolling up data

Reply
Super Contributor
Posts: 400

Rolling up data

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.
Super Contributor
Super Contributor
Posts: 3,174

Re: Rolling up data

Posted in reply to jerry898969
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
Regular Contributor
Posts: 241

Re: Rolling up data

Posted in reply to jerry898969
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 colSmiley Happy, "(", ")");
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]
PROC Star
Posts: 7,474

Re: Rolling up data

Posted in reply to jerry898969
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
Super Contributor
Posts: 400

Re: Rolling up data

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.
Ask a Question
Discussion stats
  • 4 replies
  • 2786 views
  • 0 likes
  • 4 in conversation