Whether you can use pass-through is not really important: If the queries are relatively simple, SAS automatically converts to native SQL.
But joins between two different databases (in this case, SAS and DB2) are often slow. What happens is probably that SAS reads a record and then makes a query to get the matching values in the DB2 table.
The normal way to speed this up is to read the values from DB2 first, using a dynamically generated IN clause. Something like this:
proc sql noprint;
select distinct quote(trim(JJJJJ),"'") into :invalues
from SAS_TABLE
where JJJJJ is not null and IIIII='AAA';
create table extract as select
JJJJJ,
COL1 length = 8
format = $8.
informat = $8.
label = 'COL1',
COL2 length = 8
format = 6.
informat = 6.
label = 'COL2',
COL3 length = 8
format = 6.
informat = 6.
label = 'COL3'
from EXTERNAL
where JJJJJ in(&inclause);
proc sql;
create table work.GTGYHH as
select
EXTERNAL.COL1 ,
EXTERNAL.COL2,
EXTERNAL.COL3,
SAS_TABLE.COL4 length = 8
format = 6.
informat = 6.
label = 'COL4',
SAS_TABLE.COL5 length = 60
format = $60.
informat = $60.
label = 'COL5',
SAS_TABLE.COL6 length = 8
label = 'COL6',
SAS_TABLE.COL7 length = 8
label = 'COL7'
from
Extract EXTERNAL,
SAS_TABLE
where
SAS_TABLE.JJJJJ = EXTERNAL.JJJJJ
and SAS_TABLE.JJJJJ <> .
and SAS_TABLE.IIIII = "AAA"
;
quit;
There are a couple of limitations to this specific technique: SAS macro variables cannot be longer than 64K, and there is also a limitation to how many values DB2 will accept in an IN clause. In the "good" old days, that was only one thousand values, but the bar has been raised since then; I am not sure how high. Check how many values were read from DB2 in the SELECT INTO statement (look at macro variable SQLOBS), and check the length of the generated macro variable INVALUES.
If you hit one of those limitations, it is probably easier to split the code by writing a temporary SAS program and %INCLUDE that, e.g.:
filename tempsas temp;
proc sql;
create table invalues as select distinct JJJJJ from SAS_TABLE;
quit;
data _null_;
set invalues end=done;
file tempsas temp;
if _N_=1 then
put 'set';
select(mod(_N_,2000));
when(1)
put 'EXTERNAL(keep=JJJJJ COL1 COL2 COL3 where=(JJJJJ in(' "'" JJJJJ +(-1) "'";
when(0) do;
put "'" JJJJJ +(-1) "')))";
if done then do;
put ';';
stop;
end;
end;
otherwise
put "'" JJJJJ +(-1) "'";
end;
if done then put ')));';
run;
data extract;
%include tempsas;
/* put label and format statements here */
run;
And then continue with the last part of my previous example.
