SAS Data View Programming Considerations

Data Sets of Type VIEW

Beginning with SAS 6, SAS data sets can be classified as member type DATA (SAS data file) or member type VIEW. A data set of type VIEW is called a SAS data view. It contains a definition or description of data that is stored elsewhere. SAS data views can be created by using a DATA step, the SQL procedure, or the ACCESS procedure in SAS/ACCESS software. In most SAS programs, whether the data comes from a SAS data view or data file is not important.
Many SAS/ACCESS interface products, such as DB2 or Oracle, enable you to update product data by using a SAS/ACCESS view. However, for views that are interpreted in the server's session, whether you can update a view's underlying DBMS data depends on the specific SAS/ACCESS interface engine that you are using. For information about how to use SAS/ACCESS engines in a SAS/SHARE server session, see the SAS/ACCESS documentation.

Interpreting SAS Data Views

A SAS data view that is accessed by using a server can be interpreted in either the server or the client session.
The user specifies where a SAS data view is interpreted by specifying the RMTVIEW= option in the LIBNAME statement. When RMTVIEW= YES or the option is omitted, a data view is interpreted in the server session. When RMTVIEW= NO, a data view is interpreted in the client session. For more information about the LIBNAME statement and its options, see Remote Library Services.
Interpreting a view consists of loading and calling the view engine to read the view's underlying data. When a view is interpreted in the client session, the view engine is loaded and called by the client to read and present the underlying data and present it as a SAS data set. When a view is interpreted in the server session, the view engine is loaded and called by the server to read and present the underlying data.
Whether a SAS data view is interpreted in a client or a server session, the underlying data must be accessible to its view engine. Data accessibility is based on whether the view was created by using a DATA step, PROC SQL, or PROC ACCESS.
For DATA step views, “accessible” means that any external file (or files) must be available and that any filerefs and librefs that are stored in the view must be defined in the SAS session in which the view is interpreted.
For PROC SQL views, “accessible” means that all librefs that are used in the view must be predefined in the SAS session in which the view is interpreted, or included in the USING clause of the query that is stored in the view. This libref can be associated with a SAS library that is accessed through a server or a library that is stored at the client. You do not have to specify a libref in a PROC SQL view for data sets that are in the same data library as the view itself.
For PROC ACCESS views, “accessible” means that the interface product and its data, as well as the SAS/ACCESS interface view engine, must be available to the SAS session in which the view is interpreted.
Where SAS data views should be interpreted in a shared environment is based on the following:
  • how the view was created
  • how the view's data will be used
  • specific site considerations
Is the underlying data accessible? If the data is accessible only from one of the sessions, the view must be interpreted there.
If the data is accessible from the client session and the server session, then performance must be a consideration. If interpreting the view requires the SAS session to read a large number of rows in order to select a small subset, having the SAS/SHARE server interpret the view greatly reduces the number of records that are transmitted to the client session. This method reduces network load and might be faster than having the client session interpret the view. However, putting a heavy processing load on the server (especially if joins are involved) might adversely affect server performance for other clients.
If the view selects most of the input rows or if the selection criteria are processed by a DBMS server, interpreting the view in the client session is probably preferable.

Example: Using RLS and a DATA Step View to Improve the Performance of PROC APPEND

The following code shows the creation and storage of a master data set on a SAS/SHARE server:
   libname share 'path-to-library' server=shr1;
   
   data share.master;
      do key = 1 to 100000;
         var = 'Original Value' ;
         output;
      end;
      array othervars {20};
   run;
The following program creates a transaction data set, which PROC APPEND adds to the master data set that is located on the server:
   data transactions;
      do key = 100001 to 200000 by 10;
         var = 'New Value: '||put(key,z6.);
         output;
      end;
   run;

   proc append base=share.master new=transactions force;
   run;
With the following changes to the preceding code, append processing can be shifted to a SAS/SHARE server.
Note: When using a SAS/SHARE server, you will see performance gains increase as the size of the transaction data set increases.
The SAS DATA step view can be created and stored on the server before the view is actually used. In order to create the view, a copy of the transactions data set (in this example, swork.transactions) must be available as a “template” for the expected contents (for example, variables and variable length). The template does not have to contain any observations.
libname swork slibref=work server=shr1;

   data swork.transactions;
      do key = 100001 to 200000 by 10;
         var = 'New Value: '||put(key,z6.);
         output;
      end;
   run;

   data share.append_view share.master / view=share.append_view;
      modify share.master;
      set swork.transactions;
      output;
   run;
The following DATA step now references the view that is located on the server and causes the view to be interpreted and run on the server:
data _null_;
   set share.append_view;
 run;
After the initial setup of the DATA step view, future production runs that use the Append view might appear as follows:
libname share '' server=shr1;
   libname swork slibref=work server=shr1;

   data swork.transactions;
      do key = 100001 to 200000 by 10;
         var = 'New Value: '||put(key,z6.);
         output;
      end;
   run;

   data _null_;
       set share.append_view;
   run;
Although there are several ways to create SAS DATA step views in order to improve the performance of the update process when using a SAS/SHARE server, the preceding example shows a simple way to create views. The update process can be improved by using the MODIFY statement in a DATA step view that gets interpreted at the server.
Note: You should consider the advantages and disadvantages of using the MODIFY statement to create views. A primary advantage is that the MODIFY statement allows locking at the observation level rather than at the member level. Locking at the observation level is less restrictive than locking at the member level. However, the MODIFY statement is not the most efficient query technique.