Creating and Using PROC SQL Views :: SAS(R) 9.3 SQL Procedure User's Guide

Overview of Creating and Using PROC SQL Views

A PROC SQL view contains a stored query that is executed when you use the view in a SAS procedure or DATA step. Views are useful for the following reasons:

often save space, because a view is frequently quite small compared with the data that it accesses
prevent users from continually submitting queries to omit unwanted columns or row
shield sensitive or confidential columns from users while enabling the same users to view other columns in the same table
ensure that input data sets are always current, because data is derived from tables at execution time
hide complex joins or queries from users

Creating Views

To create a PROC SQL view, use the CREATE VIEW statement, as shown in the following example:

libname sql 'SAS-library';

proc sql;
   title 'Current Population Information for Continents';
   create view sql.newcontinents as
   select continent,
          sum(population) as totpop  format=comma15. label='Total Population',
          sum(area) as totarea format=comma15. label='Total Area'
      from sql.countries
      group by continent;

   select * from sql.newcontinents;

An SQL Procedure View

Current Population Information for Continents

Note: In this example, each column has a name. If you are planning to use a view in a procedure that requires variable names, then you must supply column aliases that you can reference as variable names in other procedures. For more information, see Using SQL Procedure Views in SAS Software.

Describing a View

The DESCRIBE VIEW statement writes a description of the PROC SQL view to the SAS log. The following SAS log describes the view NEWCONTINENTS, which is created in Creating Views:

proc sql;
   describe view sql.newcontinents;

SAS Log from DESCRIBE VIEW Statement

NOTE: SQL view SQL.NEWCONTINENTS is defined as:

        select continent, SUM(population) as totpop label='Total Population' 
format=COMMA15.0, SUM(area) as totarea label='Total Area' format=COMMA15.0
          from SQL.COUNTRIES
      group by continent;

To define a password-protected SAS view, you must specify a password. If the SAS view was created with more than one password, you must specify its most restrictive password if you want to access a definition of the view.For more information, see DESCRIBE Statement.

Updating a View

You can update data through a PROC SQL and SAS/ACCESS view with the INSERT, DELETE, and UPDATE statements, under the following conditions.

You can update only a single table through a view. The underlying table cannot be joined to another table or linked to another table with a set operator. The view cannot contain a subquery.
If the view accesses a DBMS table, then you must have been granted the appropriate authorization by the external database management system (for example, ORACLE). You must have installed the SAS/ACCESS software for your DBMS. See the SAS/ACCESS documentation for your DBMS for more information about SAS/ACCESS views.
You can update a column in a view by using the column's alias, but you cannot update a derived column, that is, a column that is produced by an expression. In the following example, you can update SquareMiles, but not Density:
```
proc sql;
   create view mycountries as
      select Name, 
             area as SquareMiles,
             population/area as Density
         from sql.countries;
```
You can update a view that contains a WHERE clause. The WHERE clause can be in the UPDATE clause or in the view. You cannot update a view that contains any other clause, such as ORDER BY, HAVING, and so on.

Embedding a LIBNAME in a View

You can embed a SAS LIBNAME statement or a SAS/ACCESS LIBNAME statement in a view by using the USING LIBNAME clause. When PROC SQL executes the view, the stored query assigns the libref. For SAS/ACCESS librefs, PROC SQL establishes a connection to a DBMS. The scope of the libref is local to the view and does not conflict with any identically named librefs in the SAS session. When the query finishes, the libref is disassociated. The connection to the DBMS is terminated and all data in the library becomes unavailable.

The advantage of embedded librefs is that you can store engine-host options and DBMS connection information, such as passwords, in the view. That, in turn, means that you do not have to remember and reenter that information when you want to use the libref.

Note: The USING LIBNAME clause must be the last clause in the SELECT statement. Multiple clauses can be specified, separated by commas.

In the following example, the libref OILINFO is assigned and a connection is made to an ORACLE database:

proc sql;
   create view sql.view1 as 
      select * 
         from oilinfo.reserves as newreserves
         using libname oilinfo 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.

The following example embeds a SAS LIBNAME statement in a view:

proc sql;
   create view sql.view2 as 
      select *
         from oil.reserves
         using libname oil 'SAS-data-library';

Deleting a View

To delete a view, use the DROP VIEW statement:

proc sql;
   drop view sql.newcontinents;

Specifying In-Line Views

In some cases, you might want to use a query in a FROM clause instead of a table or view. You could create a view and refer to it in your FROM clause, but that process involves two steps. To save the extra step, specify the view in-line, enclosed in parentheses, in the FROM clause.

An in-line view is a query that appears in the FROM clause. An in-line view produces a table internally that the outer query uses to select data. Unlike views that are created with the CREATE VIEW statement, in-line views are not assigned names and cannot be referenced in other queries or SAS procedures as if they were tables. An in-line view can be referenced only in the query in which it is defined.

In the following query, the populations of all Caribbean and Central American countries are summed in an in-line query. The WHERE clause compares the sum with the populations of individual countries. Only countries that have a population greater than the sum of Caribbean and Central American populations are displayed.

libname sql 'SAS-library';

proc sql;
   title 'Countries With Population GT Caribbean Countries';
   select w.Name, w.Population format=comma15., c.TotCarib 
      from (select sum(population) as TotCarib format=comma15.
	                 from sql.countries
			       where continent = 'Central America and Caribbean') as c,
           sql.countries as w
	    where w.population gt c.TotCarib;

Using an In-Line View

Countries With Population GT Caribbean Countries

Tips for Using SQL Procedure Views

Avoid using an ORDER BY clause in a view. If you specify an ORDER BY clause, then the data must be sorted each time that the view is referenced.
If data is used many times in one program or in multiple programs, then it is more efficient to create a table rather than a view. If a view is referenced often in one program, then the data must be accessed at each reference.
If the view resides in the same SAS library as the contributing table or tables, then specify a one-level name in the FROM clause. The default for the libref for the FROM clause's table or tables is the libref of the library that contains the view. This prevents you from having to change the view if you assign a different libref to the SAS library that contains the view and its contributing table or tables. This tip is used in the view that is described in Creating Views.
Avoid creating views that are based on tables whose structure might change. A view is no longer valid when it references a nonexistent column.
When you process PROC SQL views between a client and a server, getting the correct results depends on the compatibility between the client and server architecture. For more information, see “Accessing a SAS View” in Chapter 17 of SAS/CONNECT User's Guide.

Using SQL Procedure Views in SAS Software

You can use PROC SQL views as input to a DATA step or to other SAS procedures. The syntax for using a PROC SQL view in SAS is the same as that for a PROC SQL table. For an example, see Using SQL Procedure Tables in SAS Software.