Solved
Contributor
Posts: 27

# Time difference calculation

Dear friends,

i have n issue. i have a data set a like below

DATA A;

INPUT JOBID  DATES DATETIME18. MONTH \$;

DATALINES;

1 01MAR2013:12:23:12 march

1 01MAR2013:12:40:23 march

2 10MAR2013:10:12:34 march

2 10MAR2013:10:21:31 march

3 02APR2013:12:23:12 april

3 02APR2013:12:55:11 april

4 12APR2013:06:34:12 april

4 12APR2013:06:40:11 april

;

RUN;

for each job id the 1st obs gives the start time of the job and the 2nd obs gives end time.

Now using sql query, i want to fetch that job id and the time of execution which is taking maximum time FOR EACH MONTH.. hw cn i do that in proc sql ??? plz help

Accepted Solutions
Solution
‎03-16-2013 09:14 AM
Posts: 4,736

## Re: Time difference calculation

Posted in reply to rohitguptaecb

That must be study - so more important than a solution is all the learning while trying to get there.

Below the self-join bit:

select
...

r.dates-l.dates as Duration format=time10.

from have l , have r

where

l.jobid=r.jobid and

l.dates<r.dates

...

You will also need a Group By / Having clause to then select the row with the maximum duration per month

All Replies
Contributor
Posts: 27

## Re: Time difference calculation

Posted in reply to rohitguptaecb

Some one told me this could be done using self join. idont know how to do self join .. plz help me with the code

Solution
‎03-16-2013 09:14 AM
Posts: 4,736

## Re: Time difference calculation

Posted in reply to rohitguptaecb

That must be study - so more important than a solution is all the learning while trying to get there.

Below the self-join bit:

select
...

r.dates-l.dates as Duration format=time10.

from have l , have r

where

l.jobid=r.jobid and

l.dates<r.dates

...

You will also need a Group By / Having clause to then select the row with the maximum duration per month

Contributor
Posts: 27

## Re: Time difference calculation

Thanks Patrick, i got my answer.....

Occasional Contributor
Posts: 12

## Re: Time difference calculation

Posted in reply to rohitguptaecb

proc sql;

select jobid,time

from (select *

from (select a.jobid,a.month,a.dates-b.dates as time

from (select *,monotonic() as m from a) a,

(select *,monotonic() as n from a)b

where n=(m-1) and a.month=b.month))

group by month

having time=max(time)

order by jobid;

quit;

Posts: 5,521

## Re: Time difference calculation

Posted in reply to rohitguptaecb

I don't quite see the need for a self join here (apart from academics ). You can translate your problem statement almost directly into SQL :

proc sql;

create table bigJobs as

select month, jobId, jobHours

from

(   select month, jobId, range(dates)/3600 as jobHours

from a group by month, jobId  )

group by month

having jobHours=max(jobHours);

select * from bigJobs;

quit;

PG

PG
🔒 This topic is solved and locked.

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

Discussion stats
• 5 replies
• 332 views
• 4 likes
• 4 in conversation