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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.