BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
mariko5797
Pyrite | Level 9

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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'

 

View solution in original post

4 REPLIES 4
PaigeMiller
Diamond | Level 26
%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 ...

or

 

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 ...

--
Paige Miller
mariko5797
Pyrite | Level 9
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.
Tom
Super User Tom
Super User

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'

 

ballardw
Super User

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)) ;

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 969 views
  • 0 likes
  • 4 in conversation