topic Re: How track same step on multiple system in SAS Procedures

How track same step on multiple system

BETO — Fri, 18 Apr 2014 04:34:18 GMT

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

Re: How track same step on multiple system

BETO — Fri, 18 Apr 2014 04:37:10 GMT

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

Re: How track same step on multiple system

Ksharp — Fri, 18 Apr 2014 10:07:14 GMT

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

Re: How track same step on multiple system

BETO — Fri, 18 Apr 2014 17:21:03 GMT

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

Re: How track same step on multiple system

Ksharp — Sat, 19 Apr 2014 02:08:33 GMT

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

Re: How track same step on multiple system

BETO — Sat, 19 Apr 2014 05:20:24 GMT

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

162396.

A836.

11/14/2013

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

A836.

11/14/2013

Re: How track same step on multiple system

Ksharp — Sat, 19 Apr 2014 10:01:46 GMT

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

Re: How track same step on multiple system

BETO — Sun, 20 Apr 2014 18:53:13 GMT

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

Re: How track same step on multiple system

Haikuo — Sun, 20 Apr 2014 21:15:22 GMT

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

Re: How track same step on multiple system

Ksharp — Mon, 21 Apr 2014 02:41:41 GMT

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

Re: How track same step on multiple system

BETO — Wed, 23 Apr 2014 22:17:26 GMT

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;

Re: How track same step on multiple system

BETO — Wed, 23 Apr 2014 22:20:17 GMT

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

Re: How track same step on multiple system

Ksharp — Thu, 24 Apr 2014 02:06:20 GMT

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

Re: How track same step on multiple system

BETO — Thu, 15 May 2014 18:30:05 GMT

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;

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;