BookmarkSubscribeRSS Feed
BETO
Fluorite | Level 6

I Was help with code by Patrick

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;


it works for what I need but the request has change in some cases  there will be additional machines in the location how do I account for when there are c.,d,e machines in some cases there might be up to 15 machines ...currently the reports puts each entry on its own row the ideal would to have it transpose so all site Id are group and every incident is on the same row ...I'm I open to any suggestion thanks

13 REPLIES 13
BETO
Fluorite | Level 6

HEre is original

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

Ksharp
Super User
data have;
input SIte_Id  $  Machine  $             Start_time   &  anydtdtm20.            End_time    & anydtdtm20.             dur_time ;
format      Start_time          End_time datetime.;
cards;
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
;
run;
data temp(keep=SIte_Id);
 set have;
 if SIte_Id eq lag(SIte_Id) and Start_time lt lag( End_time);
run;
proc sql;
create table want as
 select * from have where SIte_Id in (select SIte_Id from temp);
quit;

Xia Keshan

BETO
Fluorite | Level 6

Hi Ksharp,

Thank you for responding. What I'm looking forexample site is 162396 would appear on the same row instead of it appearing 2x as it is now thanks again

Ksharp
Super User

What do you mean "would appear on the same row " ? it is not what your data look like ?

BETO
Fluorite | Level 6

HI Ksharp,

this is the out put . What I'm needing is the data in 1 row not 2

SIte_Id

Machine

Start_time

End_time

dur_time

162396.

A837.

11/14/2013

11/14/2013

6

162396.

A836.

11/14/2013

11/14/2013

6


I Need the out put to look like  this

SIte_Id

Machine

Start_time

End_time

dur_time

Machine

Start_time

End_time

dur_time

162396.

A837.

11/14/2013

11/14/2013

6

A836.

11/14/2013

11/14/2013

6


Ksharp
Super User
data have;
input SIte_Id  $  Machine  $             Start_time   &  anydtdtm20.            End_time    & anydtdtm20.             dur_time ;
format      Start_time          End_time datetime.;
cards;
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
;
run;
data temp;
 set have;
 _Machine=lag(Machine);
_Start_time=lag(Start_time);
_End_time =lag(End_time );
_dur_time=lag(dur_time);
 if SIte_Id eq lag(SIte_Id) and Start_time lt lag( End_time) ;
 format  _Start_time _End_time datetime.;
run;

Xia Keshan

Message was edited by: xia keshan

BETO
Fluorite | Level 6

Ksharp,

The code works great when there are 2 machines like in the example .. how do I make it to include every machine for example in some cased there could be 10 all with same site ids? Thanks again for your assistance in this matter

Haikuo
Onyx | Level 15

Then rules need to be more specific, such as same overlapping has to be across all of the machines or just any of two? a little bit sample data (in/out) is always helpful.

Haikuo

Ksharp
Super User
data have;
input SIte_Id  $  Machine  $             Start_time   &  anydtdtm20.            End_time    & anydtdtm20.             dur_time ;
format      Start_time          End_time datetime.;
cards;
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
162396.    A835.          11/14/13 12:40:00   11/14/13 12:55:00           15
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
296871.   A075.            11/14/13 13:42:00   11/14/13 13:46:00        4
;
run;
data temp;
 set have;
 _Machine=lag(Machine);
_Start_time=lag(Start_time);
_End_time =lag(End_time );
_dur_time=lag(dur_time);
 if SIte_Id eq lag(SIte_Id) and Start_time lt lag( End_time) then do;
 output;
 Machine=_Machine;
 Start_time=_Start_time;
 End_time=_End_time;
 dur_time=_dur_time;
 output;
 end;
 drop _: ;
run;
proc sql;
 create table x as
  select distinct * from temp;
quit;
data x;
 set x;
 by SIte_Id;
 if first.SIte_Id then n=0;
 n+1;
run;
proc sql;
select distinct catt('x(where=(n=',put(n,best8. -l),') rename=(machine=machine',put(n,best8. -l),' start_time=start_time',put(n,best8. -l),' end_time=end_time',put(n,best8. -l),' dur_time=dur_time',put(n,best8. -l),'))') into : list separated by ' '
from x ;
quit;
data want;
 merge &list ;
 by SIte_Id;
 drop n;
run;

Xia Keshan

BETO
Fluorite | Level 6

HEre is your code with some minor tweaks

data Report5;

set report5;

_Machine=lag(ID);

_Start_time=lag(LOCAL_STARTDT);

_End_time =lag(LOCAL_ENDDT);

_dur_time=lag(Start_End_Dur_hr);

if SIte_Id eq lag(SIte_Id) and LOCAL_STARTDT lt lag( LOCAL_ENDDT) then do;

output;

id=_Machine;

LOCAL_STARTDT=_Start_time;

LOCAL_ENDDT=_End_time;

Start_End_Dur_hr=_dur_time;

output;

end;

drop _: ;

run;

proc sql;

create table x as

  select distinct * from report5;

quit;

data x;

set x;

by SIte_Id;

if first.SIte_Id then n=0;

n+1;

run;

proc sql;

select distinct catt('x(where=(n=',put(n,best8. -l),') rename=(ID=machine',put(n,best8. -l),' LOCAL_STARTDT=start_time',put(n,best8. -l),' LOCAL_ENDDT=end_time',put(n,best8. -l),' START_END_DUR_HR=dur_time',put(n,best8. -l),'))') into : list separated by ' '

from x ;

quit;

data want;

merge &list ;

by SIte_Id;

