I have to merge 2 tables that fall within 0 to 2 min duration

[Beto16]

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;

[ballardw]

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]

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 · Accepted solution]

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]

Thanks for assistance im still in process of tweaking the code .. I will post better examples next time

[Beto16]

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]

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]

I created alias to the 1st to table A ... still not working what do you think I'm doing wrong? Thanks for help

[Beto16]

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