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
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.
What do you mean by this doesn't work ?
What error or warning do you have in the log?
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.
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.);
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.
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!
%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
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.