topic Re: Help with Proc Transpose in SAS Programming

Help with Proc Transpose

Krysia24 — Tue, 07 May 2019 21:53:03 GMT

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

Re: Help with Proc Transpose

ballardw — Tue, 07 May 2019 22:03:10 GMT

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.

Re: Help with Proc Transpose

Krysia24 — Tue, 07 May 2019 22:13:59 GMT

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.

Re: Help with Proc Transpose

Reeza — Tue, 07 May 2019 22:22:58 GMT

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.

Re: Help with Proc Transpose

Reeza — Tue, 07 May 2019 22:25:50 GMT

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

Re: Help with Proc Transpose

Krysia24 — Tue, 07 May 2019 22:28:27 GMT

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).

Re: Help with Proc Transpose

VDD — Tue, 07 May 2019 22:37:06 GMT

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.

Re: Help with Proc Transpose

Reeza — Tue, 07 May 2019 22:37:13 GMT

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.

Re: Help with Proc Transpose

ChrisNZ — Tue, 07 May 2019 22:38:36 GMT

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-a-dataset/ta-p/433620

Re: Help with Proc Transpose

Krysia24 — Tue, 07 May 2019 22:45:10 GMT

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

Re: Help with Proc Transpose

Ksharp — Wed, 08 May 2019 13:06:48 GMT

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;