Data Sorting / Group up

[Anuz]

Hi,

I have a dataset as below 

data REL_DATA;
input ENUM $ RELATED_ENUM $ ROLE $;
datalines;
D1234 R1234 84
D1234 R1234 85
D1234 L2345 90
D1234 P1234 81
D1234 N4567 90
D1234 N4567 88
;
run;

I want the output as below 

ENUM RELATED_NUM  ROLES
D1234 R1234                  84,85
D1234 L2345                  90
D1234 P1234                  81
D1234 N4567                 90,88

Basically if the Related_num is repeated for a different role, i want it to be merged to one row with the roles displayed in the ROLES column

Thank you.  

[tarheel13 · Accepted solution]

If it was me, I would sort and transpose it and then concatenate all the values of role after transposing.

data REL_DATA;
input ENUM $ RELATED_ENUM $ ROLE $;
datalines;
D1234 R1234 84
D1234 R1234 85
D1234 L2345 90
D1234 P1234 81
D1234 N4567 90
D1234 N4567 88
;
run;

proc sort data=rel_data;
	by enum related_enum;
run;

proc transpose data=rel_data out=rel_data_t(drop=_name_);
	by enum related_enum;
	var role;
run;

data rel_data_t2;
	set rel_data_t;
	roles=catx(',',of col:);
run;

[Anuz]

@tarheel13  Thanks a ton... It did work for what I am trying to do ... 🙂 

[Reeza]

There are two different standard methods to do that, both are illustrated here.

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

[Anuz]

@Reeza  Thank you very much . It helped... Appreciate it.