DATA Step, Macro, Functions and more

SQL macro variable Today plus 6 months

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

SQL macro variable Today plus 6 months

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


Accepted Solutions
Solution
2 weeks ago
Super User
Posts: 6,774

Re: SQL macro variable Today plus 6 months

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.

 

View solution in original post


All Replies
Super User
Super User
Posts: 9,599

Re: SQL macro variable Today plus 6 months

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.

 

Respected Advisor
Posts: 3,018

Re: SQL macro variable Today plus 6 months

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

 

No quotes here, quotes would be wrong.

--
Paige Miller
Solution
2 weeks ago
Super User
Posts: 6,774

Re: SQL macro variable Today plus 6 months

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.

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 73 views
  • 2 likes
  • 4 in conversation