mardi 11 décembre 2018

SAS Turn Row into new Column variables

I would like to create a new variable 'Weight' and 'Height' using the variables A through D:

DATA: 
A       B     C    D       
Jim  Weight  180   Screen
Jim  Weight  200   C1
Jim  Height  60    Screen
Jim  Height  61    C3
Tod  Weight  190   Screen
Tod  Weight  201   C1
Tod  Height  70    Screen
Tod  Height        C1

The Weight variable would have the following criteria: Set Weight to Column C if Column B = Weight, Column D = C1 and Column C is not missing. Else if Column D is not C1 or Column C is missing, then use the Column C where Column D is Screen. So in plain terms, let's say Jim was weighed during screening and not for C1 then I would like to keep his screen weight. Or if he was screened at C1 but the wight is missing, I would like to keep his screen weight. Similarly for the Height variable.

My code which is incorrect is:

DATA MYTEST; 
    SET TEST.TEST; 
    if B = 'WEIGHT' and D = 'C1D1' and not missing(C) then NEW = C;
    else if (missing(C) or D ~= 'C1D1') and B = 'WEIGHT' then NEW = C where D = 'Screen';
    if B = 'HEIGHT' and D = 'C1D1' and not missing(C) then NEW = C;
    else if (missing(C) or D ~= 'C1D1') and B = 'HEIGHT' then NEW = C where D = 'Screen';
    else NEW = 'NA';
 RUN; 
 PROC PRINT DATA = MYTEST; 
 RUN; 

Desired Outcome:

DATA: 
A    Weight   Height 
Jim   200       60
Tod   201       70

Aucun commentaire:

Enregistrer un commentaire