O'Reilly logo

SAS 9.4 SQL Procedure User's Guide, Fourth Edition, 4th Edition by SAS Institute

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Creating and Using PROC SQL Views
Overview of 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 for the following reasons:
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 row
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
Note: You can create a view that has two columns with the same variable name.
However, if duplicate variable names exist in a view, PROC COMPARE cannot
determine which column in the base data set should be compared to the column in
the compare data set. As a result, PROC COMPARE issues an error if it finds
duplicate variable names.
Creating Views
To create a PROC SQL view, use the CREATE VIEW statement, as shown in the
following example:
libname sql 'SAS-library';
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;
Creating and Using PROC SQL Views 131
Output 4.14 An SQL Procedure View
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” on page 136.
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” on page 131:
proc sql;
describe view sql.newcontinents;
Log 4.5 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;
To define a password-protected SAS view, you must specify a password. If the SAS
view was created with more than one password, you must specify its most restrictive
password if you want to access a definition of the view.For more information, see
“DESCRIBE Statement” on page 247.
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.
132 Chapter 4 Creating and Updating Tables and Views

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required