Macro for last day of previous month when the data is numeric type

[tijanagilic]

Hello,

I am using Enterprise Guide, and I have many tables that contain a column that contains a date that is in the form, e.g. 20231231 or 20231031. The column is numeric type and I need a macro variable, which when I put it in the filter, will eject the data for the last day of the previous month.

Data in the columns

And i want to be able to put macro, like this in any table.

Thanks in advance!

[PaigeMiller]

As in your previous question, we need to see the PROC CONTENTS results for this variable.

[Patrick · Accepted solution]

OK - so you've got just a number and not a SAS Date value in your data:

And you want a macro variable with such a number for the last day of the previous month based on the date when you are running the code.

%let previous_month=%sysfunc(intnx(month,%sysfunc(today()),-1,e),yymmddn8.);
%put &=previous_month;

[tijanagilic]

Thank you! It worked 😁

[Tom]

No need to define a macro or even create a macro variable.

Instead you can just call a macro function.

Put this code into the value of your filter:

%sysfunc(intnx(month,%sysfunc(date()),-1,e),yymmddN8.)

[Kurt_Bremser]

I think it should be the YYMMDDN8. format, otherwise the date would come out as YY-MM-DD.

[Tom]

---

@Kurt_Bremser wrote:

I think it should be the YYMMDDN8. format, otherwise the date would come out as YY-MM-DD.

---

Thanks.  Updated the answer.