topic Transposing multiple variables with same ID in SAS Procedures

Transposing multiple variables with same ID

stancemcgraw — Tue, 31 Jan 2017 21:48:44 GMT

Hello,

I am working on a data set that includes patientID#s, but each patient has multiple responses for one variable. (Multiple cormorbidites and complications, for example). I want to transpose them out so that I don't have extra spaces or repeated PatientIDS. How do I transpose all of these multiple row variables in one step?

So far I have done this:

proc sort data=exlap.traumabase3; by PatientNumber; run;

proc transpose data = exlap.traumabase3 out=exlap.traumabase1 (drop=_name_) prefix=Comorbidites;

by PatientNumber;

var Comorbidities ;

run;

*And this transposes too many comorbidities and only transposes one variable at a time. I have about 40 extra with no data listed for some reason (Comorbidities 6-Comorbidites41 have nothing listed).

Re: Transposing multiple variables with same ID

Reeza — Tue, 31 Jan 2017 22:37:45 GMT

I would suggest looking at the macro here:

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

Otherwise your options are to

1: Multiple proc transpose and merge the results

2: Use a data step array to transpose but you need to know the maximum amount of variables before hand. The macro or option 1 doesn't require the amount before.

Re: Transposing multiple variables with same ID

stancemcgraw — Tue, 31 Jan 2017 22:59:56 GMT

Ok. I am not familiar with Macros at all so I am going to use multiple transpose steps, however, when I have trasnposed one variable it lists too many:

Cormobidites6-Cormibidites41 and there is no data there... How do I specifiy only Cormorbidites1-Cormobidites5?

Please see attached screenshot with empty columns of comorbidities below...

Re: Transposing multiple variables with same ID

Reeza — Tue, 31 Jan 2017 23:02:13 GMT

That means you do have some records that have 41 comorbidities. What do you want to do with those?

You can just drop the variables you don't need after if you want to keep only 5.

Typically, I've seen 25 or 10 used in these cases.

If you post the code you're using we can help with the modifications.

You don't have to write the macro, just use it. If you give the paper a quick read through it's probably the easiest method.