topic Re: Transforming data in SAS Programming

Transforming data

Sajid01 — Wed, 29 Jan 2020 02:58:37 GMT

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.

Re: Transforming data

novinosrin — Wed, 29 Jan 2020 03:38:07 GMT



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

Re: Transforming data

Reeza — Wed, 29 Jan 2020 03:23:00 GMT

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

Re: Transforming data

Satish_Parida — Wed, 29 Jan 2020 07:28:29 GMT

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.

Re: Transforming data

Kurt_Bremser — Wed, 29 Jan 2020 08:03:15 GMT

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

Re: Transforming data

Sajid01 — Wed, 29 Jan 2020 13:36:11 GMT

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.

Re: Transforming data

ballardw — Wed, 29 Jan 2020 16:34:56 GMT

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.