Creating and Managing Views Using PROC SQL

A Brief Overview

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. A view contains only the descriptor and other information required to retrieve the data values from other SAS files or external files. The view contains only the logic for accessing the data, but 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 instead of an actual SAS table. Like tables, views are considered to be SAS data sets. 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 tables or views.
The following statements are true about PROC SQL views:
  • They can be used in SAS programs in place of an actual SAS data file.
  • They can be joined with tables or other views.
  • They can be derived from one or more tables, PROC SQL views, or DATA step views.
  • They can access data from a SAS data set, a DATA step view, a PROC SQL view, or a relational database table.
  • They extract underlying data, which enables you to access the most current data.
Views are useful because for these reasons:
  • They often save space (a view is usually quite small compared with the data that it accesses).
  • They prevent users from continually submitting queries to omit unwanted columns or rows.
  • They ensure that input data sets are always current, because data is derived from tables at execution time.
  • They shield sensitive or confidential columns from users while enabling the same users to view other columns in the same table.
  • They hide complex joins or queries from users.

The CREATE VIEW Statement

CREATE VIEW Statement Syntax

You use the CREATE VIEW statement to create a view.
Syntax, CREATE VIEW statement:
CREATE VIEW proc-sql-view AS
SELECT column-1<, ... column-n>
FROM table-1 | view-1<, ... table-n | view-n>
<optional query clauses>;
  • proc-sql-view specifies the name of the PROC SQL view that you are creating.
  • SELECT specifies the column(s) to appear in the table.
  • FROM specifies the table(s) or view(s) to be queried.
  • optional query clauses are used to refine the query further and include the WHERE, GROUP BY, HAVING, and ORDER BY clauses.
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 tables or views. 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.

Example: Creating a PROC SQL View

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 Certadv.Faview creates a virtual table from the accompanying SELECT statement. Although the underlying tables, Certadv.Payrollmaster and Certadv.Staffmaster, can change, the instructions that make up 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 certadv.faview as
      select lastname, firstname ,
             int((today()-dateofbirth)/365.25) as Age,
             substr(jobcode,3,1) as Level,
             salary
         from certadv.payrollmaster,
              certadv.staffmaster
         where jobcode contains 'FA' and 
               staffmaster.empid=
               payrollmaster.empid;
quit;
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.
Log 5.1 SAS Log
NOTE: SQL view CERTADV.FAVIEW has been defined.
Tip
It is helpful to give a PROC SQL view a name that easily identifies it as a view (for example, Faview or Fav).
Note: In the Windows and UNIX operating environments, the default extension for PROC SQL views and DATA step views is .sas7bvew.

Example: Using a PROC SQL View

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 Certadv.Faview is used in a query. Because the query that is 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 Certadv.Faview were a static table, instead of a view, the age shown for each flight attendant would never change.
proc sql;
   select *
      from certadv.faview;
quit;
Output 5.7 PROC SQL Query Result: Certadv.Faview (partial output)
Partial Output: PROC SQL Query Result: Certadv.Faview
Tip
You can use PROC SQL views in other SAS procedures and DATA steps.

The DESCRIBE VIEW Statement

DESCRIBE VIEW Statement Syntax

You can use a DESCRIBE VIEW statement to display a definition of a view in the SAS log.
Syntax, DESCRIBE VIEW statement:
DESCRIBE VIEW proc-sql-view-1<,...proc-sql-view-n>;
proc-sql-view
specifies a PROC SQL view and can be one of the following:
  • a one-level name
  • a two-level libref.view name
  • a physical pathname that is enclosed in single quotation marks
Tip
If you use a PROC SQL view in a DESCRIBE VIEW statement that is based on or derived from another view, then you might want to use the FEEDBACK option in the PROC SQL statement. This option displays in the SAS log how the underlying view is defined and expands any expressions that are used in this view definition.

Example: Displaying the Definition of a PROC SQL View

The following PROC SQL step writes the view definition for Certadv.Faview to the SAS log. 
proc sql;
   describe view certadv.faview;
quit;
Log 5.2 SAS Log
NOTE: SQL view CERTADV.FAVIEW is defined as:

        select lastname, firstname,  INT((TODAY() - dateofbirth) / 365.25) as Age,
SUBSTR(jobcode, 3, 1) as Level, salary
          from CERTADV.PAYROLLMASTER, CERTADV.STAFFMASTER
         where jobcode contains 'FA' and (staffmaster.empid = payrollmaster.empid);

Managing PROC SQL Views

Guidelines for Using PROC SQL Views

  • Avoid using an ORDER BY clause in a view definition, which causes the data to be sorted every time the view is executed. Users of the view might differ in how or whether they want the data to be sorted, so it is more efficient to specify an ORDER BY clause in a query that references the view.
  • If the same data is used many times in one program or in multiple programs, it is more efficient to create a table rather than a view because the data must be accessed at each view reference. This table can be a temporary table in the Work library.
  • Avoid creating views that are based on tables whose structure might change. A view is no longer valid when it references a nonexistent column.
  • If a view resides in the same SAS library as the contributing tables, it is best to specify a one-level name in the FROM clause.

