topic Re: Combining the strings of unique items through a Data Step into one string per ID? in SAS Programming

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

PSL — Thu, 05 Jul 2018 03:56:00 GMT

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

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

Reeza — Thu, 05 Jul 2018 04:30:44 GMT

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

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

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

novinosrin — Thu, 05 Jul 2018 04:37:16 GMT

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;

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

PSL — Thu, 05 Jul 2018 20:44:46 GMT

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?

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

PSL — Thu, 05 Jul 2018 20:57:27 GMT

Thanks, that worked perfect

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

Reeza — Thu, 05 Jul 2018 21:00:09 GMT

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?

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

PSL — Thu, 05 Jul 2018 21:00:48 GMT

Understood, thank you very much