Search code examples
sqlsasproc-sql

SAS: optimize inner join with multiple conditions

I am working on a query that goes something like this

    PROC SQL;
        CREATE TABLE EXAMPLE AS 
        SELECT A.* FROM TABLE1 AS A 
        INNER JOIN TABLE2 AS B 
        ON (A.ACCOUNT = B.ACCOUNT OR A.CARDNO = B.CARDNO)
    QUIT;

However this is very slow. I get a warning saying that it is performing multiple cartesian products that cannot be optimized. So i would like to know if there is a better way to perform this operation. Maybe using a data step or rewriting the query in a different way.

Thanks.

Solution

  • This is because you are using OR when applying the join.

    Try this:

    PROC SQL;
    CREATE TABLE EXAMPLE AS 
    SELECT A.* 
    FROM TABLE1 AS A 
    INNER JOIN TABLE2 AS B 
    ON A.ACCOUNT = B.ACCOUNT

    UNION

    SELECT A.* 
    FROM TABLE1 AS A 
    INNER JOIN TABLE2 AS B 
    ON A.CARDNO = B.CARDNO;
QUIT;