Calculating time duration from different observations

Accepted Solution Solved
Reply
Contributor
Posts: 20
Accepted Solution

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 ' '                                              
              end as Comments,                                      
           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: 11,343

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   ;
   length Comments $ 8;
   select (job);
      when ('FRED'   ) Comments= 'Start';
      when ('WILMA'  ) Comments= 'End'  ;   
      when ('BARNEY' ) Comments= 'D End';
      when ('BETTY'  ) Comments= 'E End';             
      when ('BAMBAM' ) Comments= 'Online';
      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;
      Comments='Duration';
      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.

 

View solution in original post


All Replies
Super User
Posts: 5,428

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: 11,343

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 - 
Job           	Time	Comments	Sequence
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: 11,343

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   ;
   length Comments $ 8;
   select (job);
      when ('FRED'   ) Comments= 'Start';
      when ('WILMA'  ) Comments= 'End'  ;   
      when ('BARNEY' ) Comments= 'D End';
      when ('BETTY'  ) Comments= 'E End';             
      when ('BAMBAM' ) Comments= 'Online';
      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;
      Comments='Duration';
      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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 373 views
  • 0 likes
  • 3 in conversation