SQL Procedure Pass-Through Facility

The SQL Procedure Pass-Through Facility is an extension of the SQL procedure that enables you to send DBMS-specific statements to a DBMS and to retrieve DBMS data. You specify DBMS SQL syntax instead of SAS SQL syntax when you use the Pass-Through Facility. You can use Pass-Through Facility statements in a PROC SQL query or store them in a PROC SQL view.

The Pass-Through Facility consists of three statements and one component:

• The CONNECT statement establishes a connection to the DBMS.

• The EXECUTE statement sends dynamic, non-query DBMS-specific SQL statements to the DBMS.

• The CONNECTION TO component in the FROM clause of a PROC SQL SELECT statement retrieves data directly from a DBMS.

• The DISCONNECT statement terminates the connection to the DBMS.

The following Pass-Through Facility example sends a query to an ORACLE database for processing:

 proc sql;      connect to oracle as myconn (user=smith password=secret         path='myoracleserver');      select *         from connection to myconn            (select empid, lastname, firstname, salary               from employees               where salary>75000);      disconnect from myconn;   quit; 

The example uses the Pass-Through CONNECT statement to establish a connection with an ORACLE database with the specified values for the USER=, PASSWORD=, and PATH= arguments. The CONNECTION TO component in the FROM clause of the SELECT statement allows data to be retrieved from the database. The DBMS-specific statement that is sent to ORACLE is enclosed in parentheses. The DISCONNECT statement terminates the connection to ORACLE.

To store the same query in a PROC SQL view, use the CREATE VIEW statement:

 libname viewlib  'SAS-data-library'  ;   proc sql;      connect to oracle as myconn (user=smith password=secret         path='myoracleserver');   create view viewlib.salary as      select *         from connection to myconn            (select empid, lastname, firstname, salary               from employees               where salary>75000);      disconnect from myconn;   quit;