Merging a SAS table with a SQL passthrough

Question

I'm trying to merge a SAS table in a SQL passthrough to help cut down the time it takes to query the SQL database. At the moment I'm just using the passthrough as it is and it takes roughly 8-9 hours to pull in everything from the table before I then select what I want afterwards.

At the moment the passthrough is looking like this:

proc sql;
    connect to ODBC as CAW(datasrc = "CAW_ULI_STATIC");
    create table test  as 
        select aelref, aelprdtyp, aelsubtyp, aelloc, aelopndte,
         hdscontrolopendate, hdscontrolclosedate, hdscontrolaction,
        from connection to CAW (
                                select aelref, aelprdtyp, aelsubtyp, aelloc, aelextnbr, aelbrnpfx, aelitnnbr, aelopndte,
                                         aelclddte, hdscontrolopendate, hdscontrolclosedate, hdscontrolaction
                                from PUBLIC_withpersonal_short.Vwhdscisagrmnt (nolock)

                                where HDSControlACTION <> 'D' 
                                    and aelsubtyp in (1, 2, 3, 4, 5, 10, 20, 21)
                                order by aelref, hdscontrolopendate, hdscontrolclosedate
                                ); 
disconnect from CAW;
; 
quit;

But I'm now trying to use another SAS dataset to narrow down what I'm pulling in from the passthrough by doing a left join so it looks like this:

    proc sql;
        connect to ODBC as CAW(datasrc = "CAW_ULI_STATIC");
        create table test  as 
            select a.*, aelref, aelprdtyp, aelsubtyp, aelloc, aelopndte,
             hdscontrolopendate, hdscontrolclosedate, hdscontrolaction,
            from Import1 a left join connection to CAW (
                                    select aelref, aelprdtyp, aelsubtyp, aelloc, aelextnbr, aelbrnpfx, aelitnnbr, aelopndte,
                                             aelclddte, hdscontrolopendate, hdscontrolclosedate, hdscontrolaction
                                    from PUBLIC_withpersonal_short.Vwhdscisagrmnt (nolock)

                                    where HDSControlACTION <> 'D' 
                                        and aelsubtyp in (1, 2, 3, 4, 5, 10, 20, 21)
                                    order by aelref, hdscontrolopendate, hdscontrolclosedate
                                    ); 
    disconnect from CAW b;
    on a.ANUM = b.aelextnbr
    ; 
    quit;

But it doesn't seem to like adding in a join before the connection. Is this the right way to go about it, or am I missing something?

Thanks.

Answer 1

No. Your second SQL query will only reduce the number of records written, but SAS will still need to pull all records from your ODBC connection to be able to perform the join.

Push your IMPORT1 SAS dataset into your ODBC database and perform the join there.

Or if the number of records is small enough use a macro variable to generate the list of ANUM values to include it in the query. Something like this:

 proc sql noprint ;
   select ANUM into :list separated by ',' from import1;
   connect .... ;
   select ... from connection to odbc
    (... where aelextnbr in (&list)
    );
 quit;