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: 4,173

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

View solution in original post


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
Respected Advisor
Posts: 4,173

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;

Respected Advisor
Posts: 4,923

Re: Time difference calculation

Posted in reply to rohitguptaecb

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 and locked.

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

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