Just to be clear you are talking about a macro variable, not an actual macro. I do not see any macro definition in your code or any attempt to execute any macros.
The INTNX() function can let you adjust date,time or datetime values based on various intervals.
So to get the end of the previous month as a datetime value (number of seonds since 1960) you could use.
%let rightNow_seconds = %sysfunc(intnx(dtmonth,%sysfunc(datetime()),-1,e));
This will work fine for any normal usage.
If you want to convert that value into something humans can understand use a format. I wouldn't use something display dates in MDY or DMY order as either choice will confuse half of your audience. So use DATETIME or something like E8601DT.
If you just want the end of the month then perhaps you should just use a DATE value and not a DATETIME value.
%let rightNow_days = %sysfunc(intnx(month,%sysfunc(date()),-1,e));
Now you can use PUTN() to convert those into human readable strings.
%put The last day of last month is %sysfunc(putn(&rightnow_days,yymmdd10.)). ;
Example:
2453 %let rightNow_days = %sysfunc(intnx(month,%sysfunc(date()),-1,e)); 2454 %put The last day of last month is %sysfunc(putn(&rightnow_days,yymmdd10.)). ; The last day of last month is 2021-12-31.
If you want a datetime at the end of the previous month, use
%let rightnow=%sysfunc(intnx(dtmonth,%sysfunc(datetime()),-1,e));
data _null_;
x = &rightnow;
put x e8601dt19.;
run;
(the data step is there only to show that rightnow has the correct value)
Note that macro variables are best left unformatted if you want to use them in code (for calculations or comparisons). Formats are only needed if the macro variable in question is to be used for display (e.g. in a TITLE statement)
Hi all,
Useful suggestions...
But, hmmm, we are not yet fully there I'm afraid...
When I make use of
%let rightNow_days = %sysfunc(intnx(month,%sysfunc(date()),-1,e)); I obtain the last day of last month but it is needed to append some zeroes to it to get it to be useable in a filter (my filter currently shows dates such as 31MAR2022:00:00:00)
When I adjust the format, I will then obtain a timestamp that's measured down to seconds, i.e. all the seconds that have passed, which is truthful, but will never equal the 31MAR2022:00:00:00, but rather be something like 31MAR2022:59:49:00. So I need to do some modification in order to get to the same that the filter is using.
You MUST have 23:59:59 in there, or otherwise anything happening on that day would be counted for the next month.
@heleenw Below for you to pick & chose
%let curr_month_day1_dttm=%sysfunc(intnx(dtmonth,%sysfunc(datetime()),0,b));
%put &=curr_month_day1_dttm;
%let curr_month_fmt_day1_dttm=%sysfunc(intnx(dtmonth,%sysfunc(datetime()),0,b),datetime21.);
%put &=curr_month_fmt_day1_dttm;
%let last_month_lastday_dttm=%sysfunc(intnx(dtmonth,%sysfunc(datetime()),-1,e));
%put &=last_month_lastday_dttm;
%let last_month_fmt_lastday_dttm=%sysfunc(intnx(dtmonth,%sysfunc(datetime()),-1,e),datetime21.);
%put &=last_month_fmt_lastday_dttm;
28 %let curr_month_day1_dttm=%sysfunc(intnx(dtmonth,%sysfunc(datetime()),0,b)); 29 %put &=curr_month_day1_dttm; CURR_MONTH_DAY1_DTTM=1969660800 30 31 %let curr_month_fmt_day1_dttm=%sysfunc(intnx(dtmonth,%sysfunc(datetime()),0,b),datetime21.); 32 %put &=curr_month_fmt_day1_dttm; CURR_MONTH_FMT_DAY1_DTTM=01JUN2022:00:00:00 33 34 %let last_month_lastday_dttm=%sysfunc(intnx(dtmonth,%sysfunc(datetime()),-1,e)); 35 %put &=last_month_lastday_dttm; LAST_MONTH_LASTDAY_DTTM=1969660799 36 37 %let last_month_fmt_lastday_dttm=%sysfunc(intnx(dtmonth,%sysfunc(datetime()),-1,e),datetime21.); 38 %put &=last_month_fmt_lastday_dttm; LAST_MONTH_FMT_LASTDAY_DTTM=31MAY2022:23:59:59
If there is a possibility that you have to compare to a datetime value with fractional seconds then to be on the very very safe side I wouldn't go for 31MAY2022:23:59:59 with a LE comparison but for 01JUN2022:00:00:00 with a LT comparison.
...or alternatively work with SAS Date values. If your source variables contain SAS Datetime values then using the datepart() function for the comparison will convert such values to SAS Date values.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.