BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
BETO
Fluorite | Level 6
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


1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User
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

4 REPLIES 4
BETO
Fluorite | Level 6
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
Reeza
Super User
Post an example that breaks your code. Also please post a working code sample. Your code above would result in syntax errors.
BETO
Fluorite | Level 6
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
BETO
Fluorite | Level 6
Thanks Reeza

sas-innovate-2024.png

 

Secure your spot at the must-attend AI and analytics event of 2024: SAS Innovate 2024! Get ready for a jam-packed agenda featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events.

 

Register by March 1 to snag the Early Bird rate of just $695! Don't miss out on this exclusive offer. 

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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