How to get do loop in SAS to work with date values?

Question

I have a data set containing 4 years data (2014-2017). I want to give every observation a period-variable containing E, E/F or F from an if-criteria.

I got it to work by repeating my code for every year in a data step:

data new; set old;

format period $10.;

if year=2014 then do;
    if start<'01feb2014'd and end<='01mar2014'd then period='E'; 
    else if start<'01feb2014'd and end>'01mar2014'd then period='E/F'; 
    else if start>='01feb2014'd then period='F';
end;
if year=2015 then do;
    if start<'01feb2015'd and end<='01mar2015'd then period='E'; 
    else if start<'01feb2015'd and end>'01mar2015'd then period='E/F'; 
    else if start>='01feb2015'd then period='F';
end;

if year=2016 then do;
    if start<'01feb2016'd and end<='01mar2016'd then period='E'; 
    else if start<'01feb2016'd and end>'01mar2016'd then period='E/F'; 
    else if start>='01feb2016'd then period='F';
end;
if year=2017 then do;
    if start<'01feb2017'd and end<='01mar2017'd then period='E'; 
    else if start<'01feb2017'd and end>'01mar2017'd then period='E/F'; 
    else if start>='01feb2017'd then period='F';
end;

run;

But I want to do the code smarter, by using af do loop. I tried this:

data new; set old;

format period $10.;

do i=0 to 3;
    if year=(2014+i) then do;

        if start<'01feb(2014+i)'d and end<='01mar(2014+i)'d then period='E'; 
        else if start<'01feb(2014+i)'d and end>'01mar(2014+i)'d then period='E/F'; 
        else if start>='01feb(2014+i)'d then period='F';
    end;

end;

run;

But that doesn't give the right results for the years 2015-2017.

I also tried this:

data new; set old;

format period $10.;

do i=2014 to 2017;
    if year=(i) then do;

        if start<'01feb(i)'d and end<='01mar(i)'d then period='E'; 
        else if start<'01feb(i)'d and end>'01mar(i)'d then period='E/F'; 
        else if start>='01feb(i)'d then period='F';
    end;

end;

run;

But the code can't even run?

What an I missing??

Kind regards.

Answer 1

I don't see why you would need a DO loop for that data. The different years are on different observations. You can use the MDY() function to build a date from separate month, day and year values.

data new;
  set old;
  length period $10;
  if start<mdy(2,1,year) and end<=mdy(3,1,year) then period='E'; 
  else if start<mdy(2,1,year) and end>mdy(3,1,year) then period='E/F'; 
  else if start>=mdy(2,1,year) then period='F'; 
  else period=' ';
run;

If the YEAR variable is not in your data and you want to replicate your data into separate observations for each of the years 2014 to 2017 then you could add a DO loop with an OUTPUT statement inside of it.

data new;
  set old;
  length year 8 period $10;
  do year=2014 to 2017;
    if start<mdy(2,1,year) and end<=mdy(3,1,year) then period='E'; 
    else if start<mdy(2,1,year) and end>mdy(3,1,year) then period='E/F'; 
    else if start>=mdy(2,1,year) then period='F'; 
    else period=' ';
    output;
  end;
run;

P.S. The FORMAT statement is used to attach special instructions for how to display the variable, but SAS does not need special instructions to display character variables. If you want to define a new variable before you start using it in your data step code you can use either the LENGTH or ATTRIB statement.

Answer 2

You can use MDY(3,1, 2017); to create a date from integers. You can also use the function INTNX('month',dt,12,'END') + 1; to add 12 month to a date dt and then reach the end of the month and add 1 to reach the starting of the next month.

So if I'm not wrong, the code below should work.

format period $10.;

do i=2014 to 2017;
    if year=(i) then do;

        if start<MDY(2,1, i) and end<=MDY(3,1, i) then period='E'; 
        else if start<MDY(2,1, i) and end>MDY(3,1, i) then period='E/F'; 
        else if start>=MDY(2,1, i) then period='F';
    end;

end;