The SQL Procedure

CREATE VIEW Statement

Creates a PROC SQL view from a query-expression.

See also: What Are Views?

Featured in: Creating a View from a Query's Result

CREATE VIEW proc-sql-view AS query-expression

<ORDER BY order-by-item <,order-by-item>...>

<USING statement<, libname-clause> ... > ;

where each libname-clause is one of the following:

LIBNAME libref <engine> ' SAS-data-library' <option(s)> <engine-host-option(s)>

LIBNAME libref SAS/ACCESS-engine-name <SAS/ACCESS-engine-connection-option(s)> <SAS/ACCESS-engine-LIBNAME-option(s)>

Arguments

order-by-item: See ORDER BY Clause .
query-expression: See query-expression .
proc-sql-view: specifies the name for the PROC SQL view that you are creating. See What Are Views? for a definition of a PROC SQL view.

Sorting Data Retrieved by Views
PROC SQL allows you to specify the ORDER BY clause in the CREATE VIEW statement. Every time a view is accessed, its data are sorted and displayed as specified by the ORDER BY clause. This sorting on every access has certain performance costs, especially if the view's underlying tables are large. It is more efficient to omit the ORDER BY clause when you are creating the view and specify it as needed when you reference the view in queries.

Note: If you specify the NUMBER option in the PROC SQL statement when you create your view, the ROW column appears in the output. However, you cannot order by the ROW column in subsequent queries. See the description of the NUMBER option . [cautionend]

Librefs and Stored Views
You can refer to a table name alone (without the libref) in the FROM clause of a CREATE VIEW statement if the table and view reside in the same SAS data library, as in this example:

      create view proclib.view1 as
         select *
            from invoice
            where invqty>10;

In this view, VIEW1 and INVOICE are stored permanently in the SAS data library referenced by PROCLIB. Specifying a libref for INVOICE is optional.

Updating Views
You can update a view's underlying data with some restrictions. See Updating PROC SQL and SAS/ACCESS Views .

Embedded LIBNAME Statements
The USING clause allows you to store DBMS connection information in a view by embedding the SAS/ACCESS LIBNAME statement inside the view. When PROC SQL executes the view, the stored query assigns the libref and establishes the DBMS connection using the information in the LIBNAME statement. The scope of the libref is local to the view, and will not conflict with any identically named librefs in the SAS session. When the query finishes, the connection to the DBMS is terminated and the libref is deassigned.

The USING clause must be the last clause in the SELECT statement. Multiple LIBNAME statements can be specified, separated by commas. In the following example, a connection is made and the libref ACCREC is assigned to an ORACLE database.

create view proclib.view1 as
   select *
      from accrec.invoices as invoices 
      using libname accrec oracle
         user=username pass=password
         path='dbms-path';

For more information on the SAS/ACCESS LIBNAME statement, see the SAS/ACCESS documentation for your DBMS.

You can also embed a SAS LIBNAME statement in a view with the USING clause. This enables you to store SAS libref information in the view. Just as in the embedded SAS/ACCESS LIBNAME statement, the scope of the libref is local to the view, and it will not conflict with an identically named libref in the SAS session.

create view work.tableview as
   select * from proclib.invoices
      using libname proclib 'sas-data-library';

Chapter Contents
Previous
Next
Top of Page

See also:	What Are Views?
Featured in:	Creating a View from a Query's Result