topic Combing multiple columns into one based on a Condition in SAS Programming

Combing multiple columns into one based on a Condition

serena13lee — Wed, 08 May 2019 07:20:50 GMT

I am trying to create an additional column called CALC which essentially condenses CalcHT, CalcWT and CalcBMI into one column. This is done by finding the corresponding variable and height, weight, hdcirc, bmi. Some data is missing.

data have;
input Variable $ Visit $ CalcHT CalcWT CalcBMI; 
cards; 
Height 1 5 2 1 
Weight 1 5 2 1 
HDCIRC 1 5 2 1        
BMI 1 5 2 1              
Height 2 5 3 3 
Weight 2 5 3 3 
HDCIRC 2 5 3 3 
BMI 2 5 3 3 
;

data want;
input Variable $ Visit $ CalcHT CalcWT CalcBMI Calc; 
cards; 
Height 1 5 2 1 5
Weight 1 5 2 1 2
HDCIRC1 5 2 1        
BMI 1 5 2 1 1             
Height 2 5 3 3 5 
Weight 2 5 3 3 2
HDCIRC 2 5 3 3 
BMI 2 5 3 3 3
;

Variable      Visit      CalcHT      CalcWT       CalcBMI       Calc
Height           1           5          2            1            5
Weight           1           5          2            1            2
HDCIRC           1           5          2            1        
BMI              1           5          2            1            1             
Height           2           5          3            3            5 
Weight           2           5          3            3            2
HDCIRC           2           5          3            3 
BMI              2           5          3            3            3

For visit 1, CalcHT is 5 therefore Calc = 5 for Height
For visit 1, CalcWT is 2 therefore Calc = 2 for Weight 
For visit 1, CalcBMI is 1 therefore Calc = 1 for BMI
For visit 2, CalcHT is 5 therefore Calc = 5 for Height 
For visit 2, CalcWT is 3 therefore Calc = 3 for Weight
so on and so forth.

I'm not sure where to start, but I believe this would be the inverse of an array or a transpose.

Re: Combing multiple columns into one based on a Condition

PeterClemmensen — Wed, 08 May 2019 07:07:41 GMT

I don't get it? So for example, why is NEW= 5 in the first row?

Re: Combing multiple columns into one based on a Condition

serena13lee — Wed, 08 May 2019 07:17:10 GMT

@PeterClemmensen Thanks for your inquiry! So yes it's a bit confusing. I've renamed NEW to CALC to better reflect what it is and added an explanation at the bottom. Please let me know if there's anything else I can clarify! Thanks!

Re: Combing multiple columns into one based on a Condition

PeterClemmensen — Wed, 08 May 2019 07:21:09 GMT

Is your problem as simple as your data here suggests? If so, then you can simply do like this

data have;
input Variable $ Visit $ CalcHT CalcWT CalcBMI; cards; 
Height 1 5 2 1 
Weight 1 5 2 1 
HDCIRC 1 5 2 1        
BMI 1 5 2 1              
Height 2 5 3 3 
Weight 2 5 3 3 
HDCIRC 2 5 3 3 
BMI 2 5 3 3 
;

data want;
   set have;
   if Variable="Height" then NEW=CalcHT;
   else if Variable="Weight" then NEW=CalcWT;
   else if Variable="BMI" then NEW=CalcBMI;
   else NEW=.;
run;

Otherwise, I'll find a more dynamic solution 🙂

Re: Combing multiple columns into one based on a Condition

serena13lee — Wed, 08 May 2019 07:29:09 GMT

@PeterClemmensen Thanks so much! Looks like that was it. I think I've been looking at this dataset too long to make any sense of it. Your solution was great!

Re: Combing multiple columns into one based on a Condition

PeterClemmensen — Wed, 08 May 2019 07:30:11 GMT

Nice! So glad you found your answer 🙂