BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
BETO
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

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

8 REPLIES 8
Quentin
Super User

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.

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
BETO
Fluorite | Level 6

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

Patrick
Opal | Level 21

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;

BETO
Fluorite | Level 6

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 ?

LinusH
Tourmaline | Level 20

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
Patrick
Opal | Level 21

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).

BETO
Fluorite | Level 6

Thanks  Patrick

Patrick
Opal | Level 21

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?

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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