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)


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:

SELECT           name as FName, sex, age, height FORMAT=6.1, weight FORMAT=6.1
FROM               sashelp.class
WHERE           age<13

The table PRETEEN looks like Exhibit 10-1.

For more information about this book or to read a free chapter or user reviews, visit Howard Schreier's author page.


About Author

Shelly Goodin

Social Media Specialist, SAS Publications

Shelly Goodin is SAS Publications' social media marketer and the editor of "SAS Publishing News". She’s worked in the publishing industry for over thirteen years, including seven years at SAS, and enjoys creating opportunities for fans of SAS and JMP software to get to know SAS Publications' many offerings and authors.

Related Posts

Leave A Reply

Back to Top