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

[alepage]

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;

[quickbluefish]

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;

[sbxkoenk]

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

[Ksharp · Accepted solution]

%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

[alepage]

%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;

[PaigeMiller]

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?

[sbxkoenk]

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

[alepage]

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.

[PaigeMiller]

---

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

[Tom]

---

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

[Ksharp]

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

[Quentin]

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'

[Ksharp]

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