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 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

From SAS Users blog
Want more? Visit our blog for more articles like these.
5 Steps to Your First Analytics Project Using SAS

For SAS newbies, this video is a great way to get started. James Harroun walks through the process using SAS Studio for SAS OnDemand for Academics, but the same steps apply to any analytics project.

Find more tutorials on the SAS Users YouTube channel.

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