SAS Pass-through SQL - Multiple DBs

Question

I want to retrieve from DB2 the list of records that matches the identifiers in a DB1 table, like a regular SAS subquery. How can I perform that with SAS pass-through SQL?

Performing the (long and complex) SQL on db1 is too slow using a regular SAS SQL, that's why I am resorting to pass-through SQL instead.

I tried the following but no luck:

proc sql;
    connect to db1 as A (user=&userid. password=&userpw.  database=MY_DB);
    connect to db2 as B (user=&userid. password=&userpw.  database=MY_DB);

    create table test as
    select * from connection to B (
        select * from schema.table
            Where ID_NUM =
                (select * from connection to A
                      (select ID_NUM from schema2.table2)
                );
        );
   disconnect from A;
   disconnect from B;
quit;

Answer 1

If you're connecting to single DB2 instance and joining two tables in different schemas/databases, the following should work for you:

proc sql;
    connect to db2 as B (user=&userid. password=&userpw.  database=MY_DB);

    create table test as
    select * from connection to B (
            /* here we're in DB2 SQL */
        select t1.* from schema.table as t1
                inner join schema2.table2 as t2
            on t1.ID_NUM = t2.ID_NUM
        );
   /* automatic disconnect at PROC SQL boundary */
quit;

If you talk to two different servers/two user accounts a heterogenous join without pass-through could be used. Then the expected number of ID_NUM values would be important.

Answer 2

You can't perform a pass-through query to another pass-through query, unless your two databases are naturally connected in some way that you could take advantage of in the native system.

The only way to do something like this would be to perform the connection to A query and store that result in a macro variable (the list of ID_NUMs), and then insert that macro variable into the query for connection to B.

It might well be better to not explicitly use passthrough here, but instead to use libname and execute the query as you would normally. SAS may well help you out here and do the work for you without actually copying all of B's rows in first.