Hi all, I'm struggling with carrying data from one row to the next using a method that I'm pretty sure has worked in the past. I simply want to carry the 'name' from one row to the next, within each table subset, and end up with a last.table record which has all of the names in that table concatenated into one string. I also want to make use of the 'retain' function.
This is my current data:
name | table | rowcat |
Riley | t1 | |
Henderson | t1 | |
MacDonald | t1 | |
Cook | t1 | |
DuPage | t2 | |
Samoa | t2 |
This is what I want:
name | table | rowcat |
Riley | t1 | Riley, |
Henderson | t1 | Riley, Henderson, |
MacDonald | t1 | Riley, Henderson, MacDonald, |
Cook | t1 | Riley, Henderson, MacDonald, Cook, |
DuPage | t2 | DuPage, |
Samoa | t2 | DuPage, Samoa, |
Can anyone help me understand why this code isn't working?
data have;
length name $ 12 table $ 2 rowcat $ 200;
input name $ table $;
datalines;
Riley t1
Henderson t1
MacDonald t1
Cook t1
DuPage t2
Samoa t2
;
run;
proc sort data=have; by table; run;
data want;
set have;
by table;
if first.table then do;
rowcat = ' ';
end;
rowcat = strip(strip(name)||", "||strip(rowcat));
retain rowcat;
run;
Thanks!!
This makes the shown desired output:
data have; length name $ 12 table $ 2 ; input name $ table $; datalines; Riley t1 Henderson t1 MacDonald t1 Cook t1 DuPage t2 Samoa t2 ; run; proc sort data=have; by table; run; data want; set have; by table; length rowcat $ 200; retain rowcat; if first.table then do; rowcat = ' '; end; rowcat = catx(', ',rowcat,name); run;
You had a moderately subtle technical issue. If you Retain a variable that is on the input data set it gets reread from the source data set, in your case missing. So nothing would be kept from the previous record.
Note that I removed the ROWCAT from the first data set.
Catx, and the other CAT functions are bit cleaner than using strip(variable)|| constantly. The Catx function places the first parameter between values when both are not missing. So unless you actually want multiple commas adjacent if a Name happened to be missing is a more compact code. CATX automatically removes trailing blanks.
This makes the shown desired output:
data have; length name $ 12 table $ 2 ; input name $ table $; datalines; Riley t1 Henderson t1 MacDonald t1 Cook t1 DuPage t2 Samoa t2 ; run; proc sort data=have; by table; run; data want; set have; by table; length rowcat $ 200; retain rowcat; if first.table then do; rowcat = ' '; end; rowcat = catx(', ',rowcat,name); run;
You had a moderately subtle technical issue. If you Retain a variable that is on the input data set it gets reread from the source data set, in your case missing. So nothing would be kept from the previous record.
Note that I removed the ROWCAT from the first data set.
Catx, and the other CAT functions are bit cleaner than using strip(variable)|| constantly. The Catx function places the first parameter between values when both are not missing. So unless you actually want multiple commas adjacent if a Name happened to be missing is a more compact code. CATX automatically removes trailing blanks.
Thanks so much for the solution, as well as the CAT function encouragement!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.