topic Date range across years in New SAS User

Date range across years

NewSASPerson — Wed, 04 Dec 2019 12:38:12 GMT

I am trying to set up a macro for a report that the date parameters will always be September 1st - August 31st of the following year.

If I do something like the code below, in January the start date (&start) will resolve to September 1st, 2020 instead of 2019. How do I get the date to resolve to the previous year without having to change it across years.

data _null_;

call symput('start',put(intnx('year',today(),0,'b'),year4.)||"0901");

call symput('end' ,put(intnx('year' ,today(),1,'e'),year4.)||"0831" );

run;

%put &start &end;

Re: Date range across years

Kurt_Bremser — Wed, 04 Dec 2019 12:44:02 GMT

No, it does not. See this log:

 73         data _null_;
 74         call symput('start',put(intnx('year','01jan2019'd,0,'b'),year4.)||"0901");
 75         call symput('end' ,put(intnx('year' ,'01jan2019'd,1,'e'),year4.)||"0831");
 76         run;
 
 NOTE:  Verwendet wurde: DATA statement - (Gesamtverarbeitungszeit):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 
 77         
 78         %put &start &end;
 20190901 20200831

Re: Date range across years

Kurt_Bremser — Wed, 04 Dec 2019 12:49:51 GMT

Forget my previous post. You are concerned about running the step before September 1st. See this:

data _null_;
year = year(today());
if month(today()) le 9 then year = year - 1;
call symput('start',put(mdy(9,1,year),yymmddn8.));
call symput('end',put(mdy(8,31,year+1),yymmddn8.));
run;
 
%put &start &end;

Re: Date range across years

PaigeMiller — Wed, 04 Dec 2019 13:08:13 GMT

Do not use character strings to represent calendar items.

Instead use actual SAS date values, which are integers representing the number of days since Jan 1, 1960; and use with them SAS date functions such as MDY() (as shown by @Kurt_Bremser) and INTNX() and INTCK() and many other functions, and then use SAS date formats to make the results appear readable to humans.

Re: Date range across years

ballardw — Wed, 04 Dec 2019 18:06:11 GMT

@PaigeMiller wrote:

Do not use character strings to represent calendar items.

Instead use actual SAS date values, which are integers representing the number of days since Jan 1, 1960; and use with them SAS date functions such as MDY() (as shown by @Kurt_Bremser) and INTNX() and INTCK() and many other functions, and then use SAS date formats to make the results appear readable to humans.

Agreed.

Here is an example of determining a US federal fiscal year from a SAS date value:

data example;
   do year= 2010 to 2020;
      do month = 1 to 12;
         date = mdy(month,1,year);
         fedFiscalyear = year(date)+ (month(date) ge 10);
         output;
      end;
   end;
   format date yymmdd10.;
run;

You don't actually show how you intend to use those macro values but if you have an actual date for comparison I suspect a modification of the code above ( ge 9 ) gets most of what you want.

For example if I had a data set with a date and I wanted to get the Sep 2018 to Aug 2019 records I could use:

data want;
   set have;
   where  (year(date)+ (month(date) ge 9)) = 2019;
run;

If you are going to repeatedly use a ranges of values you might also consider creating a custom format such as

proc format library=work;
value repyear
'01SEP2000'd - '31AUG2001'd = '2001'
'01SEP2001'd - '31AUG2002'd = '2002'
'01SEP2002'd - '31AUG2003'd = '2003'
'01SEP2003'd - '31AUG2004'd = '2004'
'01SEP2004'd - '31AUG2005'd = '2005'
'01SEP2005'd - '31AUG2006'd = '2006'
'01SEP2006'd - '31AUG2007'd = '2007'
'01SEP2007'd - '31AUG2008'd = '2008'
'01SEP2008'd - '31AUG2009'd = '2009'
'01SEP2009'd - '31AUG2010'd = '2010'
'01SEP2010'd - '31AUG2011'd = '2011'
'01SEP2011'd - '31AUG2012'd = '2012'
'01SEP2012'd - '31AUG2013'd = '2013'
'01SEP2013'd - '31AUG2014'd = '2014'
'01SEP2014'd - '31AUG2015'd = '2015'
'01SEP2015'd - '31AUG2016'd = '2016'
'01SEP2016'd - '31AUG2017'd = '2017'
'01SEP2017'd - '31AUG2018'd = '2018'
'01SEP2018'd - '31AUG2019'd = '2019'
'01SEP2019'd - '31AUG2020'd = '2020'
'01SEP2020'd - '31AUG2021'd = '2021'
'01SEP2021'd - '31AUG2022'd = '2022'
'01SEP2022'd - '31AUG2023'd = '2023'
'01SEP2023'd - '31AUG2024'd = '2024'
'01SEP2024'd - '31AUG2025'd = '2025'
'01SEP2025'd - '31AUG2026'd = '2026'
'01SEP2026'd - '31AUG2027'd = '2027'
'01SEP2027'd - '31AUG2028'd = '2028'
'01SEP2028'd - '31AUG2029'd = '2029'
'01SEP2029'd - '31AUG2030'd = '2030'
;
run;

And you could use the REPYEAR format to create groups for analysis (providing the variable using the format is an actual DATE value) or select values such as

where put(date,repyear.) in ('2018' '2019' 2020');

Re: Date range across years

Tom — Wed, 04 Dec 2019 18:41:22 GMT

If you want your "year" to start on September first then use YEAR.9 interval.

data test;
 do month=1 to 12;
   date =mdy(month,1,2019);
   format date start end yymmdd10.;
   start=intnx('year.9',date,-1,'e')+1;
   end=intnx('year.9',date,0,'e');
   output;
 end;
run;
proc print;
run;

Obs    month          date         start           end

  1       1     2019-01-01    2018-09-01    2019-08-31
  2       2     2019-02-01    2018-09-01    2019-08-31
  3       3     2019-03-01    2018-09-01    2019-08-31
  4       4     2019-04-01    2018-09-01    2019-08-31
  5       5     2019-05-01    2018-09-01    2019-08-31
  6       6     2019-06-01    2018-09-01    2019-08-31
  7       7     2019-07-01    2018-09-01    2019-08-31
  8       8     2019-08-01    2018-09-01    2019-08-31
  9       9     2019-09-01    2019-09-01    2020-08-31
 10      10     2019-10-01    2019-09-01    2020-08-31
 11      11     2019-11-01    2019-09-01    2020-08-31
 12      12     2019-12-01    2019-09-01    2020-08-31