Passthru SQL (from Fraktal SAS Programming)

What is this?

The term "passthru" is short for "pass through" and is to express that SQL code "passed" is processed by another system that is connected by appropriate middleware in a way that system borders are not visible when looking at the code.

"SAS" implemented this technique to allow the advanced and SQL experienced programmer full control over the SQL code processed on the DBMS side. Without passthru SQL the code sent to the DBMS is prepared (generated and optimized) on the SAS side only.

We will use a maximum simplified task to be coded in SQL:

• A DBMS data table shall be created
• Data for which are kept in another DBMS data table
• The DBMS talks ANSI SQL
• SAS can connect to the DBMS using SAS/Connect software
• The solution uses Proc SQL from Base SAS software

Documented Code Example

Code executed	Function performed
proc sql;	Start the SQL interpreter inside SAS
connect to oracle (user="&DB_USR." password="&DB_PWD." path="&DB_PTH." );	Open connection to the DBMS using your credentials stored in Macro Variables
execute	Start coding a passthru SQL code segment
( create table my_ora_tab as select * from ops$&DB_USR..&MYTABLE. )	Inside brackets type your DBMS SQL code as if you were using some DBMS client or frontend; Macro Variables are resolved before code is passed to any processing system
by oracle	Specify the executing DBMS by connection name opened before
;	Pass the code through to DBMS by issuing the SAS statement terminator ';'
disconnect from oracle;	Close connection to DBMS
quit;	Terminate SAS SQL interpreter status