DATA Step, Macro, Functions and more

Difference between two datetime and add the difference to a variable.

Reply
Occasional Contributor
Posts: 5

Difference between two datetime and add the difference to a variable.

[ Edited ]

Hi, 

I am trying to get the difference between two date time values. I have a table which has Product, Production Start time, Endtime

 

There are few products which do not have the endtime, we need to update the Endtime if the endtime is missing when the report is run, it should be updated to "currentdate23:59:59"dt
I have tried using Intck but im not sure why i am getting incorrect value for Intck, and beacuse of this i am not able add the difference hours from the start time to the "currentdate23:59:59"dt

Kindly Suggest.

Regards,
Santtosh...

 

 

Product='Choclates';
dtime1='01Jun2000:08:00:00'dt; dtime2='02Jun2000:23:00:00'dt;


 

 

 

Super User
Posts: 22,857

Re: Difference between two datetime and add the difference to a variable.

Please post the code you've tried so far, the INTCK in particular that you say is not working.

Super User
Posts: 22,857

Re: Difference between two datetime and add the difference to a variable.

@Santt0sh I tried to clean up your question and I think it better reflects what you're asking, but please review it to make sure it's still what you're trying say. 

 


@Santt0sh wrote:

Hi, 

I am trying to get the difference between two date time values. I have a table which has Product, Production Start time, Endtime

 

There are few products which do not have the endtime, we need to update the Endtime if the endtime is missing when the report is run, it should be updated to "currentdate23:59:59"dt
I have tried using Intck but im not sure why i am getting incorrect value for Intck, and beacuse of this i am not able add the difference hours from the start time to the "currentdate23:59:59"dt

Kindly Suggest.

Regards,
Santtosh...

 

 

Product='Choclates';
dtime1='01Jun2000:08:00:00'dt; dtime2='02Jun2000:23:00:00'dt;


 

 

 


 

Occasional Contributor
Posts: 5

Re: Difference between two datetime and add the difference to a variable.

Hi Reeza,

 

Please find the below code i have tried to calculate the difference in two ways.

 

Starttime="05Apr2018:02:41:35"dt;

Endtime="05Apr2018:23:59:59"dt;

Int=Intck('hour',Endtime,Starttime); option 1

T=Round(endtime-starttime)/3600); option 2

Tx=Intnx('hour,Starttime,T);

 

 

 

OUTPUT

format TX datetime23.;

Tx=05APR2018:23:00:00

T=15

 

 

Kindly Suggest.

Super User
Posts: 22,857

Re: Difference between two datetime and add the difference to a variable.

You have date time variables so your time intervals need DT to indicate datetime

 

ie DTHOUR, not HOUR. Try that.

Super User
Super User
Posts: 7,860

Re: Difference between two datetime and add the difference to a variable.

TIME and DATETIME are both stored in seconds so HOURS are the same intervals.

Occasional Contributor
Posts: 5

Re: Difference between two datetime and add the difference to a variable.

Hi Reeza,

Thank you for your suggestions,
I have tried applying DTHOUR instead of HOUR for both INTCK and INTNX, unfortnately i am not able to get what i want to do.

The resullts are unchanged.
Starttime="05Apr2018:02:41:35"dt;

Endtime="05Apr2018:23:59:59"dt;

Int=Intck('DThour',Endtime,Starttime); option 1

T=Round(endtime-starttime)/3600); option 2

Tx=Intnx('DThour',Starttime,T);






OUTPUT

format TX datetime23.;

Tx=05APR2018:23:00:00

T=15
Super User
Posts: 13,066

Re: Difference between two datetime and add the difference to a variable.

Is this what you are looking for?

 

data work.junk;
   Starttime="05Apr2018:02:41:35"dt;
   endtime = intnx('dtday',starttime,0,'E');
   format Starttime endtime datetime20.;
   
run;

this advances the datetime value to the end of the day for that date.

 

Super User
Super User
Posts: 7,860

Re: Difference between two datetime and add the difference to a variable.

@Santt0sh wrote:
Hi Reeza,
Thank you for your suggestions,
I have tried applying DTHOUR instead of HOUR for both INTCK and INTNX, unfortnately i am not able to get what i want to do.
The resullts are unchanged.
Starttime="05Apr2018:02:41:35"dt;
Endtime="05Apr2018:23:59:59"dt;
Int=Intck('DThour',Endtime,Starttime); option 1
T=Round(endtime-starttime)/3600); option 2
Tx=Intnx('DThour',Starttime,T);
OUTPUT
format TX datetime23.;
Tx=05APR2018:23:00:00
T=15

 What are you trying to count.  The INTCK() function is for calculating number of boundaries crossed. The INTNX() function calculates a new date/time/datetime value.

The difference between two datetime values will be in seconds. You could attach a TIME format to it if you want.

To convert to hours then divide by the number of seconds in an hour.  Perhaps you want to round the number of hours?

data test;
Starttime="05Apr2018:02:41:35"dt;
Endtime="05Apr2018:23:59:59"dt;
format _all_ datetime20. ;
seconds = endtime-starttime ;
duration = seconds ;
hours = round(seconds/'01:00't,1);
format seconds comma12. duration hhmm12. hours comma5. ;
put (_all_) (=/);
run;
Starttime=05APR2018:02:41:35
Endtime=05APR2018:23:59:59
seconds=76,704
duration=21:18
hours=21
Trusted Advisor
Posts: 1,288

Re: Difference between two datetime and add the difference to a variable.

You apparently expected TX to be the same as starttime.  But because you used the INTNX function, with DTHOUR as the unit to add/subtract, the function has to have a rule for assigning which part of the dthour the resulting datetime value will get.  The default behavior is the beginning of the resulting DTHOUR, i.e. you got:

 Tx=05APR2018:23:00:00

 

 

But if you want the result to have the same relative location within the DTHOUR, you should populate the 4th argument of INTNX with the keyword "SAME", as in:

Tx=Intnx('DThour',Starttime,T,'same');

which yields:

 

tx=05APR2018:23:41:35

 

 

Instead of "SAME", you could have "BEGIN" (the default) or "END" (which would yield 05APR2018:23:59:59),  or you could use "S","B", or "E".

 

For the INTCK function, there is also a pair of arguments to deal with the analogous problem of specifying a user-desired alignment of the DTHOUR boundaries.  They are 'DISCRETE' (the default) and 'CONTINUOUS' (or "D" and "C").  If you use "C", then the DTHOUR boundary is not the normal boundary (i.e. on the hour), but rather the boundary is determined by the 2nd argument.  I.e. for INTCK('dthour',endtime,starttime,'C') the boundary crossings to count is on the 59th second of the 59th minute of each hour, because ENDTIME="05Apr2018:23:59:59"dt.

Super User
Super User
Posts: 7,860

Re: Difference between two datetime and add the difference to a variable.

What is CURRENTDATE?  Do you mean the date when the code runs?

data have ;
  Product='Chocolates';
  dtime1='01Jun2000:08:00:00'dt;
  dtime2=. ;
  format dtime: datetime20. ;
run;  

How about this:

data want ;
  set have ;
  if missing(dtime2) then dtime2=intnx('dtday',datetime(),0,'e');
run;

Result:

Obs     Product                    dtime1                  dtime2
 1     Chocolates      01JUN2000:08:00:00      06APR2018:23:59:59
Ask a Question
Discussion stats
  • 10 replies
  • 133 views
  • 4 likes
  • 5 in conversation