06-13-2017 04:05 AM
Hi SAS Expertise,
I am trying this code:
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?
06-13-2017 04:13 AM - edited 06-13-2017 04:17 AM
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.
06-13-2017 04:18 AM
Depending on the further use of the value, I'd even do
which allows the direct use of &nextdate in any date operation/comparison (no quotes and trailing d needed).
06-13-2017 04:25 AM
06-13-2017 04:29 AM - edited 06-13-2017 04:34 AM
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
06-13-2017 04:31 AM - edited 06-13-2017 04:56 AM
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 :
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".
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.
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)!
where '01jan2017'd >= %sysfunc(intnx(year,%sysfunc(today()),-2,s),best.);
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.