topic Re: Set Macro Variable to Date Two Years in Past in SAS Programming

Set Macro Variable to Date Two Years in Past

A_SAS_Man — Thu, 25 Jul 2019 13:54:41 GMT

I am attempting to set a macro variable to a date two years in the past. I have tried the following code:

%let lookback = intnx('year',&sysdate9,-2,same);
%put &lookback;

Which then gives me the following in the log:

26         %let lookback = intnx('year',&sysdate9,-2,same);
27         %put &lookback;
intnx('year',25JUL2019,-2,same)

So it doesn't seem to be resolving my variable correctly, but I can't figure out why.

Re: Set Macro Variable to Date Two Years in Past

PaigeMiller — Thu, 25 Jul 2019 14:00:03 GMT

%put &sysdate9;
%put %sysfunc(date());

Look at the results of these two commands.

INTNX and similar calendar functions requires SAS dates, which are integers, not formatted (and human readable) dates.

So &sysdate9 (which produces formatted human readable dates) will not work in INTNX. You need %sysfunc(date()) (which produces a valid SAS date, an integer) in INTNX.

Re: Set Macro Variable to Date Two Years in Past

Astounding — Thu, 25 Jul 2019 14:01:30 GMT

Just a few minor tweaks. Try it this way:

%let lookback = %sysfunc(intnx(year,%sysevalf("&sysdate9"d),-2,same));
%put &lookback;

Re: Set Macro Variable to Date Two Years in Past

novinosrin — Thu, 25 Jul 2019 14:00:58 GMT


%let lookback = %sysfunc(intnx(year,"&sysdate9"d,-2,same),date9.);
%put &=lookback;

Re: Set Macro Variable to Date Two Years in Past

PaigeMiller — Thu, 25 Jul 2019 14:16:45 GMT

@Astounding wrote:

Just a few minor tweaks. Try it this way:
%let lookback = %sysfunc(intnx(year,%sysevalf("&sysdate9"d),-2,same));
%put &lookback;

Adding to my explanation above, the method from @Astounding works because putting &sysdate9 inside double quotes and then appending the letter d on the end — "&sysdate9"d — turns the human readable date that results from &sysdate9 into an integer which is required by INTNX.

My OPINION is that its easier to work with unformatted dates in INTNX and similar functions, rather than format them and then unformat them to get them to work. Naturally, there is a limitation here, if a human has to enter the date into the code (hard code it), then the human has to use some formatted date and you have to convert it to an integer to work in INTNX; but if you want today's date, that doesn't apply.