Creating specific date ranges based on dynamic prior month

[acrosb]

Hi community,

I am trying to automate a report. When I run the report at the beginning of the month, I want all records from the prior month. The tricky part is billing handles the last day of the monthly differently - all reporting months end on the 30th, except Feb, which end on the 28th. Therefore, I want to set the date range based on the reporting month. Here's what I have so far:

data _null_;
*Get prior month (i.e reporting month (rm));
call symputx('rm', PUT(intnx('MONTH',today()-0,-1,'s'), monname3.)); 
*Get the year of the prior month (i.e reporting month year);
call symputx('rmy', PUT(intnx('MONTH',today()-0,-1,'s'), year4.));
*Get prior 2 month (i.e reporting month (rm2));
call symputx('rm2', PUT(intnx('MONTH',today()-0,-2,'s'), monname3.)); 
*Get the year of the prior 2 month (i.e reporting month year 2);
call symputx('rmy2', PUT(intnx('MONTH',today()-0,-2,'s'), year4.));
run;
*Test macros. If I ran in Feb 2021, I'd get:;
%put &rm; *Jan;
%put &rmy; *2021;
%put &rm2; *Dec;
%put &rmy2; *2020;

*Now I want to create code that does the following: 
*If reporting month is Jan, then keep only records where Dec 31, 2020 <= Date <= Jan 30, 2021;
*If reporting month is Feb, then keep only records where Jan 31, 2020 <= Date <= Feb 28, 2021;
*etc. for the rest of the months;

*Here's how I'm able to make the timestamp field dynamic, but I can't figure out how apply 
specific ranges based on the reporting month;

*Example if I want Jan report, I want range to be 31Dec2020 to 30Jan2021;
%let yyyy=&rmy;
%let mm=&rm;
%let xxxx=&rmy2;
%let nn=&rm2;

data Test&mm.&yyyy.;
set data;
where "30&nn&xxxx:0:0:0"dt <= Date <= "30&mm&yyyy:0:0:0"dt;
run;

Does anyone know how to do this? Should I change my approach - Is there a better way to fulfill this reporting requirement?

A

[PaigeMiller]

Don't work with dates/months/years as character strings. SAS has so many built-in functions to handle dates as numeric values, and (for example) which know that December has 31 days and November has 30 days, that there is no reason for you to create your own method using character strings.

How about this:

data _null_;
call symputx('today',intnx('month',today(), 0,'b')); /* First day of current month */
run;
%put &=today;


data test;
    input date :date9.;
    format date date9.;
cards;
01APR2021
01MAR2021
01FEB2021
01JAN2021
01DEC2020
01NOV2020
;


data test%sysfunc(putn(&today,yymmn6.));
    set test;
    if intnx('month',today(),-1,'e')-1 <= date <= intnx('month',today(),0,'e')-1;
run;

Note: your original code seems to indicate that the variable DATE in data set DATA is actually a datetime variable. If that's the case, change 'month' to 'dtmonth' in the next to last line above.

[acrosb]

The reason I'm creating my own month ranges is because, for some odd billing reason, my reporting month range does not align with the calendar month. All reporting months end on the 30th, except February, which end on the 28th/29th. 

Jan reporting range 12/31 - 1/30 (note: error in my original post, start is 12/31, not 12/30)

Feb: 1/31 - 2/28

Mar: 3/1 - 3/30

Apr: 3/31 - 4/30

May: 5/1 - 5/30

Jun: 5/31 - 6/30

etc. 

I don't see a way to automatically code this, other than hard card the range for each month.

[PaigeMiller]

---

@acrosb wrote:

The reason I'm creating my own month ranges is because, for some odd billing reason, my reporting month range does not align with the calendar month. For example, the range for the Jan report is actually Dec 30 - Jan 30.

---

Yes and my code takes care of that. If you have to write your own code breaking down character strings, you'll work a lot harder.

[LeonidBatkhan]

Since @acrosb is reporting on the previous month, I think the range should be:

if intnx('month',today(),-1,'b') - 1 <= date <= intnx('month',today(),-1,'e') - 1;

[PaigeMiller]

---

@LeonidBatkhan wrote:

Since he is reporting on the previous month, I think the range should be:

if intnx('month',today(),-1,'b') - 1 <= date <= intnx('month',today(),-1,'e') - 1;

---

That doesn't seem to match the stated requirement of

If reporting month is Jan, then keep only records where Dec 30, 2020 <= Date <= Jan 30, 2021;

and I think my code does match 

However, my code doesn't match this requirement

*If reporting month is Feb, then keep only records where Jan 31, 2020 <= Date <= Feb 28, 2021;

Looking back, I mis-read the original statement from @acrosb where reporting ends on the 30th of each month, except Feb which ends on the 28th (what about leap years?)

So, for now I withdraw my earlier code, and will work on the proper code.

[acrosb]

