topic Re: Data Sorting / Group up in SAS Programming

Data Sorting / Group up

Anuz — Tue, 22 Jun 2021 14:15:46 GMT

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.

Re: Data Sorting / Group up

tarheel13 — Tue, 22 Jun 2021 14:27:35 GMT

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;

Re: Data Sorting / Group up

Reeza — Tue, 22 Jun 2021 15:15:03 GMT

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

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

Re: Data Sorting / Group up

Anuz — Wed, 23 Jun 2021 12:02:44 GMT

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

Re: Data Sorting / Group up

Anuz — Wed, 23 Jun 2021 12:04:32 GMT

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