SAS Programming

DATA Step, Macro, Functions and more
BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
aazzarello
Fluorite | Level 6

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.

1 ACCEPTED SOLUTION

Accepted Solutions
aazzarello
Fluorite | Level 6

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;



View solution in original post

5 REPLIES 5
ballardw
Super User

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
Diamond | Level 26
INTNX('DTDAY', FIRST_STEP_START_DTM,3) as DTM2 format=datetime16.
--
Paige Miller
Tom
Super User Tom
Super User

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
Super User

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
Fluorite | Level 6

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;



SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 1071 views
  • 0 likes
  • 5 in conversation