BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Stats10
Fluorite | Level 6

Hello,

 

I've got a macro that the user can define and it will always be in this format (YYYYMM) e.g.

%let filedate = 201906;

 

and I want another macro that will use this filedate and give me the last day of that month using something like this:

%let MonthEnd = %sysfunc(intnx(month,input(cats(&filedate.,01),yymmdd8.),0,E),date9.);  (<-- but this doesn't work)

 

I can make this work in a data step but not in a macro and I don't know what I'm doing wrong. 

 

%let Filedate = 201906;

data test;
format y ddmmyy10.;
y = intnx("month",input(cats(&Filedate.,"01"),yymmdd8.),0,"E");

run;

 

Any ideas?

Thank you

 

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

Macro language does not support the INPUT function.  If you apply %SYSFUNC, you could use either INPUTN or INPUTC:

 

%let MonthEnd = %sysfunc(intnx(month, %sysfunc(inputn(&filedate.01,yymmdd8.)) ,0,E),date9.); 

It's untested at this point, so give it a shot and see if it works for you.

View solution in original post

10 REPLIES 10
Shmuel
Garnet | Level 18

What do you mean by this doesn't work ?

What error or warning do you have in the log?

Stats10
Fluorite | Level 6

Hi Smuel,

 

This is the error message I get when I run this line of code:

 

%let MonthEnd = %sysfunc(intnx(month,input(cats(&filedate.,01),yymmdd8.),0,E),date9.);

 

ERROR: Required operator not found in expression: input(cats(201906,01),yymmdd8.)
ERROR: Argument 2 to function INTNX referenced by the %SYSFUNC or %QSYSFUNC macro function is not a number.
ERROR: Invalid arguments detected in %SYSCALL, %SYSFUNC, or %QSYSFUNC argument list. Execution of %SYSCALL statement or %SYSFUNC
or %QSYSFUNC function reference is terminated.

 

I don't understand what it means and I've tried many different variations.

Shmuel
Garnet | Level 18

I think you need repeat %sysfunc for every sas function, something like:

 

%let MonthEnd = %sysfunc(intnx(month,%sysfunc(input(%sysfunc(cats(&filedate.,01)),yymmdd8.)),0,E),date9.);
Stats10
Fluorite | Level 6
Hi Shmuel, this gives me the same error message.
Astounding
PROC Star

Macro language does not support the INPUT function.  If you apply %SYSFUNC, you could use either INPUTN or INPUTC:

 

%let MonthEnd = %sysfunc(intnx(month, %sysfunc(inputn(&filedate.01,yymmdd8.)) ,0,E),date9.); 

It's untested at this point, so give it a shot and see if it works for you.

Stats10
Fluorite | Level 6

Hi Astounding,

it worked! Thank you so much! So I had to use the %sysfunc again (like Shmuel said) but remove the "cats". I wouldn't have guessed in a million years. I did try the inputn but always kept the "cats" in.Thank you so much for your help!

Tom
Super User Tom
Super User
  • Every function you want to call in macro code needs its own %SYSFUNC() call. 
  • There is no need for CATS() in macro code. Just expand the values next to each other and they are concatenated.
    • You don't need to add the day of the month anyway. Just use the YYMMN informat.
  • You cannot use INPUT(), but must use INPUTN() , or INPUTC() if you want to generate a character value instead of a numeric value.
%let filedate = 201906;
%let monthend= %sysfunc(intnx(month,%sysfunc(inputn(&filedate,yymmn6)),0,E),date9);
376   %put &=monthend;
MONTHEND=30JUN2019

Do you really want the macro variable to have a string that looks like a date value in DATE9 format?  Or do you want it to have an actual date value?  Or perhaps a date literal?  How are you planning to use it?

381   %let monthend= %sysfunc(intnx(month,%sysfunc(inputn(&filedate,yymmn6)),0,E),date9);
382   %put &=monthend;
MONTHEND=30JUN2019
383   %let monthend= %sysfunc(intnx(month,%sysfunc(inputn(&filedate,yymmn6)),0,E));
384   %put &=monthend;
MONTHEND=21730
385   %let monthend= "%sysfunc(intnx(month,%sysfunc(inputn(&filedate,yymmn6)),0,E),date9)"d;
386   %put &=monthend;
MONTHEND="30JUN2019"d

 

Stats10
Fluorite | Level 6
Hi Tom,

I did try the Inputn function but it didn't work either, so I gave up. Yes, I do need the macro to look like a string initially because it's being used elsewhere as well in that format. So I wanted to kill two birds with one stone essentially. This is exactly what I needed. Thanks for your response as well.
Tom
Super User Tom
Super User
Mostly like the problem you had with using INPUTN as you were still not converting all of your other function calls. So the YYMMDD informat will not be able to convert a string like CATS(.... into a valid date.
Stats10
Fluorite | Level 6
Thanks for the extra versions Tom! Cool that I don't need to concatenate this way! Will use that then. 🙂

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 10 replies
  • 7979 views
  • 2 likes
  • 4 in conversation