How to retain data from one row to the next

[techsassy]

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!!

[ballardw · Accepted solution]

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.

[techsassy]

Thanks so much for the solution, as well as the CAT function encouragement!