How to use explicit passthrough in PROC SQL?

Nico Chart_21517
Nico Chart_21517
Altair Employee
edited June 2023 in Altair RapidMiner

Explicit passthrough is typically used when you wish to
1. pass the whole query to the DBMS without any intervention from PROC SQL.
2. pass some DBMS specific SQL syntax to the DBMS that does not form part of the standard PROC SQL syntax.

Explicit passthrough is defined using the proc sql CONNECT, DISCONNECT, EXECUTE and CONNECTION TO constructs.
If the statement is a dml statement or something that does not return a result set then EXECUTE can be used e.g:

proc sql;
CONNECT TO ODBC(..options-as-per-libname..);
EXECUTE(drop table mytable) BY ODBC;
DISCONNECT FROM ODBC;

If the sql must return a result set then use CONNECTION TO:

proc sql;
CONNECT TO ODBC(..options-as-per-libname..);
select * from CONNECTION TO ODBC(select a.col1, b.col2 from a, b where a.col2 = b.col2);
DISCONNECT FROM ODBC;

In both cases what is inside the EXECUTE() and CONNECTION TO() is passed unmodified to the database.

n.b. If you already have a LIBNAME set up for the database connection then you can use CONNECT USING instead of CONNECT TO.

Tagged:

Welcome!

It looks like you're new here. Sign in or register to get started.

Welcome!

It looks like you're new here. Sign in or register to get started.