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

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;


 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

View solution in original post

10 REPLIES 10
Reeza
Super User

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

Reeza
Super User

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


 

 

 


 

Santt0sh
Lapis Lazuli | Level 10

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.

Reeza
Super User

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

 

ie DTHOUR, not HOUR. Try that.

Tom
Super User Tom
Super User

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

Santt0sh
Lapis Lazuli | Level 10
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
ballardw
Super User

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.

 

Tom
Super User Tom
Super User
@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
mkeintz
PROC Star

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.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Tom
Super User Tom
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 13909 views
  • 4 likes
  • 5 in conversation