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!
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;
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;
Thank you! That works wonderfully!
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.