Transforming data

[Sajid01]

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.

[novinosrin]

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]

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 · Accepted solution]

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]

@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]

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]

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.