Help with Proc Transpose

[Krysia24]

Having a hard time trying to get my transpose right. Perhaps it's not even the right thing to be going for. I have something that looks like this (just an example): 

ID	Visit_Date	Discharge_Dx	Sex	DOB	Chief_Complaint
1	3/1/2019	G44	M	3/24/1957	headache
2	2/22/2019	W34	F	8/23/1967	GSW
2	4/15/2019	S01	F	8/23/1967	cut
3	1/17/2019	T50	M	9/6/1991	overdose
4	3/27/2019	J02	F	11/3/2012	sore throat
5	2/3/2019	R11	F	12/19/2008	vomiting
6	1/6/2019	M25	M	7/7/1977	body aches
6	2/11/2019	M54	M	7/7/1977	back pain
6	3/7/2019	R05	M	7/7/1977	cough

But what I'd want it more to look like this - where those ID's with multiple visit dates are condensed into a single line. DOB and sex can stay the same but I'd like multiple columns for visit date, chief complaint, and discharge dx if a patient has multiple records for those: 

ID	Visit_Date_1	Visit_Date_2	Visit_Date_3	Discharge_Dx_1	Discharge_Dx_2	Discharge_Dx_3	Sex	DOB	Chief_Complaint_1	Chief_Complaint_2	Chief_Complaint_3
1	3/1/2019	.	.	G44	.	.	M	3/24/1957	headache	.	.
2	2/22/2019	4/15/2019	.	W34	S01	.	F	8/23/1967	GSW	cut	.
3	1/17/2019	.	.	T50	.	.	M	9/6/1991	overdose	.	.
4	3/27/2019	.	.	J02	.	.	F	11/3/2012	sore throat	.	.
5	2/3/2019	.	.	R11	.	.	F	12/19/2008	vomiting	.	.
6	1/6/2019	2/11/2019	3/7/2019	M25	M54	R05	M	7/7/1977	body aches	back pain	cough

[ballardw]

Before working on the not terribly difficult transpose, please describe WHY you want your data in this wide format? What will you be doing with it that requires the wide format? Such as what questions need to be answered or what type of a report is needed.

Also, will you be adding more data later with the same patients but more visits? If so, then anything you do to use the wide format will require rewriting to handle the additional visits.

It is not uncommon for people that have been trained to use spreadsheets to try to force SAS to behave like a spreadsheet, such as making data such as you have into a wide format. Often it is very much harder to use in a wide format and if you frequently have additional data making it wider then even worse for maintaining/writing code.

And if the sole purpose is to export this to a spreadsheet to do the "real work" you may be creating more work for yourself.

[Krysia24]

So again the data as it in the cells is just an example of what I have but what I'll be doing is repeat measures analysis so I thought it'd be easier to have it as one line for those with multiple visits. 

[Reeza]

It can be for some measures but not if you're planning to do a regression model. What's the downstream usage of this data? For calculating measures such of length of stay or recurrence it can be 'easier' in this format but the long format is much more efficient.

[Krysia24]

So one way we'd be using this is trend analysis to see if a patient's severity/risk increases over time with their multiple visits. (Not something you can tell with what I plugged in - I just put in some random discharge codes). 

[Reeza]

Interesting, I think for that use case you may need a long format but it depends on what rules and models you use downstream. Either way, you'll have the data available for either format, my only concern is how wide and unmanageable that data may get as you start doing calculations over time. For an ER visit data we had some users in almost 30 days out of the month sometimes. It's only a few people but the data structure has to handle all of those cases.

[Krysia24]

Okay thanks! I think I'll explore analyses with both formats and see what works better. 

[Reeza · Accepted solution]

You can use this user custom written macro to transpose your data. This link contains the link to the presentation and the SAS code for the macro. 

http://www.sascommunity.org/wiki/A_Better_Way_to_Flip_(Transpose)_a_SAS_Dataset

This would be the easiest solution IMO. 

Other approaches: 

• Multiple PROC TRANSPOSE and merge results https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-long-to-wide-using-proc-transpose/
• Data step transpose - bit more manual for sure: https://stats.idre.ucla.edu/sas/modules/reshaping-data-long-to-wide-using-the-data-step/

---

@Krysia24 wrote:

Having a hard time trying to get my transpose right. Perhaps it's not even the right thing to be going for. I have something that looks like this (just an example): 

ID	Visit_Date	Discharge_Dx	Sex	DOB	Chief_Complaint
1	3/1/2019	G44	M	3/24/1957	headache
2	2/22/2019	W34	F	8/23/1967	GSW
2	4/15/2019	S01	F	8/23/1967	cut
3	1/17/2019	T50	M	9/6/1991	overdose
4	3/27/2019	J02	F	11/3/2012	sore throat
5	2/3/2019	R11	F	12/19/2008	vomiting
6	1/6/2019	M25	M	7/7/1977	body aches
6	2/11/2019	M54	M	7/7/1977	back pain
6	3/7/2019	R05	M	7/7/1977	cough

But what I'd want it more to look like this - where those ID's with multiple visit dates are condensed into a single line. DOB and sex can stay the same but I'd like multiple columns for visit date, chief complaint, and discharge dx if a patient has multiple records for those: 

ID	Visit_Date_1	Visit_Date_2	Visit_Date_3	Discharge_Dx_1	Discharge_Dx_2	Discharge_Dx_3	Sex	DOB	Chief_Complaint_1	Chief_Complaint_2	Chief_Complaint_3
1	3/1/2019	.	.	G44	.	.	M	3/24/1957	headache	.	.
2	2/22/2019	4/15/2019	.	W34	S01	.	F	8/23/1967	GSW	cut	.
3	1/17/2019	.	.	T50	.	.	M	9/6/1991	overdose	.	.
4	3/27/2019	.	.	J02	.	.	F	11/3/2012	sore throat	.	.
5	2/3/2019	.	.	R11	.	.	F	12/19/2008	vomiting	.	.
6	1/6/2019	2/11/2019	3/7/2019	M25	M54	R05	M	7/7/1977	body aches	back pain	cough

---

[VDD]

this link to SAS proc transpose with examples should answer your needs given that you really want to transpose your data.

https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-long-to-wide-using-proc-transpose.

[ChrisNZ]

1. The result you seek makes it really hard to use the data in subsequent steps.

2. This should interest you 

https://communities.sas.com/t5/SAS-Communities-Library/A-better-way-to-FLIP-i-e-transpose-make-wide-...

[Ksharp]

data have;
infile cards expandtabs truncover;
input (ID	Visit_Date 	Discharge_Dx	Sex	DOB	Chief_Complaint  ) (: $20.);
cards;
1	3/1/2019	G44 	M	3/24/1957	headache 
2	2/22/2019	W34 	F	8/23/1967	GSW
2	4/15/2019	S01	F	8/23/1967	cut
3	1/17/2019	T50 	M	9/6/1991	overdose 
4	3/27/2019	J02	F	11/3/2012	sore throat 
5	2/3/2019	R11	F	12/19/2008	vomiting 
6	1/6/2019	M25 	M	7/7/1977	body aches 
6	2/11/2019	M54	M	7/7/1977	back pain 
6	3/7/2019	R05 	M	7/7/1977	cough 
;

proc sql noprint;
select 	max(n) into : n
 from (select count(*) as n from have group by id,sex,dob);
quit;
proc summary data=have nway;
class id sex dob;
output out=want idgroup(out[&n] (Visit_Date Discharge_Dx Chief_Complaint)=);
run;