Combining the strings of unique items through a Data Step into one string per ID?

[PSL]

Hi,

I am trying to get the below list to show with each ID only once with all multiple rows of strings combined

From:

code	string
1	a
1	b
2	e
3	g
3	h
4	i
5	k
5	l
5	m

The code I am using to get the initial data is

data work.blah;  
   infile datalines missover; 
   input ID $ string $;  
   datalines; 
1 a
1 b
2 e
3 g
3 h
4 i
5 k
5 l
5 m
;                         

To:

code	list
1	a, b
2	e
3	g, h
4	i
5	k, l, m

The code I am using is

data strings;
 length list $100.;
 set work.blah end=eof;
 retain list;
 if n=1 then list=string;
 else list = cats(list,", ",string);
 if eof then output;
run;

Unfortunately the result I am getting is

list	ID	string	n
,a,b,e,g,h,i,k,l,m	5	m	.

My questions are:

1) How can I get the unique IDs, and not just the last one (5)

2) How can I get the first string to start and not the ", "

3) How can I drop the 'string' and 'n' columns

For reference I am using SAS EG 6.1 M1

[Reeza]

There’s two methods here with a fully worked example. 

https://gist.github.com/statgeek/d583cfa992bf56da51d435165b07e96a

[PSL]

Thanks for the answer, could you explain the difference between the two solutions? Would there be any reason to choose the Data Step over the Proc SQL or vice versa?

[Reeza]

If you have really big data and want only one pass with the data, the data step is a faster option. Or if you're calculating multiple things in the same data step you may want that approach. 

---

@PSL wrote:
Thanks for the answer, could you explain the difference between the two solutions? Would there be any reason to choose the Data Step over the Proc SQL or vice versa?

---

[PSL]

Understood, thank you very much

[novinosrin]

data work.blah;  
   infile datalines missover; 
   input ID $ string $;  
   datalines; 
1 a
1 b
2 e
3 g
3 h
4 i
5 k
5 l
5 m
;   

data want;
set blah;
by id;
length list $50;
retain list ;
if first.id then list=string;
else list=catx(',',list,string);
if last.id;
drop string;
run;

[PSL]

Thanks, that worked perfect