Summary

Text Summary

Using PROC SQL Views

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). When executed, a PROC SQL view's output can be a subset or superset of one or more underlying files. A view contains no data, but describes or defines data that is stored elsewhere.
PROC SQL views
  • can be used in SAS programs in place of an actual SAS data file
  • can be joined with tables or other views
  • can be derived from one or more tables, PROC SQL views, DATA step views, or SAS/ACCESS views.
  • extract underlying data, which enables you to access the most current data.
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.

Creating SQL Views

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 tables(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. A view can be used in a subsequent PROC SQL step just as you would use an actual SAS table.

Displaying the Definition for a PROC SQL View

You can use a DESCRIBE VIEW statement to display a definition of a view in the SAS log.

Managing PROC SQL Views

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 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.
As a more flexible alternative to omitting the libref in the FROM clause, you can embed a LIBNAME statement in a USING clause if you want to store a SAS libref in a view. Embedding a LIBNAME statement in a USING clause does not conflict with an identically named libref in the SAS session.
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. 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.

Updating PROC SQL Views

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 column that is produced by an expression).
  • You can update a view that contains a WHERE clause. The WHERE clause can be 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 (a view that contains a GROUP BY clause).

Dropping PROC SQL Views

To drop (delete) a view, use the DROP VIEW statement.

Sample Programs

Creating a PROC SQL View

proc sql;
   create view sasuser.raisev as
      select empid, jobcode, 
             salary format=dollar12.2, 
             salary/12 as MonthlySalary
             format=dollar12.
         from payrollmaster
         using libname airline 'c:dataia';
quit;

Displaying the Definition for a PROC SQL View

proc sql;        
   describe view sasuser.raisev;
quit;

Using a PROC SQL View in a Query

proc sql;
   select *
      from sasuser.raisev
      where jobcode in ('PT2','PT3'),
quit;

Updating a PROC SQL View

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

Dropping a PROC SQL View

proc sql;
   drop view sasuser.raisev;
quit;

Points to Remember

  • 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 table(s), it is best to specify a one-level name in the FROM clause.
..................Content has been hidden....................

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