When you read and update DBMS data
with the SQL pass-through facility,
SAS/ACCESS passes SQL statements
directly to the DBMS for processing. Here are the steps.
-
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.
-
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;
-
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.
Here is an 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;
isconnect from oracle;
quit;
-
Terminate the connection
with the DISCONNECT statement.