Subtracting time

[mlogan]

I have to following Excel data with start and end time.

Time task opened	Time task completed
07:31:00	07:46:00
07:48:00	07:52:00
07:55:00	07:57:00
08:30:00	08:33:00
10:45:00	10:52:00
21:15:00	21:28:00
19:54:00	19:57:00
09:45:00	09:48:00
10:52:00	10:56:00
11:47:00	11:52:00

Calculating the time difference don't return any value in the first observation, can anyone tell me why? Here is the code I wrote.

Data Mylib.time_Final;

Total_Sec =Time_task_completed-Time_task_opened;

set Mylib.time;

run;

Total_Sec	Time_task_opened	Time_task_completed
	7:31	7:46
900	7:48	7:52
240	7:55	7:57
120	8:30	8:33
180	10:45	10:52
420	21:15	21:28
780	19:54	19:57
180	9:45	9:48
180	10:52	10:56
240	11:47	11:52

Also how do I make additional column (let's say Total_time) where Total_Sec will show as minute (eg: 00:04:05)

Thanks

[M_Maldonado]

google about intck and intcx functions

hih!

-Miguel

[mlogan]

hi Miguel,

I tried INTCK before with the following code, but it doesn't work for me (I get the same output). Do you see in my output how Total_Sec is quite incorrect. Would you be able to answer why this happen if you know of.

Data Mylib.time_Final;

Diff = INTCK('second',Time_task_opened,Time_task_completed);

set Mylib.time;

run;

Thanks

[Steelers_In_DC]

You lines are out of order.  Your set statement has to come before you reference any variables.  It should be like this:

data want;

set have;

Total_Sec=completed-opened;

run;

[Steelers_In_DC · Accepted solution]

I have the time so I did it but I agree with Miquel, you should definitely look up intck and intnx:

DATA have;

infile cards dsd;

informat opened completed time.;

format opened completed time.;

input opened completed;

cards;

07:31:00,07:46:00

07:48:00,07:52:00

07:55:00,07:57:00

08:30:00,08:33:00

10:45:00,10:52:00

21:15:00,21:28:00

19:54:00,19:57:00

09:45:00,09:48:00

10:52:00,10:56:00

11:47:00,11:52:00

;

run;

data want;

set have;

total_sec = intck('second',opened,completed);

run;

Although when I run your code I get the same answer.  The reason you want to use intck and intnx is if you are changing the 'second' to minute day or month.

This works for me:

data want;

set have;

Total_Sec=completed-opened;

run;

If it doesn't work for you you might want to check the data quality of that first line.

[mlogan]

Thanks Mark for taking your time helping me. I did a silly mistake, put the calculation function before the set command:)

It works now...thanks