About SAS/ACCESS Software |
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 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 enables 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 , use the CREATE VIEW statement:
libname viewlib 'SAS-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;
Copyright © 2010 by SAS Institute Inc., Cary, NC, USA. All rights reserved.