topic Re: SQL macro variable Today plus 6 months in SAS Programming

SQL macro variable Today plus 6 months

JMart — Thu, 05 Jul 2018 15:21:19 GMT

I would like to put a macro variable that would reference Today plus 6 months.

In my initial statement I use to change the date manually in my macro and it works fine but now I want to upgrade my macro so that it calculate Today plus 6 months without me changing it manually.

My original statement look like this : %let Date6mths=5JAN2018;

Then I tried a couple of variation of this code to have this date automatically calculated from the date of production:

%let Date6mths="%sysfunc(INTNX(Month,Today(),6,s))";

- with double quote around the %sysfunc

- without double quote and with single quote for the parameter within the Intnx function

and I also tried this variation : %let Date6mths=INTNX('Month',Today(),6,'s'))";

I use the macro within a Proc SQL

Thx you all for your help

Re: SQL macro variable Today plus 6 months

RW9 — Thu, 05 Jul 2018 15:28:31 GMT

And why do you need one? You already have the logic which you can use in the code:

intnx('month',today(),6,'s)

So just populate that through the code where you need to use the date + 6 months. Saves code creating that, storage of a macro variable which is text to hold it, then more code to convert it to numeric when used.

Oh, and to correct your code you don't need the double quotes around the sysfunc.

Re: SQL macro variable Today plus 6 months

PaigeMiller — Thu, 05 Jul 2018 15:29:20 GMT

%let Date6mths=%sysfunc(INTNX(Month,%sysfunc(Today()),6,s));

No quotes here, quotes would be wrong.

Re: SQL macro variable Today plus 6 months

Astounding — Thu, 05 Jul 2018 15:30:23 GMT

Probably close enough:

%let result =%sysfunc(INTNX(Month,"&sysdate9"d,6,s),date9);

Technically, &sysdate9 is set when the program begins to execute. It contains the current date (just like the TODAY function), but it won't change if the program continues to execute past midnight. In practice, most of the time nobody cares about that.