of the CREATE VIEW statement enables you to store DBMS connection information in
a SAS view by embedding a SAS/ACCESS LIBNAME statement inside the SAS view.
The following example uses an embedded SAS/ACCESS LIBNAME statement:
libname viewlib 'SAS-library';
proc sql;
create view viewlib.emp_view as
select *
from mydblib.employees
using libname mydblib oracle user=smith password=secret
path='myoraclepath';
quit;
When PROC SQL executes the SAS view, the SELECT statement assigns the libref and
establishes the connection to the DBMS. The scope of the libref is local to the SAS view
and does not conflict with identically named librefs that might exist in the SAS session.
When the query finishes, the connection is terminated and the libref is deassigned.
Note: You can also embed a Base SAS LIBNAME statement in a PROC SQL view.
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.
Y
ou 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;
SQL Procedure Pass-Through Facility 71
1

Get SAS 9.4 Language Reference, 6th Edition now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.