How to tweak RightNow into showing last day of previous month

[heleenw]

Hi all,
I am sorry but have been breaking my head over following seemingly simple question.
How could I tweak my rightNow macro so that it would not resolve to the current date and time, but to the last day of previous month? I am using
%let rightNow = %sysfunc(date(),date9.)
%sysfunc(time(),timeampm.)

That seems to work ok but I would actually prefer to have it show December 31st (since today it is still January the last day of previous month would be December 31st)
Or, in the case of tomorrow theoretically it should then show Jan 31st (because once we are in February the last day of previous month should be 31-01-2022). Because of formatting used the date displays like this: 19OCT2021:00:00:00.000000 (for example).
How do I go about that?
(NB probably I should also find some other more sensible name for my NotSoVeryRightNow macro, but let's first figure out what to do...)
I hope this question is clear...! Thanks in advance!
Kind regards,
Heleen

[Tom]

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.

[Kurt_Bremser]

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)

[heleenw]

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.

[Kurt_Bremser]

You MUST have 23:59:59 in there, or otherwise anything happening on that day would be counted for the next month.

[Patrick]

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