A PROC SQL view is a stored query that is executed when you use the view in a SAS procedure or DATA step. 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 do the following:

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.

1    proc sql;
2            create view sasuser.faview as
3              select lastname, firstname, gender,
4                     int((today()-dateofbirth)/365.25)
5                        as Age,
6                     substr(jobcode,3,1) as Level,
7                     salary
8                 from sasuser.payrollmaster,
9                      sasuser.staffmaster
10                where jobcode contains 'FA' and
11                     staffmaster.empid=
12                     payrollmaster.empid;
NOTE: SQL view SASUSER.FAVIEW has been defined.

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.

proc tabulate data=sasuser.faview;
   class level; 
   var age; 
   table level*age*mean;
run;