topic Re: Convert text to date in macro in SAS Programming

Convert text to date in macro

Stats10 — Thu, 01 Aug 2019 13:19:13 GMT

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

Re: Convert text to date in macro

Shmuel — Thu, 01 Aug 2019 13:47:54 GMT

What do you mean by this doesn't work ?

What error or warning do you have in the log?

Re: Convert text to date in macro

Stats10 — Thu, 01 Aug 2019 13:52:03 GMT

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.

Re: Convert text to date in macro

Shmuel — Thu, 01 Aug 2019 14:06:39 GMT

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

Re: Convert text to date in macro

Astounding — Thu, 01 Aug 2019 14:05:43 GMT

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.

Re: Convert text to date in macro

Stats10 — Thu, 01 Aug 2019 14:07:57 GMT

Hi Shmuel, this gives me the same error message.

Re: Convert text to date in macro

Stats10 — Thu, 01 Aug 2019 14:12:34 GMT

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!

Re: Convert text to date in macro

Tom — Thu, 01 Aug 2019 14:22:13 GMT

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

Re: Convert text to date in macro

Stats10 — Thu, 01 Aug 2019 14:21:05 GMT

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.

Re: Convert text to date in macro

Stats10 — Thu, 01 Aug 2019 14:27:51 GMT

Thanks for the extra versions Tom! Cool that I don't need to concatenate this way! Will use that then. 🙂

Re: Convert text to date in macro

Tom — Thu, 01 Aug 2019 14:28:43 GMT

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.