Issue with calculating difference between two times in 24 hour format (time between two dates)

[imead]

I am trying to calculate the number of seconds that an event occurs in:

I keep running into an issue where when the date changes and I am trying to calculate the number of seconds between I get an inflated number. It is clearly just subtracting it backwards. My numbers are in SAS time not numeric, which should be easy, but neither INTCK or just subtracting solves this issue.

Code used to generate the last column (number of seconds) is below:

proc sql;
create table test as
select TimeDate,
	lagtime,
	timex,
	intck('second', lagtime, timex, 'continuous') as Duration
        /*(timex - lagtime) as Duration*/    /*This variation tried with same result*/
from specfaultl2_2
quit;

I appreciate any feedback, I'm very stumped! Thank you.

[ballardw · Accepted solution]

Please at least label your "data" so we have a chance of knowing what anything is.

Proc SQL is pretty poor for using any specific order, so without knowing what you did previously this SQL is likely not terribly helpful.

Is "row" 108 supposed to show the difference from row 107?

I have to guess that in a previous step creating your Lagtime and Timex values that you stripped off the date portion. Don't do it.

I suspect you should really provide data from before you create the Lagtime and Timex variables, and then the code used to create them.

Hint: if you want the difference to cross date boundaries you need to use complete datetime values, not just time, not one date and one time, 2 datetime values.

[imead]

Yes! You were completely correct. I was using timepart() to strip away the date information because it was confusing my validation of the results. When I just keep it as a SAS date and it works just fine. Thank you so much!

Also, thank you very much for taking the time to provide feedback on how I shared my question, I could have been much more clear, but was too bogged down with my problem, not thinking to share what was upstream. I will make an effort to provide much more clear context in the future.

[ballardw]

I'm glad my hint allowed you to solve your problem.

If you could, please post a bit of your final solution. This forum has a lot of people search for solutions and if they find this thread in a search it helps to have a more complete example.

[imead]

Here is my final solution paired down to remove identifiable information and just describe datetime. Hopefully this is clear enough! Thank you again, I was stumped.

/*Import SQL Table. There were other columns in select to require order by statement.
They were removed to simplify.*/
proc sql;
create table table1 as 
select TimeDate
from hist.SQLTable
order by TimeDate; 
quit;

/*Lag() is used to grab the previous row datetime.*/
data table2;
set table1;
lagtime = lag(timedate);
timex = timedate;
run;

/*Use INTCK to calculate number of seconds between lagtime and timex*/
proc sql;
create table table3 as
select *,
	intck('second', lagtime, timex, 'continuous') as Duration
from table2
order by TimeDate Desc;
quit;