SQL Procedure

CREATE VIEW Statement

Creates a PROC SQL view from a query expression.

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

Syntax

Required Arguments

Details

Sorting Data Retrieved by Views

Librefs and Stored Views

Updating Views

Embedded LIBNAME Statements

Syntax

CREATE VIEW proc-sql-view <(column-name-list)> AS query-expression

<USING libname-clause<, … libname-clause>> ;

Required Arguments

column-name-list

is a comma-separated list of column names for the view, to be used in place of the column names or aliases that are specified in the SELECT clause. The names in this list are assigned to columns in the order in which they are specified in the SELECT clause. If the number of column names in this list does not equal the number of columns in the SELECT clause, then a warning is written to the SAS log.

query-expression

See query-expression

libname-clause

is one of the following:

LIBNAME libref <engine> ' SAS-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)>

See SAS Statements: Reference for information about the Base SAS LIBNAME statement. See SAS/ACCESS for Relational Databases: Reference for information about the LIBNAME statement for relational databases.

order-by-item

See ORDER BY Clause

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.

Details

Sorting Data Retrieved by Views

PROC SQL enables you to specify the ORDER BY clause in the CREATE VIEW statement. When a view with an ORDER BY clause is accessed, and the ORDER BY clause directly affects the order of the results, its data is sorted and displayed as specified by the ORDER BY clause. However, if the ORDER BY clause does not directly affect the order of the results (for example, if the view is specified as part of a join), then PROC SQL ignores the ORDER BY clause in order to enhance performance.

Note: If the ORDER BY clause is omitted, then a particular order to the output rows, such as the order in which the rows are encountered in the queried table, cannot be guaranteed—even if an index is present. Without an ORDER BY clause, the order of the output rows is determined by the internal processing of PROC SQL, the default collating sequence of SAS, and your operating environment. Therefore, if you want your results to appear in a particular order, then use the ORDER BY clause.

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

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 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 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 enables 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 CREATE VIEW 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 about the SAS/ACCESS LIBNAME statement, see the SAS/ACCESS documentation for your DBMS.

Note: Starting in SAS System 9, PROC SQL views, the pass-through facility, and the SAS/ACCESS LIBNAME statement are the preferred ways to access relational DBMS data; SAS/ACCESS views are no longer recommended. You can convert existing SAS/ACCESS views to PROC SQL views by using the CV2VIEW procedure. For more information, see Chapter 33, “CV2VIEW Procedure” in SAS/ACCESS for Relational Databases: Reference.

You can also embed a SAS LIBNAME statement in a view with the USING clause, which 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-library';