SAS 9.4 Language Reference, 3rd Edition

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.

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

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 681