topic Re: intnx is not giving the actual date in SAS Programming

intnx is not giving the actual date

imdickson — Tue, 13 Jun 2017 08:05:06 GMT

Hi SAS Expertise,

I am trying this code:

%put %sysfunc(intnx(year,%sysfunc(today()),1),date9.);

Based on the online guide from google, it should return the date 13th Jun 2018 as i want to get 1 year increment from today.

However, I am only getting 1st Jan 2018. May i know which part is wrong/missing from the code above?

Re: intnx is not giving the actual date

PeterClemmensen — Tue, 13 Jun 2017 08:17:19 GMT

Use 'sameday' as the alignment option

data _null_;
	format var date9.;
	var = intnx('year',today(),1,'sameday');
	put var;
run;

Consulting the documentation, the default value of the alignment option is ''beginning". Allowed values are BEGINNING, MIDDLE, END, and SAMEDAY.

http://support.sas.com/documentation/cdl/en/etsug/63939/HTML/default/viewer.htm#etsug_tsdata_sect038.htm

Re: intnx is not giving the actual date

Kurt_Bremser — Tue, 13 Jun 2017 08:18:57 GMT

Depending on the further use of the value, I'd even do

%let nextdate=%sysfunc(intnx(year,%sysfunc(today()),1,s),best.);

which allows the direct use of &nextdate in any date operation/comparison (no quotes and trailing d needed).

Re: intnx is not giving the actual date

imdickson — Tue, 13 Jun 2017 08:25:57 GMT

Hi, i tried 's' but i got error until i saw your reply without the single quotation, it works! However, when i put it in SAS DI Studio, i got this error :
data null;
27 /* set ODMSCDS.FIELD;*/
28 where '2017-01-01 00:00:00'dt >= %sysfunc(intnx(year,%sysfunc(today()),-2,s),date9.);
NOTE: Line generated by the macro function "SYSFUNC".
28 13JUN2015
_______
22
76
ERROR: Syntax error while parsing WHERE clause.
ERROR: No input data sets available for WHERE statement.
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, -, /, <, <=, <>, =, >, >=, AND, EQ, GE, GT, LE, LT,
NE, OR, ^=, |, ||, ~=.

ERROR 76-322: Syntax error, statement will be ignored.

29 run;

Re: intnx is not giving the actual date

PeterClemmensen — Tue, 13 Jun 2017 08:34:24 GMT

I think you have commented out your set statement as

/* set ODMSCDS.FIELD;*/

and you are trying to compare datetime values with dates, which will fail.

And just to avoid confusion when reading code, do not name a SAS dataset null 🙂

Re: intnx is not giving the actual date

Kurt_Bremser — Tue, 13 Jun 2017 08:56:13 GMT

@imdickson wrote:
Hi, i tried 's' but i got error until i saw your reply without the single quotation, it works! However, when i put it in SAS DI Studio, i got this error :
data null;
27 /* set ODMSCDS.FIELD;*/
28 where '2017-01-01 00:00:00'dt >= %sysfunc(intnx(year,%sysfunc(today()),-2,s),date9.);
NOTE: Line generated by the macro function "SYSFUNC".
28 13JUN2015
_______
22
76
ERROR: Syntax error while parsing WHERE clause.
ERROR: No input data sets available for WHERE statement.
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, -, /, <, <=, <>, =, >, >=, AND, EQ, GE, GT, LE, LT,
NE, OR, ^=, |, ||, ~=.

ERROR 76-322: Syntax error, statement will be ignored.

29 run;

That's why I gave you the hint with using the best. format to obtain the raw date value, which can easily be used in such situations;

but you have an apple/orange problem, because you compare a datetime literal with a date value, which will surely fail. And the datetime literal was not correctly written (datetime literals also use the SAS date. format)!

Use

where '01jan2017'd >= %sysfunc(intnx(year,%sysfunc(today()),-2,s),best.);

instead.

PS after rethinking it, I'm wondering what you try to accomplish. Depending on today's date, you'd either get all observations from the dataset or none, as you are in fact comparing two literals.