Solved
Contributor
Posts: 20

# Calculating time duration from different observations

I'm trying to calculate the duration between two times, that are reflected are separate observations/rows. I've been trying to use "INTCK('minute',Start,End)" for this, but having issues getting the Start and end times returned from the rows I'm after.

Here is the what I'm currently executing –

``````proc sql;
create table cmdsql as
select jobname as Job,
start,
end,
case
when Job = 'FRED' then start
else end
end as Time,
case
when Job = 'FRED'   then 'Start'
when Job = 'WILMA'  then 'End'
when Job = 'BARNEY' then 'D End'
when Job = 'BETTY'  then 'E End'
when Job = 'BAMBAM' then 'Online'
else ' '
case
when Job = 'FRED'   then '1'
when Job = 'WILMA'  then '2'
when Job = 'BARNEY' then '4'
when Job = 'BETTY'  then '5'
when Job = 'BAMBAM' then '6'
else ' '
end as Sequence
from cmds;

insert into cmdsql
values (" ", "00:00"t, "00:00"t, "00:00"t, "Duration", "3");

quit;                                 ``````

I'm looking to use the Time value when the Job = FRED(start), and the Time value when the Job = WILMA(end), and calculate the duration.

Ultimately this needs to get stored in the Duration row that’s being inserted.

I've been trying this within the existing sql I have, not sure if its better to do this within another SAS data step?

Appreciate any assistance.

Accepted Solutions
Solution
‎09-08-2016 09:19 AM
Super User
Posts: 13,498

## Re: Calculating time duration from different observations

Here's one way. I did some edit on the JOB text and comparisons to match spelling and case for your conditional assignment.

``````data have;
informat job \$8. start end time.;
format start end time.;
input Job   \$   	Start	End   ;
select (job);
when ('WILMA'  ) Comments= 'End'  ;
when ('BARNEY' ) Comments= 'D End';
when ('BETTY'  ) Comments= 'E End';
otherwise;
end;
select (Job);
when ('FRED'   ) Sequence= '1';
when ('WILMA'  ) Sequence= '2';
when ('BARNEY' ) Sequence= '4';
when ('BETTY'  ) Sequence= '5';
when ('BAMBAM' ) Sequence= '6';
otherwise;
end;
datalines;
FRED     23:28 23:35
WILMA    1:23  1:43
BARNEY   2:00  2:00
BETTY    2:12  2:13
BAMBAM   2:13  3:15
;
run;

data want;
length job \$ 8;
format time time5.;
set have;
Lstart=lag(start);
If sequence = '1' then time=Start;
Else time=End;

output;
if sequence='2' then do;
/* calc duration, assume start>end is date cross boundary*/
if Lstart> end then Time = end + intck('second',Lstart,'23:59:59't) +1 ;
Else Time = end-lstart;
Sequence='3';
call missing(Job);
output;
end;

drop start end lstart;
run;

``````

If I were reading your starting values from a text file I suspect that with use of RETAIN instead of Lag this could be done in the data step that reads the data.

All Replies
Super User
Posts: 5,876

## Re: Calculating time duration from different observations

Without knowing your existing data is hard to tell what to do. Attach a have and a want sample.
Data never sleeps
Super User
Posts: 13,498

## Re: Calculating time duration from different observations

If your dataset consists of more than 2 rows then Proc SQL may not be optimal as it tends to reorder data.

Contributor
Posts: 20

## Re: Calculating time duration from different observations

I've tried to outline what my input currently is, and what I've trying to achieve -

``````My input looks like this -

Job           	Start	End
Fred		23:28	23:35
Wilma		1:23    1:43
Barney 		2:00    2:00
Betty		2:12    2:13
BamBam	        2:13    3:15

I'm currently returning this –

Job           	Start	End		Time	Comments	Sequence
Fred		23:28	23:35		23:28	Start		1		 Use the Time from here
Wilma		1:23    1:43		1:43	End		2		 Use the Time from here
Barney 		2:00    2:00		2:00	D End		4
Betty		2:12    2:13		2:13	E End		5
BamBam	        2:13    3:15		3:15	Online		6
00:00	00:00		00:00	Duration	3

Ultimately I'd like to get to this -
Fred		23:28	Start		1
Wilma		1:43	End		2
2:15	Duration	3	 The duration from the times above is reflect here(135 minutes = 2 hrs 15 min)
Barney 		2:00	D End		4
Betty		2:13	E End		5
BamBam	        3:15	Online		6

``````

Thanks

Solution
‎09-08-2016 09:19 AM
Super User
Posts: 13,498

## Re: Calculating time duration from different observations

Here's one way. I did some edit on the JOB text and comparisons to match spelling and case for your conditional assignment.

``````data have;
informat job \$8. start end time.;
format start end time.;
input Job   \$   	Start	End   ;
select (job);
when ('WILMA'  ) Comments= 'End'  ;
when ('BARNEY' ) Comments= 'D End';
when ('BETTY'  ) Comments= 'E End';
otherwise;
end;
select (Job);
when ('FRED'   ) Sequence= '1';
when ('WILMA'  ) Sequence= '2';
when ('BARNEY' ) Sequence= '4';
when ('BETTY'  ) Sequence= '5';
when ('BAMBAM' ) Sequence= '6';
otherwise;
end;
datalines;
FRED     23:28 23:35
WILMA    1:23  1:43
BARNEY   2:00  2:00
BETTY    2:12  2:13
BAMBAM   2:13  3:15
;
run;

data want;
length job \$ 8;
format time time5.;
set have;
Lstart=lag(start);
If sequence = '1' then time=Start;
Else time=End;

output;
if sequence='2' then do;
/* calc duration, assume start>end is date cross boundary*/
if Lstart> end then Time = end + intck('second',Lstart,'23:59:59't) +1 ;
Else Time = end-lstart;
Sequence='3';
call missing(Job);
output;
end;

drop start end lstart;
run;

``````

If I were reading your starting values from a text file I suspect that with use of RETAIN instead of Lag this could be done in the data step that reads the data.

Contributor
Posts: 20

## Re: Calculating time duration from different observations

Great. Thanks for this.
☑ This topic is solved.