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.
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.
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
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
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.
@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;
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.
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.
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.
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.
Ready to level-up your skills? Choose your own adventure.