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.
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;
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;
@tarheel13 Thanks a ton... It did work for what I am trying to do ... 🙂
@Reeza Thank you very much . It helped... Appreciate it.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.