Unleash the Power of SAS Data Views

Let’s say you read a flat file into a SAS data file. If the values in the flat file change, you need to update the data file to reflect those changes so that you can use the data file to get the correct values. But what if you use a data view in place of your flat file?

Before working with data views, let’s look at what a SAS data view is and how it compares to a SAS data file.

SAS files can be either datasets or data views. Datasets contain values and characteristics of data, while data views only include information on how to access the data values.

Views in SAS

A SAS view is a virtual data set that extracts data values from other files to provide a customized and dynamic data representation.

• A view contains no data but rather references data that is stored elsewhere.
• In most cases, you can use a SAS view as if it were a SAS data set. For example, you can reference a view to read data in DATA steps or PROC steps.
• The SAS member type is VIEW, and the file extension in most operating environments is .sas7bvew.

Here are the types of SAS views:

DATA step view – It is a stored DATA step program.

PROC SQL view– It is a stored query expression that is created in PROC SQL.

Let’s say you read a flat file into a SAS data file. If the values in the flat file change, you need to update the data file to reflect those changes so that you can use the data file to get the correct values.

However, suppose you use a data view to access the values in your flat file; the data view will automatically update to reflect those changes the next time it is referenced.

Most of the time, you can use a SAS data view the same way you would a SAS data file, but there are a few things to keep in mind when working with data views.

DATA Step Views

A DATA step view can be created only in a DATA step. Therefore, a DATA step view cannot contain global statements, host-specific data set options, or most host-specific FILE and INFILE statements. Also, a DATA step view cannot be indexed or compressed.

You can use DATA step views to

• You can always access the most current data by changing files.
• To avoid storing a copy of a large data file.
• To combine data from multiple sources.

Compiled code takes up little storage space, so DATA step views can be used to conserve disk space. However, using DATA step views can increase CPU usage since SAS must execute the stored DATA step program every time.

You create a DATA step view by specifying the VIEW= option after the final data set name.

DATASAS-data-view <SAS-data-file-1 ... SAS data-file-n> /
          VIEW=SAS-data-view;
<SAS statements>
RUN;

I like to create views when I want to create a new variable in a huge data set but don’t want to physically copy the data. Then, when I analyze the data view using another procedure, the constructed variable is computed on the fly.

Here’s an example. Suppose you have a large dataset that includes heights and weights for millions of patients. You may need to analyze these patients’ body-mass index (BMI).

You have two options. The first option is to create a new data set with a new column titled “BMI.” This requires that you duplicate the original data and add a new column. 

The second option is to keep the original data unchanged but create a view that computes the BMI. Then, any analyst that needs the BMI can access the data by using the view.

data BMI / view=BMI;                 
   set Sashelp.Class;
   BMI = weight / height**2 * 703;   
run;
 
proc means data=BMI;
   var BMI;
run;

SAS saves the program upon encountering the RUN statement in the DATA step. As soon as the program is invoked in PROC MEANS via the DATA= option, SAS executes it and generates the BMI variable that is consumed and analyzed.

In addition to saving storage space and not cluttering up a data set with extra variables, a data view always uses current data if it uses regularly updated data (such as daily sales statistics).

PROC SQL Views

A PROC SQL view contains a stored query executed when you use the view in a SAS procedure or DATA step.

SQL views are defined in PROC SQL using a CREATE VIEW statement. Using the PROC SQL pass-through facility, they can contain code to connect to a database.

They can be in the program code or stored in a SAS library. These views are more efficient because they utilize the SQL Processor that optimizes the access strategy at execution.

CREATE VIEW proc-sql-view AS
SELECTcolumn-1<, ... column-n>
FROMtable-1 | view-1<, ... table-n | view-n>
            <optional query clauses>;

The data in a PROC SQL view comes from the tables or views in the FROM clause. The data accessed by a view is a subset or superset of the data in its underlying tables or views.

When a view is referenced by a SAS procedure or in a DATA step, it is executed, and an internal table is built conceptually. PROC SQL processes this internal table as if it were any other table.

proc sql;
	create view BMI_V as select *, weight / height**2 * 703 as BMI from 
		sashelp.class;
quit;

You can use a view in a subsequent PROC SQL step or later in the same step, just like an actual SAS table. In the following example, the PROC SQL view BMI_V is used in the select query. 

proc sql;
select * from bmi_v;
quit;

Connect to

One of the essential features of SQL views is that they can connect to other software vendors’ products, such as DB2, ORACLE, MSAccess, MSExcel, or MSSQLServer. The CONNECTION TO and CONNECT TO statements allow this access.

You can place these statements in a view so your users can access the database without struggling with the code. For example, the following is the code for connecting to DB2:

proc sql;
connect to db2 as mycon (ssid=yourid)
create view as libname.viewname
select *
 from connection to mycon
 select table.field
 from libname.table
 where some_condition is true;
disconnect from mycon;

SQL Views with Embedded LIBNAME Statements

The library’s libref containing the view is the default libref for the tables in the FROM clause. Using a one-level name, you won’t have to alter the view if you assign a different libref to the SAS library that contains the view and its contributing tables.

The following PROC SQL step creates the view dsn.class_v. The FROM clause specifies a two-level name for the contributing table, dsn.class. 

However, it isn’t 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 dsn.class_v as select * from dsn.class where age>13;
quit;

When the one-level name class is used in the FROM clause, dsn.class_v1 is being specified, though it appears that Work.class is being specified.

proc sql;
	create view dsn.class_v1 as select * from class where age>13;
quit;

Note: If you create a view stored in a different library than the tables referenced in the FROM clause, you must specify a two-level name for the 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 combining data from separate sources. This enables views to shield sensitive or confidential columns from some users while allowing the same users to view other columns in the same table.

Embedding a LIBNAME statement is more flexible because it can be used whether or not the view and its underlying tables are in the same library. It also avoids confusion if a libref is omitted from a table name in the FROM clause.

Only a PROC SQL view can be used with an embedded LIBNAME statement. A libref created with an embedded LIBNAME statement won’t conflict with a libref in the SAS session with the same name.

In the following example, while the view sasdsn.arena_v is executing in the PROC PRINT step, the libref sasdsn, specified in the USING clause, becomes active. This overrides the earlier assignment of the libref in the LIBNAME statement 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.

libname sasdsn '/home/subhroster20070/9to5sas/external_files';
proc sql;
	create view sasdsn.arena_v as select* from sasdsn.arena using 
		libname sasdsn '/home/subhroster20070/9to5sas/dsn';
quit;
proc print data=sasdsn.arena_v;
run;

Dropping a View

You can use the DROP VIEW statement to drop or delete a view.

The following PROC SQL step drops the view BMI_V. After the step is submitted, a message appears in the SAS log to confirm that the view has been dropped.

proc sql;
	drop view BMI_V;
quit;

SAS Views vs Tables

The main difference between a SAS view and a SAS table or data set is where the data values are stored:

• A view contains metadata (descriptor information) and instructions for retrieving data, but a view does not store the data values.
• A data set contains both the metadata and the data values.

If you are deciding whether to create a data set or a view, consider the different features:

Conclusion

As data becomes more and more complex, and as the speedier technology continues to advance, data views will likely become an even bigger part of the SAS user’s toolkit. The benefits are clear in terms of ease of use, relevancy, and faster implementation, and these factors alone may be enough to spur the adoption of SAS data views by SAS users worldwide.

This article should give you a good idea of what data views are and how they can be useful. However, what you do with data views is only limited by your imagination. With these versatile objects, you can get as creative as you want, which can help improve your SAS programming and simplify your work.