topic Re: How to remove entry from being used 2x in SAS Procedures

How to remove entry from being used 2x

BETO — Fri, 22 Jan 2016 16:52:22 GMT

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

Re: How to remove entry from being used 2x

BETO — Fri, 22 Jan 2016 19:39:29 GMT

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

Re: How to remove entry from being used 2x

Reeza — Sat, 23 Jan 2016 03:52:21 GMT

Post an example that breaks your code. Also please post a working code sample. Your code above would result in syntax errors.

Re: How to remove entry from being used 2x

BETO — Sun, 24 Jan 2016 19:13:40 GMT

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

Re: How to remove entry from being used 2x

BETO — Wed, 27 Jan 2016 19:30:28 GMT

Thanks Reeza