Omitting the Libref

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 Certadv.Payrollv. The FROM clause specifies a two-level name for the contributing table, Certadv.Payrollmaster. However, it is not necessary to specify the libref Certadv because the contributing table is assumed to be stored in the same library as the view. 
proc sql;
   create view certadv.payrollv as
      select *
         from certadv.payrollmaster;
quit;
When the one-level name Payrollmaster is used in the FROM clause, Certadv.Payrollmaster is being specified, even though it appears that Work.Payrollmaster is being specified. 
proc sql;
   create view certadv.payrollv as
      select *
         from payrollmaster;
quit;
Note: If you are creating a view that is stored in a different library from that of the tables that are referenced in the FROM clause, you must specify a two-level name for the tables.

Using an Embedded LIBNAME Statement

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 for these reasons:
  • It can be used regardless of whether the view and the underlying tables reside in the same library.
  • It avoids the confusion that might arise if a libref is omitted from a table name in the FROM clause.
An embedded LIBNAME statement can be used only with a PROC SQL view. A libref that is created with an embedded LIBNAME statement will not conflict with an identically named libref in the SAS session.
Syntax, USING clause:
USING libname-clause-1<,... libname-clause-n>;
libname-clause
is one of the following:
  • a valid LIBNAME statement
  • a valid SAS/ACCESS LIBNAME statement
Tip
The USING clause must be the last clause in the CREATE VIEW statement.

Example: Using an Embedded LIBNAME Statement

In the following example, while the view Certadv.Payrollv is executing in the PROC PRINT step, the libref Airline is dynamically assigned in the USING clause.
proc sql;
   create view certadv.payrollv as
      select*
         from airline.payrollmaster
         using libname airline 'SAS-library-one';
quit;
proc print data=certadv.payrollv;
run;
If an earlier assignment of the libref AIRLINE exists, the EMBEDDED LIBNAME statement overrides the assignment 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.

Creating a View to Enhance Table Security

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.
Note: Although PROC SQL views can be used to enhance table security, it is strongly recommended that you use the security features that are available in your operating environment to maintain table security.
The following PROC SQL step creates the view Certadv.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 ensure the following results:
  • Level-1 flight attendants cannot read the data stored in the Fa2 and Fa3 libraries.
  • Level-2 flight attendants cannot read the data stored in the Fa1 and Fa3 libraries.
  • Level-3 flight attendants cannot read the data stored in the Fa1 and Fa2 libraries.
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 certadv.infoview as 
      select *
         from fa1.info
      outer union corr
      select *
         from fa2.info
      outer union corr
      select *
         from fa3.info;
quit;

Updating PROC SQL Views

A Brief Overview

You can update the data underlying a PROC SQL view using the INSERT, DELETE, and UPDATE statements under the following conditions:
  • You can update only a single table through a view. The table cannot be joined or linked to another table, nor can it contain a subquery.
  • You can update a column using the column's alias, but you cannot update a derived column. A derived column is a column that is produced by an expression.
  • You can update a view that contains a WHERE clause. The WHERE clause can be specified in the UPDATE clause or in the view. You cannot update a view that contains any other clause such as an ORDER BY or a HAVING clause.
  • You cannot update a summary view, which is a view that contains a GROUP BY clause.
Updating a view does not change the stored instructions for the view. Only the data in the underlying tables is updated.

Example: Updating PROC SQL Views

The following PROC SQL step creates the view Certadv.Raisev, which includes the columns Salary and MonthlySalary. A subsequent query that references the view shows the columns. 
proc sql;
   create view certadv.raisev as
      select empid, jobcode, 
             salary format=dollar12., 
             salary/12 as MonthlySalary
             format=dollar12.
         from certadv.payrollmaster;
quit;
proc sql;
   select *
      from certadv.raisev
      where jobcode in ('PT2','PT3');
quit;
Output 5.8 PROC SQL Query Result: Certadv.Raisev
PROC SQL Query Result: Certadv.Raisev
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 certadv.raisev
      set salary=salary * 1.20 
      where jobcode='PT3';
quit;
Log 5.3 SAS Log
NOTE: 2 rows were updated in CERTADV.RAISEV.
Note: Remember that the rows were updated in the table that underlies the view Certadv.Raisev.
When you resubmit the query, the updated values for Salary and MonthlySalary appear in the rows where JobCode equals PT3.
proc sql;
   select *
      from certadv.raisev
      where jobcode in ('PT2', 'PT3');
quit;
Output 5.9 PROC SQL Query Result: Certadv.Raisev View with Updated Values
PROC SQL Query Result: Certadv.Raisev View with Updated Values

The DROP VIEW Statement

DROP VIEW Statement Syntax

Use the DROP VIEW statement to drop or delete a view.
Syntax, DROP VIEW statement:
DROP VIEW view-name-1 <,...view-name-n>;
view-name
specifies a SAS data view of any type (PROC SQL view or DATA step view) and can be one of the following:
  • a one-level name
  • a two-level libref.view name
  • a physical pathname that is enclosed in single quotation marks

Example: Dropping a PROC SQL View

The following PROC SQL step drops the view Certadv.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 certadv.raisev;
quit;
Log 5.4 SAS Log
NOTE: View CERTADV.RAISEV has been dropped.
Last updated: October 16, 2019
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.144.38.24