Calculate Dates using INTNX (Weekly, Monthly, Quarterly, and more)

In this article, I will show you how to calculate the Month End date, and Fiscal Quarter End Date for Canadian Banks.

Automatically Calculate the Month End Date

This code will calculate the previous month end date automatically based on the date of execution of the code.

data viewdates;
today = today();
date1 = intnx('month',today(),-1,'E'); 
format date1 yymmd7.;
format today yymmdd10.;
run;

proc print data=viewdates;
run;

Result

Assigning the Variable to a Macro Variable

So, the month end calculation is done. But, there is one more thing that needs to be done to utilize this variable in another part of the program. It needs to be assigned to a macro variable that can be used anywhere in the program.

So, the final code looks like this:

data viewdates;
today = today();
date1 = intnx('month',today(),-1,'E'); 
format date1 yymmd7.;
format today yymmdd10.;
call symputx('rpdt',put(date1,yymmddn7.),'G');
call symputx('month',put(date1,monname.),'G');
run;

proc print data=viewdates;
run;

/* These two lines will display the result of rpdt and month in the SAS log */
%put rpdt is: &rpdt;
%put month is: &month;

Result of the %put statements

 68         %put rpdt is: &rpdt;
 rpdt is: 2016-10
 69         %put month is: &month;
 month is: October

This will allow you to use the macro variables, “rpdt” and “month” anywhere else in the program as if it was assigned in SAS as:

%let rpdt = 2016-10;
%let month = October;

Calculate the Fiscal Quarter for Canadian Banks

Surprisingly, in my 10 years of contracting primarily at Canadian Banks, I’ve never seen anyone use this. It’s possible to automatically calculate the fiscal quarter end for a Canadian Bank using this intnx parameter below.

data _null_;
	today = today();
	prev_qtr = intnx('month3.2',today,-1,'E');
run;

What this will calculate is the most recent past fiscal quarter end date for Canadian Banks. So, if “today” is Mar 2, 2017 or any day between Feb. 1, 2017 and Apr 30, 2017, then the date PREV_QTR will be: January 31, 2017.

You can change the ‘E’ to a ‘B’ and then you will get a date of November 1, 2016.