BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Sandeep77
Lapis Lazuli | Level 10

Hi experts,

I have used a code to add the number of days to a date. I want the output data to add the number of days and give me a new column which shows the new_date but when I use the code, it add the number of days in time as I am using datetime format. Please see the sample dataset and suggest what can I do so that sas code adds the number of days and give the new date. If you observe the below data, the new_date constains the same date and dt_datdelay is added as time.

Data delayed_accounts;				
infile cards expandtabs;				
input debt_code	rep_code$	dt_delaydays	dt_datdelay:datetime22.3 new_date:datetime22.3;
format dt_datdelay datetime22.3 new_date:datetime22.3;
datalines ;	
106444375	133	16	21MAR2013:00:00:00.000	21MAR2013:00:00:16.000
345347595	168	2	23DEC2019:00:00:00.000	23DEC2019:00:00:02.000
323832873	168C	10	24FEB2020:00:00:00.000	24FEB2020:00:00:10.000
178669982	122	30	06SEP2017:00:00:00.000	06SEP2017:00:00:30.000
;				
run;

 

Proc sql;
create table delayed_accounts as
select *,
dt_delaydays + dt_datdelay as new_date format datetime22.3
from Accounts_in_trace;
quit;
1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

Write this down (in handwriting!) 100 times:

SAS dates are counts of days, SAS datetimes and times are cou nts of seconds!

 

This should let the message sink in.

 

In add a number of days to a datetime, you either add 86400 (the number of seconds in a day) * number of days, or you use the INTNX function, which gives you the additional advantage that you can align the result to specific timepoints within the given interval.

The interval you want to use is DTDAY (DAY is for dates).

proc sql;
create table delayed_accounts as
  select
    *,
    intnx("dtday",dt_datdelay,dt_delaydays,"s") as new_date format datetime22.3
  from accounts_in_trace
;
quit;

If you used "B" or "E" instead of the "S", you'd get the beginning or end of the day.

View solution in original post

2 REPLIES 2
Kurt_Bremser
Super User

Write this down (in handwriting!) 100 times:

SAS dates are counts of days, SAS datetimes and times are cou nts of seconds!

 

This should let the message sink in.

 

In add a number of days to a datetime, you either add 86400 (the number of seconds in a day) * number of days, or you use the INTNX function, which gives you the additional advantage that you can align the result to specific timepoints within the given interval.

The interval you want to use is DTDAY (DAY is for dates).

proc sql;
create table delayed_accounts as
  select
    *,
    intnx("dtday",dt_datdelay,dt_delaydays,"s") as new_date format datetime22.3
  from accounts_in_trace
;
quit;

If you used "B" or "E" instead of the "S", you'd get the beginning or end of the day.

Tom
Super User Tom
Super User

Dates are stored in days, but datetimes are stored in seconds.

 

You can use the INTNX() function to move date/time/datetime values by intervals that are not in the units used to store the values.  Datetime intervals have DT prefixed to their names.

create table delayed_accounts as
  select *
   , intnx('dtday',dt_datdelay,dt_delaydays,'same') as new_date format datetime22.3
  from Accounts_in_trace
;

If you really want to use addition then multiple days by 24 hours worth of seconds.

,dt_datdelay + dt_delaydays*'24:00:00't as new_date format datetime22.3

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 2 replies
  • 2594 views
  • 0 likes
  • 3 in conversation