01-31-2017 04:48 PM
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;
var Comorbidities ;
*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).
01-31-2017 05:37 PM
I would suggest looking at the macro here:
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.
01-31-2017 05:59 PM
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...
01-31-2017 06:02 PM
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.