topic Re: I have to merge 2 tables that fall within 0 to 2 min duration in SAS Procedures

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

Beto16 — Thu, 26 May 2016 21:07:00 GMT

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;

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

ballardw — Thu, 26 May 2016 21:38:53 GMT

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

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

Beto16 — Thu, 26 May 2016 22:17:36 GMT

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

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

ballardw — Thu, 26 May 2016 22:43:40 GMT

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.

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

Beto16 — Thu, 26 May 2016 23:11:02 GMT

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

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

Beto16 — Thu, 26 May 2016 23:18:32 GMT

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

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

Beto16 — Thu, 26 May 2016 23:47:21 GMT

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

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

Beto16 — Fri, 27 May 2016 02:05:45 GMT

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

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

Beto16 — Fri, 27 May 2016 15:28:37 GMT

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