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

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