Managing PROC SQL Views

Guidelines for Using PROC SQL Views

When you are working with PROC SQL views, it is best to follow these guidelines:
  • 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.

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 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;
CAUTION:
If you are creating a view that is stored in a different library than the table(s) referenced in the FROM clause, you must specify a two-level name for the table(s).

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 because
  • it can be used regardless of whether the view table and the underlying table 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 created with an embedded LIBNAME statement does not conflict with an identically named libref in the SAS session.
General form, USING clause:
USING libname-clause-1<,... libname-clause-n>;
Here is an explanation of the syntax:
libname-clause
is one of the following:
  • a valid LIBNAME statement
  • a valid SAS/ACCESS LIBNAME statement.
CAUTION:
The USING clause must be the last clause in the CREATE VIEW statement.

Example

In the following example, while the view Sasuser.Payrollv is executing in the PROC PRINT step, the libref Airline is dynamically assigned in the USING clause. 
proc sql;
   create view sasuser.payrollv as
      select*
         from airline.payrollmaster
         using libname airline 'SAS-library-one';
quit;
proc print data=sasuser.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 reestablished 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.
CAUTION:
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.

Example

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
  • Level 1 flight attendants from reading the data stored in the Fa2 and Fa3 libraries
  • Level 2 flight attendants from reading the data stored in the Fa1 and Fa3 libraries
  • Level 3 flight attendants from reading 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 manager.infoview as 
      select *
         from fa1.info
      outer union corr
      select *
         from fa2.info
      outer union corr
      select *
         from fa3.info;
..................Content has been hidden....................

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