SAS Enterprise Guide 7.1: %SYSFUNC INTNX - Unexpected output

[AndreasF]

Hey Community,

I would like to use create to macro variables with an enddate and startdate in the format 20170817 for Aug, 17, 2017. The "initial" variable report_date is in the format 17.08.2017.

This is the code I use:

/* Variables */
%let report_date = 17.08.2017;
%let EndDate = %sysfunc(inputn(&report_date,ddmmyy10.),yymmddn8.);
%let StartDate = %sysfunc(intnx(month,&EndDate,-12,b),yymmddn8.);
%put &StartDate;

However, I get this warning:

WARNING: An argument to the function INTNX referenced by the %SYSFUNC or %QSYSFUNC macro function is out of range.

OK. Does not work. I think the format 2017-08-17 would also be ok. So I change the code to:

/* Variables */
%let report_date = 17.08.2017;
%let EndDate = %sysfunc(inputn(&report_date,ddmmyy10.),yymmdd10.);
%let StartDate = %sysfunc(intnx(month,&EndDate,-12,b),yymmdd10.);
%put &StartDate;

Ok. It runs without a warning. Here is the output:

1964-06-01

Can anyone help me with that? Ideally with Plan A.

Thanks,

Andreas

[Quentin]

Hi,

Looks like your calculation of &EndDate is doing what you want:

20   %let report_date = 17.08.2017;
21   %let EndDate = %sysfunc(inputn(&report_date,ddmmyy10.),yymmddn8.);
22   %put &=EndDate;
ENDDATE=20170817

When you compute the StartDate via INTNX(), INTNX() needs a date value to use as a base for the calculation.  You gave it EndDate, which has a value of 20170817 which you know means August 17, 2017, but in SAS that number represents a date that is twenty million days after Jan 1, 1960.  Apparently that date is too far into the future to be valid for INTNX.

So the answer is to convert your value 20170817 back into the SAS date for August 17, 2017, which you can do by using the inputn() function again:

23   %let StartDate = %sysfunc(intnx(month,%sysfunc(inputn(&EndDate,yymmdd8.)),-12,b),yymmddn8.);
24   %put &=StartDate;
STARTDATE=20160801

[Ksharp]

%let report_date = 17.08.2017;
%let EndDate = %sysfunc(inputn(&report_date,ddmmyy10.),yymmddn8.);

%let _EndDate = %sysfunc(inputn(&report_date,ddmmyy10.));
%let StartDate = %sysfunc(intnx(month,&_EndDate,-12,b),yymmddn8.);
%put &StartDate;