A PROC SQL view is a stored query expression that reads data values from its underlying files, which can include SAS data files, DATA step views, other PROC SQL views, or DBMS data.

You can refer to views in queries as if they were tables. The view derives its data from the tables or views that are listed in its FROM clause. The data that is accessed by a view is a subset or superset of the data that is in its underlying table(s) or view(s).

PROC SQL views

In this chapter, you learn to

Before beginning this chapter, you should complete the following chapters:

A PROC SQL view is a stored query that is executed when you use the view in a SAS procedure, DATA step, or function. A view contains only the descriptor and other information required to retrieve the data values from other SAS files (SAS data files, DATA step views, or other PROC SQL views) or external files (DBMS data files). The view contains only the logic for accessing the data, not the data itself.

Because PROC SQL views are not separate copies of data, they are referred to as virtual tables. They do not exist as independent entities like real tables. However, views use the same naming conventions as tables and can be used in SAS programs in place of an actual SAS table. Like tables, views are considered to be SAS data sets.

Views are useful because they

You use the CREATE VIEW statement to create a view.

A PROC SQL view derives its data from the tables or views that are listed in the FROM clause. The data that is accessed by a view is a subset or superset of the data that is in its underlying table(s) or view(s). When a view is referenced by a SAS procedure or in a DATA step, it is executed and, conceptually, an internal table is built. PROC SQL processes this internal table as if it were any other table.

The following PROC SQL step creates a view that contains information for flight attendants. The view always returns the employee's age as of the current date.

The view Sasuser.Faview creates a virtual table from the accompanying SELECT statement. Although the underlying tables, Sasuser.Payrollmaster and Sasuser.Staffmaster, can change, the instructions that comprise the view stay constant. The libref specified in the FROM clause is optional. It is assumed that the contributing tables are stored in the same library as the view itself, unless otherwise specified.

proc sql;
      create view sasuser.faview as 
      select lastname, firstname, gender,
             int((today()-dateofbirth)/365.25) as Age,
             substr(jobcode,3,1) as Level,
             salary
         from sasuser.payrollmaster,
              sasuser.staffmaster
         where jobcode contains 'FA' and
               staffmaster.empid=
               payrollmaster.empid;

When this PROC SQL step is submitted, SAS does not actually execute the SELECT statement that follows the AS keyword, but partially compiles and stores the SELECT statement in a data file with a member type of VIEW. A message in the SAS log confirms that the view has been defined.

Table 7.1. SAS Log

You can use a view in a subsequent PROC SQL step, or later in the same step, just as you would use an actual SAS table. In the following example, the PROC SQL view Sasuser.Faview is used in a query. Because the query stored in the view calculates the age of each flight attendant based on the current date, the resulting output from this PROC SQL step shows each flight attendant's age as of the current date. If Sasuser.Faview were a static table, instead of a view, the age shown for each flight attendant would never change.

proc sql;
    select *
      from sasuser.faview;

Partial output is shown below.

The following PROC SQL step writes the view definition for Sasuser.Faview to the SAS log:

proc sql;
   describe view sasuser.faview; 

Table 7.2. SAS Log

When you are working with PROC SQL views, it is best to follow these guidelines:

The default libref for the table or tables in the FROM clause is the libref of the library that contains the view. Using a one-level name in the FROM clause prevents you from having to change the view if you assign a different libref to the SAS library that contains the view and its contributing table or tables.

The following PROC SQL step creates the view Sasuser.Payrollv. The FROM clause specifies a two-level name for the contributing table, Sasuser.Payrollmaster. However, it is not necessary to specify the libref Sasuser because the contributing table is assumed to be stored in the same library as the view.

proc sql;
   create view sasuser.payrollv as
      select *
         from sasuser.payrollmaster; 

When the one-level name Payrollmaster is used in the FROM clause, Sasuser.Payrollmaster is being specified, though it appears that Work.Payrollmaster is being specified.

proc sql;
   create view sasuser.payrollv as
      select *
         from payrollmaster; 

As an alternative to omitting the libref in the FROM clause, you can embed a LIBNAME statement in a USING clause to store a SAS libref in a view. Embedding a LIBNAME statement is a more flexible approach because

An embedded LIBNAME statement can be used only with a PROC SQL view. A libref created with an embedded LIBNAME statement will not conflict with an identically named libref in the SAS session.

In the following example, while the view Sasuser.Payrollv is executing in the PROC PRINT step, the libref Airline, which is specified in the USING clause, becomes active. This overrides the earlier assignment of the libref in the LIBNAME statement for the duration of the view's execution. After the view executes, the original libref assignment is re-established and the embedded assignment is cleared.

libname airline 'SAS-library one';
proc sql;
   create view sasuser.payrollv as
      select*
         from airline.payrollmaster
         using libname airline 'SAS-library two'; 
quit;
proc print data=sasuser.payrollv;
run;

One advantage of PROC SQL views is that they can bring data together from separate sources. This enables views to be used to shield sensitive or confidential columns from some users while enabling the same users to view other columns in the same table.

The following PROC SQL step creates the view Manager.Infoview. The view accesses data about flight attendants that is stored in three SAS libraries: Fa1, Fa2, and Fa3. The Fa1, Fa2, and Fa3 libraries can be assigned access privileges at the operating system level to prevent

Access privileges can also be assigned to permit managers (who are authorized to access all SAS libraries) to view all of the information.

proc sql;
   create view manager.infoview as 
      select *
         from fa1.info 
      outer union corr
      select * 
         from fa2.info 
      outer union corr
      select * 
         from fa3.info; 

The following PROC SQL step creates the view Sasuser.Raisev, which includes the columns Salary and MonthlySalary. A subsequent query that references the view shows the columns.

Suppose you want to update the view to show a salary increase for employees whose job code is PT3. You can use an UPDATE statement to change the column Salary and a WHERE clause in the UPDATE clause to identify the rows where the value of JobCode equals PT3. Though MonthlySalary is a derived column and cannot be changed using an UPDATE statement, it will be updated because it is derived from Salary.

When the PROC SQL step is submitted, a note appears in the SAS log that indicates how many rows were updated:

proc sql;
   update sasuser.raisev
      set salary=salary * 1.20
      where jobcode='PT3';

Table 7.3. SAS Log

When you resubmit the query, the updated values for Salary and MonthlySalary appear in the rows where JobCode equals PT3:

The following PROC SQL step drops the view Sasuser.Raisev. After the step is submitted, a message appears in the SAS log to confirm that the view has been dropped.

proc sql;
   drop view sasuser.raisev;

Table 7.4. SAS Log

  PROC SQL;

            CREATE VIEW proc-sql-view AS

                     SELECT column-1<, … column-n>

                              FROM table-1 | view-1<, … table-n | view-n>

                              <optional query clauses>;

                              USING libname-clause<,…libname-clause>;

    DESCRIBE VIEW proc-sql-view<,…proc-sql-view>;

    INSERT INTO table-name | proc-sql-view

                  <(target-column-1<, … target-column-n)>

               SET column-1=value-1< , … column-n=value-n>

               < … SET column-1=value-1<, … column-n=value-n>>;

    DELETE FROM table-name | proc-sql-view

               < WHERE expression>;

    UPDATE table-name | proc-sql-view

               SET column-1=expression< , … column-n=expression>>

               <WHERE expression>;

    DROP VIEW view-name< ,…view-name>;

  QUIT;