SAS Turn Row into new Column variables

[serena13lee]

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

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 WEIGHT = 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 WEIGHT = C where D = 'Screen';
else WEIGHT = 'NA';
RUN; 
PROC PRINT DATA = MYTEST; 
RUN;

Desired Outcome: 
DATA: 
A Weight Height 
Jim 200 60
Tod 201 70

[Reeza · Accepted solution]

1. Fill in missing values using RETAIN

2. Transpose by filtering for records that are not screening

DATA have;
infile cards truncover;
input @1 name $   @5  stat $    @12 value   @17 Test $;
cards;
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
;;;;
proc sort data=have;
by name stat descending test;
run;

data filled;
set have;
by name stat;
retain stat_value;

if first.name or not missing(value) then stat_value = value;

run;


proc transpose data=filled (where = (test ne 'Screen')) 
               out=want;
  by name;
  id stat;
  var stat_value;
run;

---

@serena13lee wrote:

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

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 WEIGHT = 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 WEIGHT = C where D = 'Screen';
else WEIGHT = 'NA';
RUN; 
PROC PRINT DATA = MYTEST; 
RUN;

Desired Outcome: 
DATA: 
A Weight Height 
Jim 200 60
Tod 201 70

---