Add hours or days to a datetime column in PROC SQL

[aazzarello]

I'm trying to add 3 days (or 72 hours) to each value in my column FIRST_STEP_START_DTM to my table WORK.TABLE1. See my syntax below

PROC SQL;
CREATE TABLE WORK.table2 AS
SELECT DISTINCT
FIRST_STEP_START_DTM,
INTNX('DTDAY', FIRST_STEP_START_DTM,3) as DTM2
FROM WORK.TABLE1
;QUIT;

Though FIRST_STEP_START_DTM has a format and an informat of DATETIME23.3, I'm returned numeric values instead - a value of "2030400000" in every one of my output rows. I assume the problem is that I'm using the incorrect function for my data type, but I can't seem to find any others. Pointing me in the right direction would be greatly appreciated.

[ballardw]

The issue is likely the optional Alignment parameter. If you do not provide the option it uses Beginning of an interval for the result. Beginning of Dtday interval would be midnight; Dtmonth would be the first day of the month; Hour would add the hours but with no minutes or seconds.

If you want the SAME time of day 3 days later us the 'S' or 'SAME' alignment:

INTNX('DTDAY', FIRST_STEP_START_DTM,3, 'S') as DTM2

The other alignment options are 'E' of 'END' or 'M' 'MIDDLE'

[PaigeMiller]

INTNX('DTDAY', FIRST_STEP_START_DTM,3) as DTM2 format=datetime16.

[Tom]

The function looks mainly correct.  You can use the INTNX() function change a DATE, TIME or DATETIME value by some multiple of one of its supported intervals.  Did you want to remove the time of day part of the original value?  If not then make sure to specify the optional fourth argument in the function call.  The default is to align the value to the BEGINNING of the interval.  To keep the time of day part tell it align to the same position in the interval.

INTNX('DTDAY', FIRST_STEP_START_DTM,3,'same')

But you did not tell SAS that you wanted the values to print using a format that would display the value as something a human would recognize as a datetime value.  Make sure to attach your favorite datetime display format to the new variable.

INTNX('DTDAY', FIRST_STEP_START_DTM,3,'same') as DTM2  format=datetime19.

[Kurt_Bremser]

The INTNX function, as used here, returns a datetime value. In SAS, datetimes are represented as a count of seconds, with 1960-01-01T00:00:00 as the zero second. What you see is this raw count, as you did not also assign a suitable display format to make that raw value human-readable.

[aazzarello · Accepted solution]

Thanks for both of the replies! I found that the solution was combining both, I had to convert back to a datetime format, as well as use the optional operator to keep my exact datetime standing within the given interval. Just using the format alone converted all my datetimes to the start of a given day while just using the optional argument still gave me a numeric output, I also had to alter it to say "SAME" instead of start or end.

PROC SQL;
CREATE TABLE WORK.table2 AS
SELECT DISTINCT
FIRST_STEP_START_DTM,
INTNX('DTDAY',FIRST_STEP_START_DTM,3, 'SAME') as DTM2 format=datetime23.3
FROM WORK.TABLE1
;QUIT;