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 because they

• 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 rows.

• 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.

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

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

                                                    Total
     Continent                                 Population       Total Area
     ---------------------------------------------------------------------
                                                  384,772          876,800
     Africa                                   710,529,592       11,299,595
     Asia                                   3,381,858,879       12,198,325
     Australia                                 18,255,944        2,966,200
     Central America and Caribbean             66,815,930          291,463
     Europe                                   813,335,288        9,167,084
     North America                            384,801,818        8,393,092
     Oceania                                    5,342,368          129,600
     South America                            317,568,801        6,885,418

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.  

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;

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.

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';

To delete a view, use the DROP VIEW statement:

proc sql;
   drop view sql.newcontinents;

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.

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

     Name                                      Population         TotCarib
     ---------------------------------------------------------------------
     Bangladesh                               126,387,850       66,815,930
     Brazil                                   160,310,357       66,815,930
     China                                  1,202,215,077       66,815,930
     Germany                                   81,890,690       66,815,930
     India                                    929,009,120       66,815,930
     Indonesia                                202,393,859       66,815,930
     Japan                                    126,345,434       66,815,930
     Mexico                                    93,114,708       66,815,930
     Nigeria                                   99,062,003       66,815,930
     Pakistan                                 123,062,252       66,815,930
     Philippines                               70,500,039       66,815,930
     Russia                                   151,089,979       66,815,930
     United States                            263,294,808       66,815,930
     Vietnam                                   73,827,657       66,815,930

• 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 the SAS/CONNECT User's Guide.

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.

Copyright © 2008 by SAS Institute Inc., Cary, NC, USA. All rights reserved.