How SAS/ACCESS Works How the ACCESS Procedure Works 51
4 If SAS/ACCESS approves the translation, it sends an approval message to PROC
SQL and the query (or query fragment) gets processed by the DBMS, which
returns the results to SAS. Any queries or query fragments that can not be passed
to the DBMS are processed in SAS.
See the chapter on performance considerations for detailed information about tasks that
SAS/ACCESS can pass to the DBMS.
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 speciļ¬ed 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 (that is, the PROC SQL query) can be stored as a PROC SQL
view. For 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.
How the ACCESS Procedure Works
When you use the ACCESS procedure to create an access descriptor, the
SAS/ACCESS interface view engine requests the DBMS to execute a SQL SELECT
