Adding the number of days to a date

[Sandeep77]

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;

[Kurt_Bremser · Accepted solution]

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]

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