Hi,
I have a table with date values. The dates in datetime20.0 format.
I would like to add 22 months to those value using an sql procedure.
What is the easiest way to do this?
Thank you for your help and time.
Use the INTNX() function.
SAS(R) 9.2 Language Reference: Dictionary, Fourth Edition
If you are using DATETIME20.0 format then you have DATETIME values instead of DATE values. So use the DTMONTH interval instead of the MONTH interval.
data _null_;
now=datetime();
future = intnx('dtmonth',now,22-1,'S');
format now future datetime22.;
put (_all_) (=/);
run;
now=07NOV2013:15:23:08
future=07AUG2015:15:23:08
You can use the function call in SQL the same as you would in a DATA step, but you might need to give the result a variable name.
select mydate
, intnx('dtmonth',MYDATE,22-1,'S') as future_date format=datetime22.
from mytable
SAS(R) 9.3 Functions and CALL Routines: Reference (intnx and the related fucntions)
Be aware as this is a SAS function Performance will be the best wiht that for SAS-datasets.
When you are dealing with an external DBMS this can become challenging as date/time values are not standardized.
Use the INTNX() function.
SAS(R) 9.2 Language Reference: Dictionary, Fourth Edition
If you are using DATETIME20.0 format then you have DATETIME values instead of DATE values. So use the DTMONTH interval instead of the MONTH interval.
data _null_;
now=datetime();
future = intnx('dtmonth',now,22-1,'S');
format now future datetime22.;
put (_all_) (=/);
run;
now=07NOV2013:15:23:08
future=07AUG2015:15:23:08
You can use the function call in SQL the same as you would in a DATA step, but you might need to give the result a variable name.
select mydate
, intnx('dtmonth',MYDATE,22-1,'S') as future_date format=datetime22.
from mytable
Thank you very much Tom.
This works perfectly.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.