Yes, I should be more clear on the ranges. For billing reasons the reporting month always ends on the 30th, except Feb, which ends of the 28th (or 29th if a leap year).

Jan reporting range 12/31 - 1/30 (note: error in my original post, start is 12/31, not 12/30)

Feb: 1/31 - 2/28

Mar: 3/1 - 3/30

Apr: 3/31 - 4/30

May: 5/1 - 5/30

Jun: 5/31 - 6/30

Jul: 7/1 - 7/30

Aug: 7/31 - 8/30

Sep: 8/31 - 9/30

Oct: 10/1 - 10/30

Nov: 10/31 - 11/30

Dec: 12/1 - 12/30

[PaigeMiller · Accepted solution]

data test%sysfunc(putn(&today,yymmn6.));
    set test;
    curr_month=intnx('month',&today,0,'b');
    prev_month=intnx('month',&today,-1,'b');
    if month(curr_month)=2 then end_reporting_day=28;
    else end_reporting_day=30;
    if month(prev_month)=2 then prev_month_end_reporting_day=28;
    else prev_month_end_reporting_day=30;
    if (mdy(month(prev_month),prev_month_end_reporting_day,year(prev_month))+1)<= date
        <= mdy(month(curr_month),end_reporting_day,year(curr_month));
run;

I think this incorporates the rule properly now. Feb reporting ends on the 28th, all other months reporting ends on the 30th.

[acrosb]

This looks like exactly what's I've been looking for! Let me test it out.

[acrosb]

Thank you so much for your help! This works great. (Although I need to adjust it so it references last month for the report, but easy to do). Below is the data I used for testing (for anyone's reference). I'm still working on adding it to my program, but I see how it works.

data mydata;
    input Bill $ date :date9.;
    format date date9.;
cards;
Bill1 15OCT2020	
Bill2 05FEB2021
Bill3 08MAR2021
Bill4 17MAR2021
Bill5 21APR2021
Bill6 10APR2021
Bill7 30APR2021
;
RUN;

[ballardw]

---

@acrosb wrote:

Yes, I should be more clear on the ranges. For billing reasons the reporting month always ends on the 30th, except Feb, which ends of the 28th (or 29th if a leap year).

Jan reporting range 12/31 - 1/30 (note: error in my original post, start is 12/31, not 12/30)

Feb: 1/31 - 2/28

Mar: 3/1 - 3/30

Apr: 3/31 - 4/30

May: 5/1 - 5/30

Jun: 5/31 - 6/30

Jul: 7/1 - 7/30

Aug: 7/31 - 8/30

Sep: 8/31 - 9/30

Oct: 10/1 - 10/30

Nov: 10/31 - 11/30

Dec: 12/1 - 12/30

---

It might be a better idea to use the "month" the report is prepared in for your example as I am pretty confused at this point what "FEB" above represents.

You have some periods, 5 of 12, starting on the first day of a month and others starting on the last day of the previous month. Which does not come at all close to your stated boundaries. So you need to explicitly state what the exceptions and rules involved might be.

[ballardw]

One of the first things I would do if this were my project is to stop calling a datetime value a "DATE". The two in SAS use different measurement scales for starters. If I see a variable with "date" in it then I expect certain behavior and almost none of the datetime values will be "nice" in that respect. If all of your "date" values have the time component of 00:00:00 then ditch the datetime and just create a proper date to save a lot of headaches. I see lots of this garbage from lazy Microsoft application designers, no need to continue to use "time" if there isn't really any. The DATEPART function will strip out the date portion to leave just a date value.

Second, create actual date or datetime values in the macro variables instead of formatted text using "<macro string"dt for use in the WHERE statement(s).

You will save a lot of headaches in the long run.

Third, it might help to use slightly more meaningful names.

It would help to show the actual values you want to create.

From your descripton I am guessing that for a report run in APRIL you want dates between the last day of Feb and the next to last day of March. This does that:

data example;
   endofmonthprior = intnx('month',today(),-1,'b') - 1;
   endoflastmonth  = intnx('month',today(),-1,'e') - 1;
   format endofmonthprior endoflastmonth date9.;
run;

I really didn't understand why you using the 's' alignment when you wanted the beginning or end of a month.

And no macro variables are need if you are doing that for a single "where" assuming the dates generated in my Example date set are the ones you actually want.

data test;
  set <your data set name>;
  where (intnx('month',today(),-1,'b') - 1) le date le
        (intnx('month',today(),-1,'e') - 1);
  /* of if you keep "date" as datetime*/
/*   where (intnx('month',today(),-1,'b') - 1) le datepart(date) le*/
/*        (intnx('month',today(),-1,'e') - 1);*/
run;

[ErikLund_Jensen]

Hi @acrosb 

It seems from your examples that December is treated differently, so both DEC 30 and DEC 31 is reported i January, but only JAN 31 is reported in February and so on. Is that an error or something to work into the code when reporting for December and January?