topic Re: Difference between two datetime and add the difference to a variable. in SAS Programming

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

Santt0sh — Fri, 06 Apr 2018 19:53:37 GMT

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;

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

Reeza — Fri, 06 Apr 2018 19:52:55 GMT

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

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

Reeza — Fri, 06 Apr 2018 19:54:20 GMT

@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;  

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

Santt0sh — Fri, 06 Apr 2018 20:30:02 GMT

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.

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

Reeza — Fri, 06 Apr 2018 20:50:59 GMT

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

ie DTHOUR, not HOUR. Try that.

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

Santt0sh — Fri, 06 Apr 2018 21:10:32 GMT

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

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

ballardw — Fri, 06 Apr 2018 21:21:02 GMT

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.

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

Tom — Fri, 06 Apr 2018 22:09:45 GMT

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

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

Tom — Fri, 06 Apr 2018 22:15:45 GMT

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

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

Tom — Fri, 06 Apr 2018 22:27:46 GMT

@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

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

mkeintz — Sat, 07 Apr 2018 02:36:02 GMT

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.