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