BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Sajid01
Meteorite | Level 14

I have the following dataset :

data test;
input user $ group $;
datalines;
user1 group1
user1 group2
user1 group4
user2 group2
user2 group3
user2 group5
user3 group1
user3 group4
user3 group4
user4 group2
user4 group5
user4 group6
;
run;
Want a new two column dataset output like this

user1 group1,group2,group4
user2 group2,group3,group5
user3 group1,group4,group6
user4 group2,group5,group6

Can anybody help.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Satish_Parida
Lapis Lazuli | Level 10

Try this.

 

data test;
input user $ 	group $;
datalines;
user1 group1
user1 group2
user1 group4
user2 group2
user2 group3
user2 group5
user3 group1
user3 group4
user3 group4
user4 group2
user4 group5
user4 group6
;
run;

proc sort data=test;
by user;
run;

data want(rename=(group1=group));
length group1 $200;  /*Increase the length if required*/
set test;
by user;
retain group1;
if first.user then group1=group;
else group1=cats(group1,',',group);
if last.user then output;
drop group;
run;

Please let us know if it worked for you.

View solution in original post

6 REPLIES 6
novinosrin
Tourmaline | Level 20


data test;
input user $ 	group $;
datalines;
user1 group1
user1 group2
user1 group4
user2 group2
user2 group3
user2 group5
user3 group1
user3 group4
user3 group4
user4 group2
user4 group5
user4 group6
;
run;

proc transpose data=test out=want(drop=_name_) ;
by user;
var  group;
run;

Then Concatenate with CATX 

Reeza
Super User

If you actually want them combined into a single variable see the two different examples here. It helps if you run them to see the input/output.

 

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

 

 

Satish_Parida
Lapis Lazuli | Level 10

Try this.

 

data test;
input user $ 	group $;
datalines;
user1 group1
user1 group2
user1 group4
user2 group2
user2 group3
user2 group5
user3 group1
user3 group4
user3 group4
user4 group2
user4 group5
user4 group6
;
run;

proc sort data=test;
by user;
run;

data want(rename=(group1=group));
length group1 $200;  /*Increase the length if required*/
set test;
by user;
retain group1;
if first.user then group1=group;
else group1=cats(group1,',',group);
if last.user then output;
drop group;
run;

Please let us know if it worked for you.

Kurt_Bremser
Super User

@Satish_Parida: you can simplify your code by using catx:

data want (rename=(group1=group));
length group1 $200;  /*Increase the length if required*/
set test;
by user;
retain group1;
if first.user then group1 = '';
group1 = catx(',',group1,group);
if last.user;
drop group;
run;
Sajid01
Meteorite | Level 14

Thanks everybody for the help.

All solutions worked.
Accepting a solution meant making a difficult choice.

In reality all posts are solution.

Thanks once again.

ballardw
Super User

And just to be consistent since I have been asking this question for essentially identical requests:

 

How is that data set to actually be used? For many purposes having a variable with multiple values in it is extremely difficult to do anything in the way of analysis and can be problematic for reports.

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 6 replies
  • 1086 views
  • 0 likes
  • 6 in conversation