SAS author's tip: views in PROC SQL

This week's SAS author's tip is perfect for SAS programmers using PROC SQL. Author Howard Schreier is a member of the SAS-L Hall of Fame, an independent consultant and trainer, and has been using SAS since 1981. His book PROC SQL by Example: Using SQL within SAS is also an all-star performer. It was easy choosing an excerpt from the many easy-to-follow examples featured in the book. I hope that you will find this week's selection beneficial to your work!

The following excerpt is from SAS Press author Howard Schreier's book PROC SQL by Example: Using SQL within SAS, Copyright © 2008, SAS Institute Inc., Cary, North Carolina, USA. ALL RIGHTS RESERVED. (please note that results may vary depending on your version of SAS software)

Views

A view can be thought of as a virtual table. The distinctive essence of a view is that only instructions (and not data) are stored when the view is defined. The instructions are not applied to the data source until the view is referenced. Consequently, the source does not have to exist when the view is defined, but it must exist whenever the view is used. This is the diametric opposite of the situation for a table. The source data for a table obviously must exist when the table is created, but are not needed to support later use of the table.

Both the DATA step and PROC SQL are capable of constructing views. These two kinds of views are interoperable in use, meaning that SQL views can be read by DATA steps and by procedures other than PROC SQL, and that PROC SQL can read DATA step views. However, the two kinds of views have some distinctly different properties. In contrast, tables created using PROC SQL are virtually indistinguishable from those generated by other parts of SAS.

(Reference: Read more about views in SAS 9.2 Language Reference: Concepts: SAS Files Concepts: SAS Views.)

Before we demonstrate the creation and use of views, let’s create a small table that can be used for most of the examples. The code is:

PROC SQL;
CREATE TABLE   preteen AS
SELECT           name as FName, sex, age, height FORMAT=6.1, weight FORMAT=6.1
FROM               sashelp.class
WHERE           age<13
;
QUIT;

The table PRETEEN looks like Exhibit 10-1.