topic Re: how to get the last day of the month using %sysfunc and intnx function in SAS Enterprise Guide

how to get the last day of the month using %sysfunc and intnx function

alepage — Tue, 18 Nov 2025 22:42:59 GMT

I would like to get this macro variable calculatedDate equal to : '2025-09-30' and between single quote

Please help.

%let mm=09;
%let yyyy=2025;
%let calculatedDate = %sysfunc(INTNX("MONTH", MDY(&mm., 1, &yyyy.), 1)-1);
%put &=calculatedDate;

Re: how to get the last day of the month using %sysfunc and intnx function

quickbluefish — Tue, 18 Nov 2025 23:22:50 GMT

Unless you really need to do this entirely with %let statements, I would just do this:

%let mm=09;
%let yyyy=2025;

data _null_;
call symputx("calculatedDate", compress("'" || put(intnx("month", mdy(&mm,1,&yyyy), 1)-1, date9.) || "'"));
run;
%put &=calculatedDate;

Re: how to get the last day of the month using %sysfunc and intnx function

sbxkoenk — Wed, 19 Nov 2025 00:00:12 GMT

Solved: How to find the last day of the month - SAS Support Communities

Compute the first or last day of a month or year - The DO Loop

Ciao,
Koen

Re: how to get the last day of the month using %sysfunc and intnx function

Ksharp — Wed, 19 Nov 2025 07:30:34 GMT

%let mm=09;
%let yyyy=2025;
%let calculatedDate = %sysfunc(INTNX(MONTH, %sysfunc(MDY(&mm., 1, &yyyy.)),0,e),yymmdd10.);
%put &=calculatedDate;

159  %let mm=09;
160  %let yyyy=2025;
161  %let calculatedDate = %sysfunc(INTNX(MONTH, %sysfunc(MDY(&mm., 1, &yyyy.)),0,e),yymmdd10.);
162  %put &=calculatedDate;
CALCULATEDDATE=2025-09-30

Re: how to get the last day of the month using %sysfunc and intnx function

alepage — Wed, 19 Nov 2025 14:53:04 GMT

%let mm=09;
%let yyyy=2025;
%let EOM = %sysfunc(quote(%sysfunc(INTNX(MONTH, %sysfunc(MDY(&mm., 1, &yyyy.)),0,e),yymmdd10.),%str(%'))); /* EOM = End Of the Month */
%put &=EOM;

Re: how to get the last day of the month using %sysfunc and intnx function

PaigeMiller — Wed, 19 Nov 2025 15:01:30 GMT

I'm curious why you marked a solution correct when that solution didn't meet the requirements of having the date within single quotes. Is that no longer a requirement?

Re: how to get the last day of the month using %sysfunc and intnx function

sbxkoenk — Wed, 19 Nov 2025 15:13:57 GMT

pertinent remark @PaigeMiller .

I enriched the solution of @Ksharp with an extra line to have the single quotes around the formatted date.

%let mm=09;
%let yyyy=2025;
%let calculatedDate = %sysfunc(INTNX(MONTH, %sysfunc(MDY(&mm., 1, &yyyy.)),0,e),yymmdd10.);
%put &=calculatedDate;

%let calculatedDateWithSingleQuotes = %str(%')&calculatedDate%str(%');
%put &=calculatedDateWithSingleQuotes;

Ciao,

Koen

Re: how to get the last day of the month using %sysfunc and intnx function

Tom — Wed, 19 Nov 2025 15:20:09 GMT

@alepage wrote:

I would like to get this macro variable calculatedDate equal to : '2025-09-30' and between single quote

Please help.
%let mm=09;
%let yyyy=2025;
%let calculatedDate = %sysfunc(INTNX("MONTH", MDY(&mm., 1, &yyyy.), 1)-1);
%put &=calculatedDate;

First thing is the the INTNX() function is not going to understand an interval name that includes quote characters in it. It is also not going to like receiving a string for an argument that requires a DATE value. Try it in normal SAS code and see what it does.

data _null_;
  date = intnx('"MONTH"','MDY(09, 1, 2025)',1);
run;

If you want the macro processor to run the MDY() function you need to also wrap it inside the %SYSFUNC() macro function.

Also if you want INTNX() to return the end of the interval instead of the beginning of the interval then just tell it so instead of accepting the default of the beginning of the value.

And if you want the date formatted as a string in YYYY-MM-DD style then tell %SYSFUNC() to use the YYMMDD10. format when returning its result, instead of just converting the date into a digit string.

%let calculatedDate = %sysfunc(MDY(&mm,1,&yyyy));
%let calculatedDate = %sysfunc(INTNX(MONTH,&calculatedDate,0,end),yymmdd10.);

You can use %BQUOTE() to add single quotes and still resolve macro variable references.

%let calculatedDate = %bquote('&calculatedDate');

If the macro quoting that %BQUOTE() adds causes trouble then remove it.

%let calculatedDate = %unquote(&calculatedDate);

You could nest the function calls, but why make the code harder to understand?

Re: how to get the last day of the month using %sysfunc and intnx function

alepage — Wed, 19 Nov 2025 15:20:33 GMT

No it seems that I need the single quote with a snowflake sql query. But it is a way to thanks that person for giving me the major part of the solution.

Re: how to get the last day of the month using %sysfunc and intnx function

PaigeMiller — Wed, 19 Nov 2025 15:46:16 GMT

@alepage wrote:
No it seems that I need the single quote with a snowflake sql query. But it is a way to thanks that person for giving me the major part of the solution.

Okay that's a good thing to do, but it would be even better if you explained to us why it was marked correct, without us having to ask.

Re: how to get the last day of the month using %sysfunc and intnx function

Ksharp — Thu, 20 Nov 2025 07:43:31 GMT

As Tom showed you ,if you need add single quote ,you need use macro masked function %bquote() or %superq() or %str().

%let mm=09;
%let yyyy=2025;
%let calculatedDate ='%sysfunc(INTNX(MONTH, %sysfunc(MDY(&mm., 1, &yyyy.)),0,e),yymmdd10.)';
%put %bquote(&calculatedDate);

5    %let mm=09;
6    %let yyyy=2025;
7    %let calculatedDate ='%sysfunc(INTNX(MONTH, %sysfunc(MDY(&mm., 1, &yyyy.)),0,e),yymmdd10.)';
8    %put %bquote(&calculatedDate);
'2025-09-30'

And if you are running into some problem, you also need help of %unquote() as Tom showed you .

%unquote(  %bquote(&calculatedDate)  );

Re: how to get the last day of the month using %sysfunc and intnx function

Quentin — Thu, 20 Nov 2025 13:33:58 GMT

Thanks @Ksharp .

I didn't think your code would work because calculatedDate stores a single quoted string with calls to %sysfunc:

1    %let mm=09;
2    %let yyyy=2025;
3    %let calculatedDate ='%sysfunc(INTNX(MONTH, %sysfunc(MDY(&mm., 1, &yyyy.)),0,e),yymmdd10.)';
4    %put &=calculatedDate;
CALCULATEDDATE='%sysfunc(INTNX(MONTH, %sysfunc(MDY(&mm., 1, &yyyy.)),0,e),yymmdd10.)'

I guess I forgot (or never knew?) that %bquote will resolve macro triggers inside a single quoted string.

5    %put %bquote(&calculatedDate);
'2025-09-30'

Re: how to get the last day of the month using %sysfunc and intnx function

Ksharp — Fri, 21 Nov 2025 06:32:11 GMT

Quentin,
Yes. %bquote will mask single quote inside a macro variable. As Tom already said and showed .