Help using Base SAS procedures

How to remove entry from being used 2x

Accepted Solution Solved
Reply
Regular Contributor
Posts: 240
Accepted Solution

How to remove entry from being used 2x

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



Accepted Solutions
Solution
‎01-27-2016 02:30 PM
Grand Advisor
Posts: 16,893

Re: How to remove entry from being used 2x

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

View solution in original post


All Replies
Regular Contributor
Posts: 240

Re: How to remove entry from being used 2x

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
Solution
‎01-27-2016 02:30 PM
Grand Advisor
Posts: 16,893

Re: How to remove entry from being used 2x

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

Re: How to remove entry from being used 2x

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
Regular Contributor
Posts: 240

Re: How to remove entry from being used 2x

Thanks Reeza
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 286 views
  • 0 likes
  • 2 in conversation