BookmarkSubscribeRSS Feed
PSL
Calcite | Level 5 PSL
Calcite | Level 5

Hi,

 

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

 

From:

 

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

 

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:

codelist
1a, b
2e
3g, h
4i
5k, 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

listIDstringn
,a,b,e,g,h,i,k,l,m5m .

 

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

 

 

6 REPLIES 6
PSL
Calcite | Level 5 PSL
Calcite | Level 5
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
Super User

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
Calcite | Level 5 PSL
Calcite | Level 5
Understood, thank you very much
novinosrin
Tourmaline | Level 20
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;

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!

How to Concatenate Values

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.

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
  • 6 replies
  • 344 views
  • 0 likes
  • 3 in conversation