Combing multiple columns into one based on a Condition

[serena13lee]

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. 

[PeterClemmensen]

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

[serena13lee]

@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!

[PeterClemmensen · Accepted solution]

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 🙂

[serena13lee]

@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!

[PeterClemmensen]

Nice! So glad you found your answer 🙂