SASBI.net

Recently I have found a troublesome bug in a pass-through facility (PTF). PTF allows one to connect to remote DB, to write code in its language and to send it to remote DB for execution. After execution the results are sent back to SAS. Therefore you can access all the features of the remote DB language as well as to the stored procedures. For instance once we used PTF and stored procedure to get history of accounts’ balances from the Automatic Banking System DB and improved data quality in the Data Warehouse.

PTF can be used only within PROC SQL. For instance the following code copies table from remote DB to SAS library.

proc sql;
/* connect to remote DB */
connect to odbc(dsn = oracle);

/* copy table */
create table copy as
select * from connection to odbc(
select *
from table -- table name
where clmn > 0
);

/* disconnect from remote DB */
disconnect from myConnect;
quit;

Code within round brackets after the line “select * from connection to odbc” is sent to remote ODBC for execution. The bug was found when I worked with Oracle. The following code should return rows with clmn > 0.

select *
from table –- table name
where clmn > 0

If you submit this code using PTF it will return all the records.

I guess that when SAS sends code to a remote DB it deletes end of row symbols. Therefore all operators after the comment “– table name” are affected. And the operator where doesn’t take part in the query.