How the Pass-Through Facility Works

When you read and update DBMS data with the Pass-Through Facility, SAS/ACCESS passes SQL statements directly to the DBMS for processing. Here are the steps:

1. Invoke PROC SQL and submit a PROC SQL CONNECT statement that includes a DBMS name and the appropriate connection options to establish a connection with a specified database.

2. Use a CONNECTION TO component in a PROC SQL SELECT statement to read data from a DBMS table or view. In the SELECT statement (that is, the PROC SQL query) that you write, use the SQL that is native to your DBMS. SAS/ACCESS passes the SQL statements directly to the DBMS for processing. If the SQL syntax that you enter is correct, the DBMS processes the statement and returns any results to SAS. If the DBMS does not recognize the syntax that you enter, it returns an error that appears in the SAS log. The SELECT statement can be stored as a PROC SQL view. Here is an example.

   proc sql;
   connect to oracle (user=scott password=tiger);
   create view budget2000 as select amount_b,amount_s 
      from connection to oracle 
      (select Budgeted, Spent from annual_budget);
   quit;

3. Use a PROC SQL EXECUTE statement to pass any dynamic, non-query SQL statements (such as INSERT, DELETE, and UPDATE) to the database. As with the CONNECTION TO component, all EXECUTE statements are passed to the DBMS exactly as you submit them. INSERT statements must contain literal values. For example:

   proc sql;
      connect to oracle(user=scott password=tiger);
      execute (create view whotookorders as select ordernum, takenby, 
        firstname, lastname,phone from orders, employees 
        where orders.takenby=employees.empid) by oracle;
      execute (grant select on whotookorders to testuser) by oracle;
   disconnect from oracle;
   quit;

4. Terminate the connection with the DISCONNECT statement.

For more details, see The Pass-Through Facility for Relational Databases.

Copyright © 2009 by SAS Institute Inc., Cary, NC, USA. All rights reserved.