today() Custom Replacement

[BCNAV]

I have the following in a macro:

%let date2 = %sysfunc(intnx(month,%sysfunc(today()),-15,end));

I would like to replace the today() with a custom field for a date:

%let yearuse = 2019;

%let monthuse = 3;

%let date2 = %sysfunc(intnx(month,%sysfunc(today()),-15,end));

What would I replace the today() above with?  That is, I want 15 months back from the 2019 month 3 above it.

Thx!

[Astounding · Accepted solution]

Assuming you correct these invalid statements:

%yearuse = 2019;

%monthuse = 3;

there would be a few ways.  Here's one:

%let date2 = %sysfunc(intnx(month, %sysfunc(mdy(&monthuse, 1, &yearuse)),-15,end));

[mkeintz]

This is, I think, the right way to establish the value for macrovar DATE2.  The only downside I see is that it is a numeric value (21184 in the case of year=2019, month=3).  It will work in any date filtering (i.e.   WHERE MYDATE >= &DATE2), but of course is not a user-friendly way to understand what date is being represented.

I'd suggest considering the format option in the SYSFUNC function (the ",date9." portion below): 

%let date2b= %sysfunc(intnx(month, %sysfunc(mdy(&monthuse, 1, &yearuse)),-15,end),date9.);
%put &=date2b;

Then DATE2B will have the value 31DEC2017.  Much easier for the user to read.  And not much harder to use as a date filter.  Just let SAS know it is a date literal, as in WHERE MYDATE >="&date2b"d.

[BCNAV]

thanks!

[Kurt_Bremser]

Replace today() with mdy(&monthuse,1,&yearuse)

[Tom]

You can't use your numeric month in a date literal.  You could use it in the MDY() function. You could use the first day of the year in the date in the date literal and use the month number to change the number of months you need to offset by.

%let yearuse = 2019;
%let monthuse = 10;

%let date1 = %sysfunc(intnx(month,%sysfunc(today()),-15,end));
%let date2 = %sysfunc(intnx(month,%sysfunc(mdy(&monthuse,1,&yearuse)),-15,end));
%let date3 = %sysfunc(intnx(month,"01JAN&yearuse"d,&monthuse-1-15,end));

8     %put &=date1 &=date2 &=date3 ;
DATE1=21396 DATE2=21396 DATE3=21396
9     %put %sysfunc(putn(&date1,date9.));
31JUL2018