topic Re: Values offset by one row upon new variable creation and conditional value assignment in SAS Programming

Values offset by one row upon new variable creation and conditional value assignment

YD5265 — Fri, 28 May 2021 10:11:24 GMT

Hello,

I'm running into a strange issue. I'm trying to assign values to the variable YEARBUILT based on the value of CNS_P_LBL like so:

data OUTPUT;
length YEARBUILT $100;
format YEARBUILT $100.;
informat YEARBUILT $100.;
length CNS_P_LBL $16;
if CNS_P_LBL = "1971-1990" then  YEARBUILT = "01/01/1971";
if CNS_P_LBL = "1991-2000" then  YEARBUILT = "01/01/1990";
if CNS_P_LBL = "2001-2005" then  YEARBUILT = "01/01/2003";
if CNS_P_LBL = "2006-2010" then  YEARBUILT = "01/01/2008";
if CNS_P_LBL = "2011-2015" then  YEARBUILT = "01/01/2013";
if CNS_P_LBL = "2016-2020" then  YEARBUILT = "01/01/2018";
if CNS_P_LBL = "1970 or before" then  YEARBUILT = "12/31/9999"; 
set INPUT;

However, this results in shifted YEARBUILT values by one row. It is shown in the picture below.

What am I doing wrong?

Thank you in advance.

Best regards.

Re: Values offset by one row upon new variable creation and conditional value assignment

PaigeMiller — Fri, 28 May 2021 10:46:46 GMT

Move the line that says set input; to immediately below the length statement.

Since SAS dates are numeric, you do not want to create a character variable YEARBUILT containing 01/01/1970. You should really make YEARBUILT into a numeric variable, as follows:

if CNS_P_LBL = "1971-1990" then  YEARBUILT = '01JAN1971'd;

and you should assign YEARBUILT the format DATE9. or any other date format you like.

Re: Values offset by one row upon new variable creation and conditional value assignment

AMSAS — Fri, 28 May 2021 13:59:41 GMT

@YD5265 Adding an explanation to why you got the results you did, prior to implementing @PaigeMiller solution.

There are two types of Data Step statements, Declarative & Executable
The first 5 lines (up to and including length CNS_P_LBL $16;) are all declarative, the rest executable.
Executable statements are processed in the order they appear in the data step.

Your first executable statement is

if CNS_P_LBL = "1971-1990" then  YEARBUILT = "01/01/1971";

At this point in the data step execution CNS_P_LBL (as is YEARBUILT) is an empty string, it is a known variable as it was declared in the length statement during the compilation phase.

None of your IF statements are true when CNS_P_LBL is an empty string so YEARBUILT will be an empty string.
When the execution phase reaches the set INPUT; statement, then CNS_P_LBL is read from the INPUT data set, resulting in its value being set to 1971-1990.

An easy way to see this to understand it, is to use PUT statements within your code (note this is a handy debugging method):

data OUTPUT;
length YEARBUILT $100;
format YEARBUILT $100.;
informat YEARBUILT $100.;
length CNS_P_LBL $16;
PUT "1) " _all_ ; /* _all_ will output all the variable values */ 
if CNS_P_LBL = "1971-1990" then  YEARBUILT = "01/01/1971";
if CNS_P_LBL = "1991-2000" then  YEARBUILT = "01/01/1990";
if CNS_P_LBL = "2001-2005" then  YEARBUILT = "01/01/2003";
if CNS_P_LBL = "2006-2010" then  YEARBUILT = "01/01/2008";
if CNS_P_LBL = "2011-2015" then  YEARBUILT = "01/01/2013";
if CNS_P_LBL = "2016-2020" then  YEARBUILT = "01/01/2018";
if CNS_P_LBL = "1970 or before" then  YEARBUILT = "12/31/9999"; 
PUT "2) " _all_ ; /* _all_ will output all the variable values */ 
set INPUT;
PUT "3) " _all_ ; /* _all_ will output all the variable values */ 
RUN ;

Paige's solution works, because once you move the SET statement prior to the IF statements, then the variable CNS_P_LBL gets assigned prior to the IF statements.