DATA Step, Macro, Functions and more

Linking two tables with multiple rules

Accepted Solution Solved
Reply
Contributor
Posts: 26
Accepted Solution

Linking two tables with multiple rules

[ Edited ]

Hi - 

 

I have a table that has an start date and an end date and need to see if a call was made on the start date or the end date. this is my current query but it does not work. 

 

rsubmit;
proc sql;
create table test.field1 as
select
A.*,
B.*
from test.rock A
left join test.paper B
on A.account = B.account 
and A.start_date = B.call_date
or A.end_date = B.call_date
;
endrsubmit;

 

So ideally i want to get a table that shows me all the interactions I got that matched the start date and end date WITH the call_date(different table)

 

**the interactions are linked to Call_date which is a different table. 

 

This is the error i got...

 

NOTE: The execution of this query involves performing one or more Cartesian product joins that
can not be optimized.
ERROR: A lock is not available for test.field
ERROR: Lock held by process 5229800


Accepted Solutions
Solution
‎07-20-2016 09:35 AM
PROC Star
Posts: 1,760

Re: Linking two tables with multiple rules

There shouldnt be a cartesian product if you write the boolean clause the way you mean it:

on A.account = B.account 
and (A.start_date = B.call_date
    or A.end_date = B.call_date)

View solution in original post


All Replies
Solution
‎07-20-2016 09:35 AM
PROC Star
Posts: 1,760

Re: Linking two tables with multiple rules

There shouldnt be a cartesian product if you write the boolean clause the way you mean it:

on A.account = B.account 
and (A.start_date = B.call_date
    or A.end_date = B.call_date)

☑ This topic is solved.

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

Discussion stats
  • 1 reply
  • 186 views
  • 1 like
  • 2 in conversation