8.2. Views — Windows to Your Data
Views are one of the more powerful features available in the SQL procedure. They are commonly referred to as “virtual tables” to distinguish them from base tables. The simple difference is that views are not tables, but files consisting of executable instructions. As a query, a view appears to behave as a table with one striking difference — it does not store any data. When referenced, a view produces results just like a table does. So how does a view get its data? Views access data from one or more underlying tables (base tables) or other views, provide you with your own personal access to data, and can be used in DATA steps as well as by SAS procedures.
Views offer improved control, manageability, and security in dynamic environments where data duplication or data redundancy, logic complexities, and data security are an issue. When used properly, views enable improved change control by providing enhanced data accessibility, hiding certain columns from unauthorized users, while enabling improved maintainability.
Data references are coded one time and, once a view is tested, can be conveniently stored in common and shareable libraries making them accessible for all to use. Views ensure that the most current input data is accessed and prevent the need for replicating partial or complete copies of the input data. As a means of shielding users from complex logic constructs, views can be designed to look as though a database were designed specifically for a single user as well as for a group of users, each with differing needs.
Views are also beneficial when queries or subqueries are repeated a number of times throughout an application. In these situations the addition of a view enables a change to be made only once, improving a your productivity through a reduction in time and resources. The creation of view libraries should be considered so users throughout an organization have an easily accessible array of productivity routines as they would a macro.
8.2.1. What Views Aren’t
Views are not tables, but file constructs containing compiled code that access one or more underlying tables. Because views do not physically store data, they are referred to as “virtual” tables. Unlike tables, views do not physically contain or store rows of data. Views, however, do have a physical presence and take up space. Storage demands for views are minimal because the only portion saved is the SELECT statement or query itself. Tables, on the other hand, store one or more rows of data and their attributes within their structure.
Views are created with the CREATE VIEW statement while tables are created with the CREATE TABLE statement. Because you use one or more underlying tables to create a virtual (derived) table, views provide you with a powerful method for accessing data sources.
Although views have many unique and powerful features, they also have pitfalls. First, views generally take longer to process than tables. Each time a view is referenced, the current underlying table or tables are accessed and processed. Because a view is not physically materialized until it is accessed, higher utilization costs are typically involved, particularly for larger views. Next, views cannot create indexes on the underlying base tables. This can make it more difficult to optimize views.
8.2.2. Types of Views
Views can be designed to achieve a number of objectives:
Referencing a single table
Producing summary data across a row
Concealing sensitive information
Creating updatable views
Grouping data based on summary functions or a HAVING clause
Using set operators
Combining two or more tables in join operations
Nesting one view within another
As a way of distinguishing the various types of views, Joe Celko introduced a classification system based on the type of SELECT statement used (see SQL for Smarties: Advanced SQL Programming).
To help you understand the different view types, this chapter describes and illustrates view construction as well as how they can be used. A view can also have the characteristics of one or more view types, thereby being classified as a hybrid. A hybrid view, for example, could be designed to reference two or more tables, perform updates, and contain complex computations. The table below presents the different view types along with a brief description of their purpose.
A Description of the Various View Types
8.2.3. Creating Views
You use the CREATE VIEW statement in the SQL procedure to create a view. When the SQL processor sees the words CREATE VIEW, it expects to find a name assigned to the newly created view. The SELECT statement defines the names assigned to the view’s columns as well as their order.
Views are often constructed so that the order of the columns is different from the base table. In the next example, a view is created with the columns appearing in a different order from the original MANUFACTURERS base table. The view’s SELECT statement does not execute during this step because its only purpose is to define the view in the CREATE VIEW statement.
SQL Code
PROC SQL; CREATE VIEW MANUFACTURERS_VIEW AS SELECT manuname, manunum, manucity, manustat FROM MANUFACTURERS; QUIT;
SAS Log Results
PROC SQL;
CREATE VIEW MANUFACTURERS_VIEW AS
SELECT manuname, manunum, manucity,
|
manustat
FROM MANUFACTURERS;
NOTE: SQL view WORK.MANUFACTURERS_VIEW has been
When you create a view, you can create columns that are not present in the base table from which you built your view. That is, you can create columns that are the result of an operation (addition, subtraction, multiplication, etc.) on one or more columns in the base tables. You can also build a view using one or more unmodified columns of one or more base tables. Columns created this way are referred to as derived columns or calculated columns. In the next example, say we want to create a view consisting of the product name, inventory quantity, and inventory cost from the INVENTORY base table and a derived column of average product costs stored in inventory.
PROC SQL;
CREATE VIEW INVENTORY_VIEW AS
SELECT prodnum, invenqty, invencst,
invencst/invenqty AS AverageAmount
FROM INVENTORY;
QUIT;SAS Log Results
PROC SQL;
CREATE VIEW INVENTORY_VIEW AS
SELECT prodnum, invenqty, invencst,
invencst/invenqty AS AverageAmount
FROM INVENTORY;
NOTE: SQL view WORK.INVENTORY_VIEW has been defined.
QUIT;
NOTE: PROCEDURE SQL used:
real time 0.00 seconds |
8.2.4. Displaying a View’s Contents
You would expect the CONTENTS procedure to display information about the physical characteristics of a SAS data library and its tables. But what you may not know is that the CONTENTS procedure can also be used to display information about a view. The output generated from the CONTENTS procedure shows that the view contains no rows (observations) by displaying a missing value in the Observations field and a member type of View. The engine used is the SQLVIEW. The following example illustrates the use of the CONTENTS procedure in the Windows environment to display the INVENTORY_VIEW view’s contents.
SQL Code
PROC CONTENTS DATA=INVENTORY_VIEW; RUN;
The SAS System
The CONTENTS Procedure
Data Set Name WORK.INVENTORY_VIEW
|
8.2.5. Describing View Definitions
Because views consist of partially compiled executable statements, ordinarily you will not be able to read the code in a view definition. However, the SQL procedure provides a statement to inspect the contents of the executable instructions (stored query expression) contained within a view definition. Without this capability, a view’s underlying instructions (PROC SQL code) would forever remain a mystery and would make the ability to modify or customize the query expressions next to impossible. Whether your job is to maintain or customize a view, the DESCRIBE VIEW statement is the way you review the statements that make up a view. Let’s look at how a view definition is described.
The next example shows the DESCRIBE VIEW statement being used to display the INVENTORY_VIEW view’s instructions. It should be noted that results are displayed in the SAS log, not in the Output window.
PROC SQL;
DESCRIBE VIEW INVENTORY_VIEW;
QUIT;SAS Log Results
PROC SQL;
DESCRIBE VIEW INVENTORY_VIEW;
NOTE: SQL view WORK.INVENTORY_VIEW is defined as:
select prodnum, invenqty, invencst,
invencst/invenqty as CostQty_Ratio
from INVENTORY;
QUIT;
NOTE: PROCEDURE SQL used:
real time 0.05 seconds |
8.2.6. Creating and Using Views in the SAS System
Views are accessed the same way as tables. The SQL procedure permits views to be used in SELECT queries, subsets, joins, other views, and DATA and PROC steps. Views can reference other views (as will be seen in more detail in a later section), but the referenced views must ultimately reference one or more existing base tables.
The only thing that cannot be done is to create a view from a table or view that does not already exist. When this is attempted, an error message is written in the SAS log indicating that the view is being referenced recursively. An error occurs because the view being referenced directly (or indirectly) by it cannot be located or opened successfully. The next example shows the error that occurs when a view called NO_CAN_DO_VIEW is created from a non-existing view by the same name in a SELECT statement FROM clause.
SQL Code
PROC SQL; CREATE VIEW NO_CAN_DO_VIEW AS SELECT * FROM NO_CAN_DO_VIEW; SELECT * FROM NO_CAN_DO_VIEW; QUIT;
PROC SQL;
CREATE VIEW NO_CAN_DO_VIEW AS
SELECT *
FROM NO_CAN_DO_VIEW;
NOTE: SQL view WORK.NO_CAN_DO_VIEW has been defined.
SELECT *
FROM NO_CAN_DO_VIEW;
ERROR: The SQL View WORK.NO_CAN_DO_VIEW is
|
8.2.7. Views and SAS Procedures
In most cases but not all, views can be used just as input SAS data sets to the universe of available SAS procedures. In the first example, the INVENTORY_VIEW view is used as input to the MEANS procedure to produce simple univariate descriptive statistics for numeric variables. Accessing the INVENTORY_VIEW view is different from accessing the INVENTORY table because the view’s internal compiled executable statements are processed providing current data from the underlying table to the view itself. The view statements and the statements and options from the MEANS procedure determine what information is produced.
The next example uses the INVENTORY_VIEW view as input to the MEANS procedure to produce simple univariate descriptive statistics for numeric variables. Accessing the INVENTORY_VIEW view is different from accessing the INVENTORY table because the view derives and provides current data from the underlying table to the view itself. The view statements and the statements and options from the MEANS procedure determine what information is produced.
SAS Code
PROC MEANS DATA=INVENTORY_VIEW; TITLE1 'Inventory Statistical Report'; TITLE2 'Demonstration of a View used in PROC MEANS'; RUN;
SAS Log Results
PROC MEANS DATA=INVENTORY_VIEW;
TITLE1 'Inventory Statistical Report';
TITLE2 'Demonstration of a View used in
|
Results
Inventory Statistical Report
Demonstration of a View used in
|
The next example uses the INVENTORY_VIEW view as input to the PRINT procedure to produce a detailed listing of the values contained in the underlying base table.
| Note: | It is worth noting that, as with all procedures, all procedure options and statements are available by views. |
SAS Code
PROC PRINT DATA=INVENTORY_VIEW N NOOBS UNIFORM; TITLE1 'Inventory Detail Listing'; TITLE2 'Demonstration of a View used in PROC PRINT'; format AverageAmount dollar10.2; RUN;
SAS Log Results
PROC PRINT DATA=INVENTORY_VIEW N NOOBS UNIFORM;
TITLE1 'Inventory Detail Listing';
TITLE2 'Demonstration of a View used in a
|
Results
Inventory Detail Listing
Demonstration of a View used in PROC PRINT
Average
prodnum invenqty invencst Amount
1110 20 $45,000.00 $2
|
8.2.8. Views and DATA Steps
As we have already seen, views can be used as input to SAS procedures as if they were data sets. You will now see that views are a versatile component that can be used in a DATA step as well. This gives you a controlled way of using views to access tables of data in custom report programs. The next example uses the INVENTORY_VIEW view as input to the DATA step as if it were a SAS base table. Notice that the KEEP= data set option reads only two of the variables from the INVENTORY_VIEW view.
SAS Code
DATA _NULL_;
SET INVENTORY_VIEW (KEEP=PRODNUM AVERAGEAMOUNT);
FILE PRINT HEADER=H1;
PUT @10 PRODNUM
@30 AVERAGEAMOUNT DOLLAR10.2;
RETURN;
H1: PUT @9 'Using a View in a DATA Step'
/// @5 'Product Number'
@26 'Average Amount';
RETURN;
RUN;SAS Log Results
DATA _NULL_;
SET INVENTORY_VIEW (KEEP=PRODNUM AVERAGEAMOUNT);
FILE PRINT HEADER=H1;
PUT @10 PRODNUM
@30 AVERAGEAMOUNT DOLLAR10.2;
RETURN;
H1: PUT @9 'Using a View in a DATA Step'
/// @5 'Product Number'
@26 'Average Amount';
RETURN;
RUN;
NOTE: 11 lines were written to file PRINT.
NOTE: There were 7 observations read from the
|
Using a View in a DATA Step
Product Number Average Amount
1110 $2,250.00
1700 $2,800.00
5001 $200.00
5002 $300.00
5003 $200.00
5004 $70.00
5001 $600.00 |