Time difference calculation

Accepted Solution Solved
Reply
Contributor
Posts: 27
Accepted Solution

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
Respected Advisor
Posts: 3,890

Re: Time difference calculation

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

View solution in original post


All Replies
Contributor
Posts: 27

Re: Time difference calculation

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
Respected Advisor
Posts: 3,890

Re: Time difference calculation

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

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;

Respected Advisor
Posts: 4,646

Re: Time difference calculation

I don't quite see the need for a self join here (apart from academics Smiley Happy). 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.

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

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