Before you learn more about SAS Views you should look at what is SAS Data Set. SAS views are built on top of the SAS data set hence it is pivotal to know more about what happens in the backend when you create SAS data set.
What Is SAS Data Set?
A SAS data set is a SAS file stored in a SAS library that SAS creates and processes. A SAS data set contains data values that are organised as a table of observations (rows) and variables (columns) that can be processed by SAS software.
A SAS data set also contains descriptor information such as the data types and lengths of the variables, as well as which engine was used to create the data.
A SAS data set can be one of the following SAS data file or SAS view.
- SAS data file: It contains both the DATA and the DESCRIPTOR information. SAS data files have a member type of DATA.
- SAS view: It contains ONLY the DESCRIPTOR information that points to DATA stored elsewhere. SAS views have a member type of VIEW.
What Is SAS View?
A SAS view is a type of SAS data set that retrieves data values from other files. A SAS view contains only descriptor information such as the data types and lengths of the variables (columns).
A SAS view also contains information that is required for retrieving data values from other SAS data sets or from files that are stored in other software vendors’ file formats. SAS views are of member type VIEW.
In most cases, you can use a SAS view as if it were a SAS data file. When you create a view it’s registered under SAS library and you can locate the view metadata with SAS-view-name with extension of [dot]sas7bvew file. Please note this is not a data file. It has only descriptor information.
Descriptor and Data Portion Of SAS Views
The descriptor and data portion information of SAS view are the same as SAS data set. The only difference here is how they both get stored physically and their data types. You can say, SAS views are part of or subset of SAS data sets.
The proc contents procedure can also be used on SAS views to print descriptor information of a SAS view. Here is the simplest example.
The sample dataset sashelp.class used to create/define a SAS view in WORK library with a new name “CLASS_V”
/* create a demo SAS view from SAS data set */
proc sql;
create view work.class_v as
select * from sashelp.class;
quit;
/* print descriptor information */
proc contents data=work.class_v;
run;
Why Use SAS Views?
A view is like a stored query. Views allow for a “virtual tier”, between the database tables and user code. It creates greater flexibility in your data system. The views are efficient as they reuses the underline code.
Views can be used to reduce workspace, because SAS does not read the underlying data into a work dataset when a view is used. Data in large files can be selected into a subset using a view preventing the job from running out of space and failing
Types of Views In SAS
There are three types of SAS data views: DATA step views, PROC SQL views, and SAS/ACCESS views (view descriptors).
1. DATA Step Views
SAS DATA step views tell SAS how to process data using the code that you specify in the DATA step. Only one view can be created in a DATA step and the view name must match one of the DATA step names.
/* syntax */
data libref.VIEWNAME / view=libref.VIEWNAME;Example: The sashelp.cars dataset is used to demonstrate how to create a new view CARS_V under WORK library using the data step method.
/* 1. data step view */
data work.cars_v / view=work.cars_v;
set sashelp.cars;
where make="BMW";
run;
Describe Data Step View
Let’s say, someone has created a SAS data step view called work.cars_v and you want to know the it’s source code, a code that’s being executed in the backend while using this view.
You can use the DESCRIBE statement to retrieve source code from a stored compiled DATA step program or a DATA step view.
/* describe data step view */
data view=work.cars_v;
describe;
run;
2. PROC SQL Views
The PROC SQL views are defined in PROC SQL SAS procedure using a CREATE VIEW statement. They are based on SAS datasets, sequential files, databases, and other views.
They can contain code to connect to a database using the PROC SQL pass-through facility. These views are more efficient because at execution they utilize the SQL Processor that optimizes the access strategy.
Example: The sashelp.cars dataset is used to demonstrate how to create a new view work.CARS_V2 under WORK library using the proc sql method.
/* 2. proc sql view */
proc sql;
create view work.cars_v2 as
select * from sashelp.cats
where make="BMW";
quit;
Describe PROC SQL View
Let’s say, someone has created a SAS data step view called work.cars_v2 and you want to know the it’s source code, a code that’s being executed in the backend while using this view.
You can use the DESCRIBE statement with the PROC SQL procedure to retrieve source code from a stored compiled PROC SQL view.
/* describe proc sql view */
proc sql;
describe view work.cars_v2 ;
quit;
3. SAS/ACCESS Views
A SAS/ACCESS view is an interface view, also called a view descriptor, which accesses DBMS data that is defined in a corresponding access descriptor.
The SAS/ACCESS dynamic LIBNAME engine enables you to treat DBMS data as if it were SAS data by assigning a SAS libref to DBMS objects.
It means that you can use both native DATA step views and native PROC SQL views to access DBMS data instead of view descriptors.
Note: Starting in SAS 9, PROC SQL views are the preferred way to access relational DBMS data. You can convert existing SAS/ACCESS view descriptors into PROC SQL views by using the CV2VIEW procedure. This enables you to use the LIBNAME statement to access your data.
To demonstrate one example we are using ODBC views. ODBC stands for Open Database Connectivity. It is an interface standard that provides a common application programming interface (API) for accessing databases.
MSSQLServer Views:
SQL Server views can be connected to with an ODBC connection. You can use this view like a table in the database.
These views can change data in the underlying database. They allow for the processing to be done inside the database using the database which provides greater efficiency.
As recommended, we’ll use the PROC SQL method to create MSSQLServer Views.
The SQL pass through facility (CONNECT TO) and ODBC will be used to connect MS SQL server.
/* MSSQLServer Views */
proc sql;
connect to odbc as MYCON (DSN='MYLINK');
create view dbo.VIEW_1 AS
select * from SERVERNAME.SYSNAME.dbo.TABLE
where (VAR1 LIKE '%ABC%' or
VAR1 LIKE '%CDE%') and
(VAR2 = 'Some character string');
disconnect from MYCON;
quit;DB2 View
Similar to the MSSQL views you can also create DB2 views with little changes in the query. Here is the sample example of creating db2 views using SQL pass through facility (CONNECT TO) in the proc sql procedure.
/* db2 views */
proc sql;
connect to db2 as MYCON (SSID = YOURID);
create view FULL.NAME.MYVIEW AS
select TABLE.FIELD from FULL.TABLE.NAME TABLE
where SOME_CONDITION = TRUE;
disconnect from MYCON;
quit;ADDITIONAL RESOURCES:
FAQ – SAS Views – Explained In 5 Minutes
A SAS view is a virtual table that is defined based on existing data in SAS datasets or external data sources. Unlike a physical dataset, a view does not store data separately but provides a logical representation of data that can be accessed and manipulated like a table.
SAS views offer several advantages. They allow users to create customized views of data without duplicating storage, enabling efficient data management and storage optimization. Views also facilitate data abstraction, allowing users to work with subsets of data or present data in a structured format tailored to specific analysis needs.
Additionally, views can help improve data security by restricting access to sensitive information while still providing necessary insights.
SAS views and SAS datasets differ primarily in their storage and representation. A SAS dataset is a physical file that stores data on disk, occupying storage space.
In contrast, a SAS view is a logical structure that references data stored elsewhere, such as in other datasets or external databases, without duplicating the data. While SAS datasets contain actual data rows and columns, SAS views provide a virtual representation of data based on predefined criteria or conditions.
SAS views themselves are not directly modified or updated, as they represent a logical view of data rather than storing data independently.
However, the underlying data that a SAS view references can be modified or updated by changing the source datasets or data sources.
Any changes made to the source data will be reflected in the output of the SAS view the next time it is accessed or queried.
Additionally, users can redefine SAS views to incorporate new criteria or conditions for data selection or presentation.