Hi after getting the min an max with your help I need to see if machine on the same id were down at the same time I have a macro that does that it looks like this
% macro r (×,n);&x. As &x.&n. &mend; proc sql;Create table want as select a.id ,a.date , a.%r (number, 1),a.%r (min_time,1),a.%r (max_time,1),a.%r (dur_time,1), B.id ,a.date , a.%r (number, 2),b.%r (min_time,3),b.%r (max_time,2),b.%r (dur_time,3) from fix as a inner join fix as b on a.Id=b.id where (a.max_time gt b.min_time) And a.max_time LT b.max_time ) Or ( a.max_time eq b.max_time And input (substr(a . number, 3,4)4.) LT input (substr( b.number,3,4.) ); Run;
What I get is if there are only 2 machines that overlap between there min an max time it works great ...but if there were 3 or 4 machined brought down it combined machine 1 with 2nd machine than in 2nd row it adds machine 1 to machine 3 I would like if machine 3 or 4 were all part of the first 2... I wonder if it's because I'm only refer a.b. I should c and d as well ? Thanks for help
a example of data output Id Number min_time1. max_time1. 1a. 123. 12/14/15 15:18. 12/14/15 15:25 number min_time2. max time2. 456. 12/14/15 15:18. 12/14/15 15:34 that's is perfect but when I have a 3rd or 4th machine I get this
Id Number min_time1. max_time1. 1a. 123. 12/14/15 15:18. 12/14/15 15:25 number min_time2. max time2. 300 12/14/15 15:02. 12/14/15 15:37
which duplicates 123 if 300 could be group with first example that be perfect
Hi reeza works when it compares one machine to another machine the issue is when there 3 machines it compares the 1st 2 than it inserts another row where it compares the 1st one again an the 3rd instead of adding the 3rd to the 1st two....I need it to look like this 1st. And 2nd. An 3rd if the dt. Overlaps ...not 1st. And 2nd 1st. And. 3rd ....
If I can add something that keeps the date time field one time and doesn't reuse it since it was used already ....hope that helps thanks for your assistance