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
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.
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 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
proc sql;
connect to oracle as myconn (user=smith password=secret
select *
from connection to myconn
(select empid, lastname, firstname, salary
from employees
where salary>75000);
disconnect from myconn;
SQL Procedure Pass-Through Facility 681

Get SAS 9.4 Language Reference, 3rd Edition now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.