BookmarkSubscribeRSS Feed
jerry898969
Pyrite | Level 9
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.
4 REPLIES 4
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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
Obsidian | Level 7
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
Opal | Level 21
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
Pyrite | Level 9
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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 4055 views
  • 0 likes
  • 4 in conversation