topic Re: Transposing wide data in SAS Programming

Transposing wide data

stancemcgraw — Sun, 04 Oct 2020 18:57:59 GMT

Hello,

I have several values under the variable Comorbidities per unique studyid. I'd like to create new dummy variables for each comorbidity listed.

Data have;

Studyid Comorbidities

678 smoker, alcohol use disorder

679 current smoker

680 hypertension, diabetes mellitus

data want;

studyid Smoker Alcohol_use_disorder Hypertension Diabetes_Mellitus

678 1 1 0 0

679 1 0 0 0

680 0 0 1 1

Re: Transposing wide data

Kurt_Bremser — Sun, 04 Oct 2020 19:16:34 GMT

First, dissect the string:

data long;
set have;
length comorbidity $50;
value = 1;
do i = 1 to countw(comorbidities,',');
  comorbidity = translate(strip(scan(comorbidities,i,',')),'_',' ');
  output;
end;
drop i comorbidities;
run;

Then you can transpose that (although I recommend to keep the long dataset, as it is easier to work with):

proc transpose
  data=long
  out=want (dropy=_name_)
;
by studyid;
id comorbidity;
var value;
run;

Untested, posted from my tablet.

Re: Transposing wide data

stancemcgraw — Sun, 04 Oct 2020 20:27:44 GMT

So now it gives me soooo many different iterations of the same thing. This makes it so that any comorbidity that is cut-off now becomes its own variable.

for example, when I proc freq comorbidites, mental/personaliy disorder, for example now has :
mental
mental/
mental/personalit
mental/personality
mental/personality_diso
And now, each of those become a variable, when those should all be the same variable....

Re: Transposing wide data

Kurt_Bremser — Sun, 04 Oct 2020 20:46:39 GMT

Add the additional characters that need to be replaced to the TRANSLATE function.

Re: Transposing wide data

Reeza — Mon, 05 Oct 2020 01:45:08 GMT

I think this is identical to a previous question you asked recently...

Given that diseases aren't coded cleanly, I'd definitely recommending using the dynamic approach instead.

1. Separate out terms into individual rows (data step)
2. Clean them up here for the issues mentioned in your second post.
3. Add an indicator variable to get the 1
4. Flip the clean version wide using PROC TRANSPOSE