Help using Base SAS procedures

Two Steps done at Same time

Accepted Solution Solved
Reply
Regular Contributor
Posts: 240
Accepted Solution

Two Steps done at Same time

hi Ive and issue on how to determine if a tech brought down both machine at same time. here is what I have now

machine1 start date 11/01/13 start time 14:00:00  end date 11/01/13 end time 14:06:00      duration  6

machine2 Start date 11/01/13 Start time 14:04:00 end date 11/01/13   end time 14:10:00 duration 6

as you see the machine 1 was still down when machine 2 was brought down  if machine 2 was brought down after 14:06:00 than thats ok .im looking for is if down time overlaps each other... thanks in advances


Accepted Solutions
Solution
‎11-16-2013 10:36 PM
Respected Advisor
Posts: 3,887

Re: Two Steps done at Same time

proc sql;

     create table overlap as

     select a.site_id, a.machine as a_machine, ....

     from log_tbl as A, log_tbl as B

     where a.site_id=b.site_id and a.start_dttm<b.start_dttm and a.end_dttm>b.start_dttm

     ;

quit;

View solution in original post


All Replies
PROC Star
Posts: 1,230

Re: Two Steps done at Same time

Hi,

Can you show about 10  records of what your data look like?

Is it something like:

Machine     StartDate     EndDate

1     1/1/2000 12:00:00     1/5/2000 18:00:00

2     1/6/2000 12:30:00     1/7/2000 04:04:00

...

About how many records do you have?

What's the goal:

Find out if both machines were ever down at the same time?

Find out when both machines were down?

Regards,

--Q.

Regular Contributor
Posts: 240

Re: Two Steps done at Same time

HI Quentin,

SIte Id    Machine.               Start time.               End time              dur time

162396.    A837.          11/14/13 12:18:00    11/14/13 12:29:00           6

162396.    A836.          11/14/13 12:27:00   11/14/13  12:33:00           6

111501.    N21.             11/14/13. 11:42:00  11/14 /13 11:54:00        12

296871.   A077.             11/14/13 13:32:00  11/14/13 13:40:00         8

296871.   A076.            11/14/13 13:40:00   11/14/13 13:44:00        4

i forgot to mention that we ID the site where machines are located so for example. 162396 we gave 2 machines at the location  I will be dealing on average 700 entries daily.

What's the goal:

Find out if both machines were ever down at the same time?

Find out when both machines were down?

The goal is to determine if the machines were down at same time.....FYI Start time  is when the machine went down

FOr example 162396 is what I would be looking for  the 2 machines at the same site Id  the down time overlap.... And 296871 would be one that  would be excluded because end time of one machine and start time of the 2nd machine is the same... Thanks agaiN I hope this helps

Solution
‎11-16-2013 10:36 PM
Respected Advisor
Posts: 3,887

Re: Two Steps done at Same time

proc sql;

     create table overlap as

     select a.site_id, a.machine as a_machine, ....

     from log_tbl as A, log_tbl as B

     where a.site_id=b.site_id and a.start_dttm<b.start_dttm and a.end_dttm>b.start_dttm

     ;

quit;

Regular Contributor
Posts: 240

Re: Two Steps done at Same time

HI Patrick,

i have a couple of questions what does"..." The 3 dots make refer to after machine?

and there is a comma right after the word machine should I add something else? Or not include the comma?  I'm getting and error  file work.log_tbl does not exist   Thanks for the help  FYI the name of the table that I'm using is  Report  the columns where the date time field is called start time and end time should I just make refer to that with the code ?

Super User
Posts: 5,255

Re: Two Steps done at Same time

He probably means that you can add more columns specification if you wish. If not, just ignore it AND the preceding comma.

Data never sleeps
Respected Advisor
Posts: 3,887

Re: Two Steps done at Same time

Hi Beto

It's exactly as Linus said. And yes, of course, you need to replace table and column names with the ones in your data. That's why it's really worth to provide sample data (a data step creating a SAS work table) when asking for code as this will allow us to use your table and variable names as well as actually test the code we post.

Please note that in the code I've posted columns like "start_dttm" are supposed to contain SAS datetime values (so date and time from source combined).

Regular Contributor
Posts: 240

Re: Two Steps done at Same time

Thanks  Patrick

Respected Advisor
Posts: 3,887

Re: Two Steps done at Same time

I agree with Quentin. You need to provide more test cases and then tell us how the result should look like.

Eg. what about a case like:

machine1 1/1/2000 12:00:00     1/5/2000 12:10:00

machine2 1/1/2000 12:05:00     1/5/2000 12:15:00

machine3 1/1/2000 12:12:00     1/5/2000 12:20:00

machine1 1/1/2000 12:19:00     1/5/2000 12:25:00

How should the report you're after look like?

☑ This topic is SOLVED.

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

Discussion stats
  • 8 replies
  • 301 views
  • 0 likes
  • 4 in conversation