topic Re: Use Loop + Macro (Maybe) to run if/then statement on dates in SAS Programming

Use Loop + Macro (Maybe) to run if/then statement on dates

altatunc — Mon, 20 Jun 2022 01:39:05 GMT

I have a data set with start and stop dates and i need to assign it a year based off some simple logic. Given the years span unknown number of years, I'd like to embed this into a do loop or macro or both. Also just trying to understand loops and macros a bit more here.

Here is what the logic would look like for two years:

If Start_Date <= "01OCT010"d and End_Date > "01OCT2010"d and Start_Date ne "31DEC9999"d
             then Assigned_Year = 2010;
If Start_Date <= "01OCT011"d and End_Date > "01OCT2011"d and Start_Date ne "31DEC9999"d
            then Assigned_Year = 2011;

In short, what is the best way to embed this into a loop/macro to assign this? I can assign it a starting year or date but still struggling with the syntax just a bit.

Thanks in advance.

Re: Use Loop + Macro (Maybe) to run if/then statement on dates

Kurt_Bremser — Mon, 20 Jun 2022 06:51:04 GMT

And which assigned_year do you want for start_date=2010-01-01 and end_date=2012-12-31?

2009, 2010, 2011 or 2012?

Re: Use Loop + Macro (Maybe) to run if/then statement on dates

ballardw — Mon, 20 Jun 2022 15:52:42 GMT

First hint: Use of 2-digit years is a BAD IDEA. Google costs associated with Y2K. There is absolutely no reason to use 2-digit years in date literals "01OCT10"d really should be "01OCT2010"D

Second hint: If the use of '31DEC9999'd is for anything resembling invalid, not present, not recorded or such meaning then you may actually want to use a MISSING value in SAS instead.

Just how many of these conditional values are to be assigned?

Re: Use Loop + Macro (Maybe) to run if/then statement on dates

ErikLund_Jensen — Mon, 20 Jun 2022 18:56:17 GMT

Hi @altatunc

It is a little difficult to figure out what you are trying to achieve. So my guess is:

You have some data with observations containing a time interval Start_Date-End_Date spanning any period.
You want to test the time interval against a consecutive list of years (one for each year in your code).
for each observation, you want an output record for each year where October1. in the given year is present in the time interval.

If my guess is correct, then this code will work:

data have;
  format Start_Date End_Date date9.;
  ID = 1; Start_Date = '03apr2004'd; End_Date = '31dec2012'd; output;
  ID = 2; Start_Date = '01oct2018'd; End_Date = '31aug2020'd; output;
run;

data want;
  set have;
  do assigned_year = 2008 to 2021;
   if Start_Date <= mdy(10,1,assigned_year) <= End_Date then output;
  end;
run;

Result:

Re: Use Loop + Macro (Maybe) to run if/then statement on dates

andreas_lds — Tue, 21 Jun 2022 07:20:25 GMT

Please post sample data and show the expected result so that we understand better what you are really doing.

Re: Use Loop + Macro (Maybe) to run if/then statement on dates

altatunc — Tue, 21 Jun 2022 14:41:48 GMT

Thanks so much. This is exactly what i was looking for and far simpler than i thought at that!
Thanks again,
AT