topic Re: How do I make subjects with the same date in multiple rows go in one column? in SAS Programming

How do I make subjects with the same date in multiple rows go in one column?

awardell — Thu, 18 Aug 2022 18:50:50 GMT

Hello,

I have data that looks like the following:

ID	AE_TYPE	AE_DATE
1	A	08-05-2022
1	B	.
1	C	08-05-2022
1	D	08-05-2022
1	E	06-23-2021
2	A
2	B	05-07-2019
2	C	04-15-2020
2	D	05-07-2019
2	E
3	A	07-21-2016
3	B
3	C	09-25-2018
3	D	11-09-2017
3	E



data have;
	input ID AE_TYPE $ AE_DATE :mmddyy10. ;
	format AE_DATE mmddyy10.;
	cards;
	1	A	08-05-2022
	1	B	.
	1	C	08-05-2022
	1	D	08-05-2022
	1	E	06-23-2022
	2	A	.
	2	B	05-07-2019
	2	C	04-15-2020
	2	D	05-07-2019
	2	E	.
	3	A	07-21-2016
	3	B	.
	3	C	09-25-2018
	3	D	11-09-2017
	3	E	.
;
run;

I would like to have the data look like the following:

ID	AE_TYPE	AE_DATE	AE_TYPEA	AE_TYPEB	AE_TYPEC	AE_TYPED	AE_TYPEE
1	A C D	08-05-2022	1		1	1
1	E	06-23-2021					1
2	B D	05-07-2019		1		1
2	C	04-15-2020			1
3	A	07-21-2016	1
3	C	09-25-2018			1
3	D	11-09-2017				1

Essentially, I need those that have the same AE_DATE to be represented in the same row, and those that have differing AE_DATE for subjects be in different rows. I have tried to use proc transpose, however this is similar to doing something that is long to "half-wide". Additionally, I am unsure how to make AE_TYPE as a concatenated variable.

Thank you in advance for any help you can provide!

Re: How do I make subjects with the same date in multiple rows go in one column?

PeterClemmensen — Thu, 18 Aug 2022 19:06:21 GMT

How about

data have;
input ID AE_TYPE $ AE_DATE :mmddyy10. ;
format AE_DATE mmddyy10.;
cards;
1 A 08-05-2022
1 B .         
1 C 08-05-2022
1 D 08-05-2022
1 E 06-23-2022
2 A .         
2 B 05-07-2019
2 C 04-15-2020
2 D 05-07-2019
2 E .         
3 A 07-21-2016
3 B .         
3 C 09-25-2018
3 D 11-09-2017
3 E .         
;

proc sort data = have;
   by ID AE_DATE AE_TYPE;
run;

proc transpose data = have(where = (AE_DATE)) out = temp(drop = _:) prefix = AE_TYPE;
   by ID AE_DATE;
   id AE_TYPE;
   var AE_TYPE;
run;

data want;
   set temp;
   length AE_TYPE $200;
   AE_TYPE = catx(' ', of AE_TYPE:);
run;

Re: How do I make subjects with the same date in multiple rows go in one column?

awardell — Thu, 18 Aug 2022 19:10:20 GMT

Thank you! That works wonderfully!