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;
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
Please post the code you've tried so far, the INTCK in particular that you say is not working.
@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;
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.
You have date time variables so your time intervals need DT to indicate datetime
ie DTHOUR, not HOUR. Try that.
TIME and DATETIME are both stored in seconds so HOURS are the same intervals.
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.
@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
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.
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 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.