BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
serena13lee
Quartz | Level 8

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
1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

 

View solution in original post

1 REPLY 1
Reeza
Super User

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

 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 1 reply
  • 633 views
  • 0 likes
  • 2 in conversation