BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Beto16
Obsidian | Level 7




I have table that shows when machine was rebooted which eq action code key xy and when it was service action code key gh col of table
id
key,
action key
start date dt
End date dt
time duration

What I need is to determine is when there was an action key xy was there a action code key gh.. thst falls within 0 to 2 min if yes create dataset if action key eq xy and no gh within 0 tO 2 min create data setb for it ..data looks like this


    

     ;
Id. Key. Action key start date dt. End date dt dur
A1 40 Gh 25 may 2016;00:01:00 25 may 2016;05:00. 4 min duration
A1 34. Xy 25 may 2016;00:02:00 25 may 2016;00:18:00. 17 min duration


I want a dataset where start from xy an gh falls within 2 min

Id. Key. Action key start date dt. End date dt dur
A1 40 Gh 25 may 2016;00:01:00 25 may 2016;05:00. 4 min duration
A1 34. Xy 25 may 2016;00:08:00 25 may 2016;00:18:00. 10 min duration

and a dataset of xy where gh didn't overlap 2 min

thank you again In the bottom I was tinkering with it but running circles lol



oc sql;

     create table overlap as

     select a.site_id, a.machine as a_machine, ....

     from log_tbl as A, log_tbl as B

     where a.site_id=b.site_id and a.start_dttm<b.start_dttm and a.end_dttm>b.start_dttm

     ;

quit;
1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

I think you need to provide some more complete example of actual match and not match. I can't tell within 2 minutes of what from your description. You have starttime and endtime on boths sides. Is it supposed to be within 2 minutes of either start or end from A or only within 2 minutes after start or 2 minutes before end or is it okay to be more than 2 minutes from either if the interval between start and end exceeds 4 minutes.

View solution in original post

8 REPLIES 8
ballardw
Super User

First thing: Are your datetime values actual SAS datetime valued or are they character values that just look like datetime?

 

 

I think you may want to change this:

from log_tbl as A to from (select * from log_tbl where action = 'Xy') as a

to get the Xy records and similarly

log_tbl as B

would be (select * from log_tbl where action='Gh') as b

 

You might try, for better reading: 

Instead of: and a.start_dttm<b.start_dttm and a.end_dttm>b.start_dttm

try

and b.start_dttm between a.start_dttm and a.end_dttm

Beto16
Obsidian | Level 7
It's a datetime sas format. .. where do I make refer on the 0-2 min duration which the service = Gh is within 0-2 mind from xy ? Thanks for assisting
ballardw
Super User

I think you need to provide some more complete example of actual match and not match. I can't tell within 2 minutes of what from your description. You have starttime and endtime on boths sides. Is it supposed to be within 2 minutes of either start or end from A or only within 2 minutes after start or 2 minutes before end or is it okay to be more than 2 minutes from either if the interval between start and end exceeds 4 minutes.

Beto16
Obsidian | Level 7
Thanks for assistance im still in process of tweaking the code .. I will post better examples next time
Beto16
Obsidian | Level 7
Ok I have this
Proc sql;
Create table overlapping as
(Select
Id,
action key,
start date dt,
End date dt
From (select*from table where action code key ='xy')as A

From (select*from table where action code key ='Gh')as b

and b.start_date between a.start_date and a.end_date
);
Run;

I get error on the asterisks
22_76 syntax error , expecting one of the following : ),', ' .... the duration time should be starttime of gh and start time of xy if it falls with 0 to 2 min that create data set if it's greater than 2 min that create differ dataset. Thanks
Beto16
Obsidian | Level 7
I think I fixed error but now I'm getting
ambiguous reference column action code key is more than one table
ambiguous reference column start date is more than one table
Beto16
Obsidian | Level 7
I created alias to the 1st to table A ... still not working what do you think I'm doing wrong? Thanks for help
Beto16
Obsidian | Level 7
Here is example of what I'm working with
Id. Key. Action key start date dt. End date dt dur
A1 40 Gh 25 may 2016;00:01:00 25 may 2016;05:00. 4 min duration
A1 34. Xy 25 may 2016;00:02:00 25 may 2016;00:18:00. 16 min duration
this example shows that first gh had within range of 2 min
I would that in a dataset as overlap if I have

Id. Key. Action key start date dt. End date dt dur
A1 40 Gh 25 may 2016;00:01:00 25 may 2016;05:00. 4 min duration
A1 34. Xy 25 may 2016;00:08:00 25 may 2016;00:18:00. 10 min duration
On this example the first gh an first xy start time is greater than 2 min so this example I would like a dataset named no overlap thank you again hope this helps

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

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
  • 8 replies
  • 1315 views
  • 0 likes
  • 2 in conversation