Help using Base SAS procedures

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

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 80
Accepted Solution

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





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;

Accepted Solutions
Solution
‎05-27-2016 11:26 AM
Super User
Posts: 11,343

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

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


All Replies
Super User
Posts: 11,343

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

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

Frequent Contributor
Posts: 80

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

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
Solution
‎05-27-2016 11:26 AM
Super User
Posts: 11,343

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

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.

Frequent Contributor
Posts: 80

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

Thanks for assistance im still in process of tweaking the code .. I will post better examples next time
Frequent Contributor
Posts: 80

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

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
Frequent Contributor
Posts: 80

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

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
Frequent Contributor
Posts: 80

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

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

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

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
☑ This topic is solved.

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

Discussion stats
  • 8 replies
  • 333 views
  • 0 likes
  • 2 in conversation