topic Re: Adding month to datetime20.0 value in SAS Procedures

Adding month to datetime20.0 value

cynthya — Thu, 07 Nov 2013 20:12:51 GMT

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.

jakarman — Thu, 07 Nov 2013 20:21:51 GMT

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.

Tom — Thu, 07 Nov 2013 20:24:17 GMT

Use the INTNX() function.

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

cynthya — Thu, 07 Nov 2013 20:30:45 GMT

Thank you very much Tom.

This works perfectly.