topic Create a macro that stores the date of the last day of the previous month in SAS Programming

Create a macro that stores the date of the last day of the previous month

mariko5797 — Mon, 29 Aug 2022 16:43:04 GMT

As the title says, I would like to create a macro that stores the last date of the previous month so I don't have to constantly update my code each month.

My current code would only work assuming I ran the report on the 1st of the current month. However that won't always be the case. I tried using the 'B' and 'E' intnx options, but came up with an error:

/*Current code: no B or E option*/
%let period= %sysfunc(intnx(month, %sysfunc(today()), -1), date9.)-%sysfunc(intnx(day, %sysfunc(today()), -1), date9.);

/*Log results with B and E option*/
18   %let period= %sysfunc(intnx(month, %sysfunc(today()), -1, %sysfunc('B')), date9.)-%sysfunc(intnx(month, %sysfunc(today()), -1, %sysfunc('E')),
18 ! date9.);
ERROR: Function name missing in %SYSFUNC or %QSYSFUNC macro function reference.
WARNING: Argument 4 to function INTNX referenced by the %SYSFUNC or %QSYSFUNC macro function is out of range.

Re: Create a macro that stores the date of the last day of the previous month

PaigeMiller — Mon, 29 Aug 2022 16:58:13 GMT

%let period= %sysfunc(intnx(month, %sysfunc(today()), -1, b), date9.)-%sysfunc(intnx(month, %sysfunc(today()), -1,e),date9.);

You would be wise not to format these, and not to use them in a character string such as 01JUL2022-31JUL2022

Instead, you want to create a start date of the time period (unformatted) and an end data of the time period (unformatted) and then you can do something like

if date>=&start_date and date<=&end_date then ...

if date between &start_date and &end_date then ...

usually formatting is an unnecessary step that just requires more programming ... first you have to format the dates, then you have to unformat them to do the if statement ...

Re: Create a macro that stores the date of the last day of the previous month

Tom — Mon, 29 Aug 2022 17:04:37 GMT

I don't see where you tried to give INTNX() the B or E option.

The E option seems to be what you need, the B option is the default so no need to add that.

2941  %let period= %sysfunc(intnx(month, %sysfunc(today()), -1), date9.)
2942  -%sysfunc(intnx(month, %sysfunc(today()), -1, e), date9.);
2943  %put &=period;
PERIOD=01JUL2022 -31JUL2022

The INTNX() function does not allow options of 'B' or 'E', Just B, E or S (or their longer versions).

%SYSFUNC() does not know about (and neither do I) any SAS function named 'B' or 'E'

Re: Create a macro that stores the date of the last day of the previous month

mariko5797 — Mon, 29 Aug 2022 17:03:30 GMT

I guess I should have clarified that I’m using this macro variable to just
change one of the report headers with ease. I’m not using it in any actual
calculations.

It definitely wouldn’t kill me to manually change the dates, but it’s
something I easily forget, so I figured automating it a little would be
better.

Re: Create a macro that stores the date of the last day of the previous month

ballardw — Mon, 29 Aug 2022 17:08:09 GMT

use a DATE value to start, the intnx function with month as the interval, tell it to use the previous month and the END basis.

I have no idea why you placing a dash in the middle but unless your only use is for a display in a title or such bodes ill when used.

The interval alignment options 'B','E' and 'S' are not functions so should not have %sysfunc to set them and is causing your errors. Also in the macro language the quotes aren't wanted. The call for the end of the previous month would be (without format)

%sysfunc(intnx(month, %sysfunc(today()), -1, E)) ;