drop n;


BETO
Fluorite | Level 6

SITE_ID

Machine

LOCAL_STARTDT

LOCAL_ENDDT

TICKET_KEY

START_END_DUR_HR

HEre are the headers sorry couldn't copy it all at once... Thanks again. For all your help...I hope it make sense

Ksharp
Super User

Just replace these variables name if you could understand my code.

data have;
input SIte_Id  $  Machine  $             LOCAL_STARTDT   &  anydtdtm20.    LOCAL_ENDDT    & anydtdtm20.     TICKET_KEY        START_END_DUR_HR ;
format      LOCAL_STARTDT         LOCAL_ENDDT datetime.;
cards;
162396.    A837.          11/14/13 12:18:00    11/14/13 12:29:00      114146284     6
162396.    A836.          11/14/13 12:27:00   11/14/13 12:33:00    114146284       6
162396.    A835.          11/14/13 12:40:00   11/14/13 12:55:00      114146284     15
111501.    N21.             11/14/13. 11:42:00  11/14 /13 11:54:00   114146284     12
296871.   A077.             11/14/13 13:32:00  11/14/13 13:40:00    114146284     8
296871.   A076.            11/14/13 13:40:00   11/14/13 13:44:00    114146284    4
296871.   A075.            11/14/13 13:42:00   11/14/13 13:46:00   114146284     4
296871.   A074.            11/14/13 13:45:00   11/14/13 13:50:00   114146284     5
;
run;
data temp;
 set have;
 _Machine=lag(Machine);
_LOCAL_STARTDT=lag(LOCAL_STARTDT);
_End_time =lag(LOCAL_ENDDT );
_ticket=lag(TICKET_KEY);
_dur_time=lag(START_END_DUR_HR);
 if SIte_Id eq lag(SIte_Id) and LOCAL_STARTDT lt lag(LOCAL_ENDDT) then do;
 output;
 Machine=_Machine;
 LOCAL_STARTDT=_LOCAL_STARTDT;
LOCAL_ENDDT=_End_time;
TICKET_KEY=     _ticket;
START_END_DUR_HR=_dur_time;
 output;
 end;
 drop _: ;
run;
proc sql;
 create table x as
  select distinct * from temp;
quit;
data x;
 set x;
 by SIte_Id;
 if first.SIte_Id then n=0;
 n+1;
run;
proc sql;
select distinct catt('x(where=(n=',put(n,best8. -l),') rename=(machine=machine',put(n,best8. -l),' LOCAL_STARTDT=LOCAL_STARTDT',put(n,best8. -l),' LOCAL_ENDDT=LOCAL_ENDDT',put(n,best8. -l),' TICKET_KEY=TICKET_KEY',put(n,best8. -l),' START_END_DUR_HR=START_END_DUR_HR',put(n,best8. -l),'))') into : list separated by ' '
from x ;
quit;
data want;
 merge &list ;
 by SIte_Id;
 drop n;
run;

Xia Keshan

BETO
Fluorite | Level 6

HI Ksharp,

i have made some changes to script but I'm still getting more than 1 site Id any suggestions how I can have it all all site Id in 1 row ?

%macro r(x,n);&x. as &x.&n.%mend;

proc sql; create table want3 as select

a.site_id , a.%r(machine,1),a.%r(start_time,1),a.%r(end_time,1),a.%r(dur_time,1),

b.%r(machine,2),b.%r(start_time,2),b.%r(end_time,2),b.%r(dur_time,2)

from Report4 as a inner join report4 as b on a.site_id=b.site_id

where (a.end_time GT b.start_time

             and

             a.end_time LT b.end_time

            )

         or (a.end_time EQ b.end_time

             and

             INPUT(SUBSTR(a.machine,3,4),4.) LT INPUT(SUBSTR(b.machine,3,4),4.)

            );

quit;

     

proc sort data=Want3;by site_ID machine1 descending machine2;run;

proc sql noprint; create table tmp as select site_ID, machine1,count(*) as cnt

from want3 group by 1,2;

select max(cnt) into:mx from tmp;

quit;

data tmp(drop=i);

format mac  st et dh allvar $100.;

do i=2 to &mx.+1;

mac=compress("machine"||i);

st=compress("start_time"||i);

et=compress("end_time"||i);

dh=compress("dur_time"||i);

allvar=compress(mac)||" "||compress(st)||" "|| compress(et)||" "||compress(dh);

output;

end;

run;

proc sql noprint; select mac into:mac separated by ' ' from tmp ;

select st into:st separated by ' ' from tmp ;

select et into:et separated by ' ' from tmp ;

select dh into:dh separated by ' ' from tmp ;

select allvar into:allvar separated by ' ' from tmp ;

quit;

data report6(drop=i n mac st et dh) ;

retain site_id machine1 start_time1 end_time1 dur_time1 &allvar. n;

format &mac. $10. &st. &et. datetime.;

set want3(rename=(machine2=mac start_time2=st end_time2=et dur_time2=dh));

by site_ID machine1 ;

array m{*} &mac.;

array s{*} &st.;

array e{*} &et.;

array d{*} &dh.;

if first.site_ID or first.machine1  then

      do; n=0;

            do i=1 to &mx.;

            m(i)="";s(i)=.;e(i)=.;d(i)=.;

            end;

      end;

n+1;

m(n)=mac;

s(n)=st;

e(n)=et;

d(n)=dh;

if last.site_id or last.machine1 then output report6;

run;


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
  • 13 replies
  • 1113 views
  • 0 likes
  • 3 in conversation