DATA Step, Macro, Functions and more

Time difference in defined work hours

Reply
Regular Contributor
Regular Contributor
Posts: 166

Time difference in defined work hours

Guys,
I have a dataset in the below format which has Intime, Outime, the Begin time of a working day and the End time of working day.
I have to calculate the difference between intime and outtime in hours (Time_taken) but the difference has to be on the basis of the BOD and EOD.
Ex: In the second case you can see that time taken is 10 hours in the first day and 8 hours in the second day. hence total time taken is 18 hours.
If somebody has worked on something like this then kindly help.


data test;
infile datalines delimiter = ',';
input INTIME : datetime19. OUTTIME : datetime19. BOD : $5. EOD : $5. Time_taken : $15.;
format intime : datetime19. OUTTIME : datetime19.;
cards;
01OCT2009:08:00:00, 01OCT2009:18:00:00, 08:00, 18:00, 10 (+10)
01OCT2009:08:00:00, 02OCT2009:16:00:00, 08:00, 18:00, 18 (10 +8)
01OCT2009:08:00:00, 02OCT2009:16:00:00, 08:00, 20:00, 20 (12 + 8)
01OCT2009:10:00:00, 03OCT2009:09:00:00, 08:00, 20:00, 23 (10+12+1)
;
run;
N/A
Posts: 0

Re: Time difference in defined work hours

Hi,

I send you one possible solution to your question.
1) I have changed BOD and EOD format to TIME5.
2) The calculation is do using DO sentence by days

Best regards
Jaroslav

data test;
infile datalines delimiter = ',';
input INTIME : datetime19. OUTTIME : datetime19. BOD : TIME5. EOD : TIME5. Time_taken : $15.;
format intime OUTTIME datetime19. BOD EOD TIME5.;
cards;
01OCT2009:08:00:00, 01OCT2009:18:00:00, 08:00, 18:00, 10 (+10)
01OCT2009:08:00:00, 02OCT2009:16:00:00, 08:00, 18:00, 18 (10 +8)
01OCT2009:08:00:00, 02OCT2009:16:00:00, 08:00, 20:00, 20 (12 + 8)
01OCT2009:10:00:00, 03OCT2009:09:00:00, 08:00, 20:00, 23 (10+12+1)
;
run;

DATA TEST(DROP=DAY);
FORMAT DIFERENCE TIME5.;
RETAIN DIFERENCE;
SET TEST;
DO DAY=DATEPART(INTIME) TO DATEPART(OUTTIME);
IF DAY=DATEPART(INTIME) THEN DIFERENCE=0;
DIFERENCE=DIFERENCE+MIN(OUTTIME,DHMS(DAY,0,0,EOD))-MAX(INTIME,DHMS(DAY,0,0,BOD));
IF DAY=DATEPART(OUTTIME) THEN OUTPUT;
END;
RUN;
Regular Contributor
Regular Contributor
Posts: 166

Re: Time difference in defined work hours

Jaroslav Thanks a lot This works just fine
Ask a Question
Discussion stats
  • 2 replies
  • 160 views
  • 0 likes
  • 2 in conversation