Calculate time between rows

[jerry898969]

Tom,

Thank you for your help.  I'm working with my date and time in seperate fields based on a request from my lead. 

Is it just adding 12 hours to PM and leaving AM alone?

Thank you again

[Tom · Accepted solution]

Both datetime and time values are stored as the number of seconds. So the trick of adding '12:00't to add 12 hours will work the same for a time variable as it does for a datetime variable. If time='12:00't and ampm='PM' then do NOT add the 12 hours.

if time < '12:00't and ampm='PM' then time+'12:00't ;

It is easier to calculate differences that might cross midnight if you convert your date and time variables into a datetime value.  To calculate the difference from SDATE/STIME to EDATE/ETIME you could use this formula.

diff = dhms(edate,0,0,etime) - dhms(sdate,0,0,stime) ;

[Ksharp]

Tom,

I found '2:00 pm't '2:00 am't also are time constant just like ''2:00 't '14:00 't .

data _null_;

time='2:00 pm't;

put time time.;

run;

Ksharp

[jerry898969]

Tom,

Thank you so much.  That extra check if time < '12:00't corrected an issue I was running into was the time in seconds was greater then 86400.

I will eventually be using the date and time together to get the difference but for now my lead just wants me to deal with time.

I have my rows in the order they appear in the log file. 

sec tab var date time

1 1 1 01/01/2010 09:05:26 AM

1 1 1 01/01/2010 09:05:46 AM

1 1 2 01/01/2010 09:05:46 AM

1 1 2 01/01/2010 09:05:48 AM

1 1 1 01/01/2010 09:06:01 AM

1 1 1 01/01/2010 09:06:09 AM

If I use the diff function it will do a minus from a current row from the previous row.  Without sorting my data since I need the times to be in the correct order how can I get it to be.

sec tab var diff

1 1 1 00:00:20

1 1 2 00:00:02

1 1 1 00:00:08

After this point I can go a sum in a proc sql and sum up total time spent on each combination

sec tab var diff

1 1 1 00:00:28

1 1 2 00:00:02

Thank you again to everyone for your help