topic Re: Macro for last day of previous month when the data is numeric type in SAS Enterprise Guide

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

tijanagilic — Sat, 06 Jan 2024 15:54:57 GMT

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!

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

PaigeMiller — Sat, 06 Jan 2024 16:16:22 GMT

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

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

Patrick — Sat, 06 Jan 2024 16:56:14 GMT

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;

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

Tom — Sun, 07 Jan 2024 01:04:16 GMT

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

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

Kurt_Bremser — Sat, 06 Jan 2024 22:03:22 GMT

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

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

tijanagilic — Sun, 07 Jan 2024 17:31:40 GMT

Thank you! It worked 😁

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

Tom — Mon, 08 Jan 2024 16:03:52 GMT

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