Previous Page | Next Page

Creating and Updating Tables and Views

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


Creating Views

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.  [cautionend]


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;

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.


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.  [cautionend]

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.

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

Tips for Using SQL Procedure Views


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.

Previous Page | Next Page | Top of Page