BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
awardell
Obsidian | Level 7

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! 

1 ACCEPTED SOLUTION

Accepted Solutions
PeterClemmensen
Tourmaline | Level 20

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;

View solution in original post

2 REPLIES 2
PeterClemmensen
Tourmaline | Level 20

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;
awardell
Obsidian | Level 7

Thank you! That works wonderfully! 

 

 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 399 views
  • 0 likes
  • 2 in conversation