Unlock the Power of SAS Views

Do you find SAS data manipulation to be too complex? If so, there’s a solution that can make your life easier: Views. These are not just any datasets, but more like markers that help you navigate to your data destination without the need for extensive data manipulation. In this easy-to-follow guide, we’ll unwrap the mystery around three types of SAS Views: DATA Step Views, PROC SQL Views, and SAS/ACCESS Views.

The Magic of SAS Views

Think of SAS Views as your magical map. They don’t hold any treasure (data), but they show you where it’s hidden.

In traditional datasets, the data is stored physically, much like how treasure would be stored in a chest. However, SAS Views don’t store the data; they merely point to where the data can be found in the original datasets.

The best part? They’re dynamic, resource-efficient, and oh-so-flexible!

• Dynamic: They update automatically when the original data changes.
• Resource-Efficient: They take up very little disk space since they don’t store the data.
• Flexible: You can modify them without affecting the original data.

What You Must Know About SAS Views

The Enchanting Compilation

When you run your program, the View gets its magical essence (it’s compiled). But here’s the catch: the records from your original dataset only get processed when you actually call upon the View in another DATA or PROC Step.

The One-by-One Spell

Just like a wizard summoning elements, the View sends data one record at a time to the ‘calling’ DATA or PROC Step. Think of it as a magical chant (MACRO); if you don’t utter it (reference the View), nothing—absolutely nothing—happens.

The Never-Ending Magic

Every time you call upon your View, the records get processed. But be cautious, young wizard; this magic is potent and can have effects each time it’s invoked. (We’ll dive deeper into this magical caution later!)

The Rule of One

Here’s the golden rule in the wizarding world of SAS: You can conjure only ONE View per DATA Step. So choose your spells (Views) wisely!

There you have it! These are your magical scrolls of wisdom for mastering SAS Views. Keep these points close to your wizard’s robe; you’ll find them invaluable in your SAS adventures.

DATA Step Views: The Simple Magicians

What Are They?

DATA Step Views are your basic-level wizards. They’re great for simple spells like data transformations.

What Are They?

DATA Step Views are your basic-level wizards. They’re great for simple spells like data transformations.

How to Create

Here’s a code to create a DATA Step View:

data work.data_step_view / view=work.data_step_view;
	set sashelp.class;
	where age > 14;
run;

How to Use

Just call upon the view like you would any other dataset:

proc print data=work.data_step_view;
run;

PROC SQL Views

What Are They?

If you require to execute complicated queries, you can utilize PROC SQL Views.

How to Create

Here’s the code:

proc sql;
	create view work.sql_view as select * from sashelp.class where age > 14;
quit;

How to Use

Invoke the view in the same manner as a DATA Step View:

proc print data=work.data_step_view;
run;

Why Use a View?

You can preprocess your data using a View instead of creating an intermediate dataset, which can clutter your SAS WORK area.

The Scenario: Merging Two Data Sets

Suppose you have two data sets—SalesData contains detailed transaction records, and ProductInfo contains product categories. You want to aggregate the sales data by product category and join it with ProductInfo.

The Challenge

The SalesData set is massive and contains transactions for multiple years, but you’re only interested in the sales for the current year. Also, SalesData doesn’t contain the ‘Category’ variable, which is available in ProductInfo.

The Solution: Using traditional approach

/*Approach 1*/
/* Step 1: Filter SalesData for the year 2023 */

data work.filtered_sales;
	set work.SalesData;
	where Year=2023;
run;

/* Step 2: Aggregate Sales by ProductID */
proc sql;
	create table work.aggregated_sales as select ProductID, sum(SalesAmount) as 
		TotalSales from work.filtered_sales group by ProductID;
quit;

/* Step 3: Join with ProductInfo */
proc sql;
	create table work.final_data as select a.*, b.TotalSales from work.ProductInfo 
		as a left join work.aggregated_sales as b on a.ProductID=b.ProductID;
quit;

The Solution: Using a PROC SQL View

Instead of creating a new, filtered, and aggregated data set, you can use a PROC SQL View to preprocess SalesData. Here’s how:

/* Create a PROC SQL View to filter and aggregate SalesData */

proc sql;
	create view work.filtered_sales_v as select ProductID, sum(SalesAmount) as 
		TotalSales from work.SalesData where Year=2023 group by ProductID;
quit;

/* Now join ProductInfo and the View */
proc sql;
	create table work.aggregated_data_2 as select a.*, b.TotalSales from 
		work.ProductInfo as a left join work.filtered_sales_v as b on 
		a.ProductID=b.ProductID;
quit;

Let’s break down the example of filtering and aggregating sales data using a PROC SQL View.

Normally, you’d have to create a new dataset to hold the filtered and aggregated data, which would involve additional disk I/O operations and storage space.

The Clock is Ticking: Why Time Matters in Data Processing

We’ve all been there staring at the screen, waiting for SAS to finish processing a chunk of data. But what if you could speed things up? To give you a real sense of how much time you could save, let’s put the traditional and view-based approaches to the test.

The Traditional Approach

In the traditional approach, we filter the data, aggregate it, and then join it with another dataset. Sounds simple, but each of these steps creates a new dataset, eating up both time and storage space.

Here’s is the snapshot of the processing time.:

The View-Based Stopwatch

In the view-based approach, we use SAS Views to do all these operations on-the-fly, without creating any new datasets. Neat, right?

And the time?

The Time-Saver Medal Goes To…

It’s clear as day: opting for a view shave off time, even if it’s just a fraction of a second—0.01 seconds to be exact—compared to going the traditional route without views.

But Wait, There’s More: The Real-World Scenario

Now, you might be thinking, “Hey, this is a small difference. Does it really matter?” Fair point. But let’s scale this up to real-world proportions. Imagine you’re dealing with millions of records. Those extra seconds saved by using views can add up to minutes or even hours!

So, the next time you find yourself waiting for SAS to process data, remember time is of the essence, and views could be your shortcut to a quicker result.

How Views Work?

1. View Creation: When you create a view using a PROC SQL statement, SAS stores only the SQL query and metadata about the view, not the actual data. The view acts like a saved query.
2. View Compilation: The first time you reference the view in a SAS session, SAS compiles the view but doesn’t execute it. Compilation involves checking the syntax and resolving variable names, but no data is read or written.
3. View Execution: When you actually use the view in a DATA step or another PROC step, SAS executes the saved SQL query on-the-fly. It reads data from the SalesData dataset, filters it for the year 2023, and aggregates the sales by ProductID.
4. Data Passing: The view passes this processed data to the subsequent DATA or PROC step, one record at a time. It’s like a real-time filter and aggregator.
5. No Extra Storage: Because the view doesn’t store data, it doesn’t take up additional disk space. This makes it resource efficient.

By using a PROC SQL View, you’ve efficiently filtered and aggregated the sales data without creating an additional data set. Now, your aggregated data table, work.aggregated_data, contains all the necessary information, including the product categories and total sales.

Here are tips to keep in mind as you work with Views:

1. In SAS, you can’t create a view and a data set in the same DATA step using the / view= option. You’ll need to separate them into two different DATA steps if you want to create both a view and a data set.
2. You can create a maximum of one view per data step, but you can also create one or more data sets when you create a view.
3. If you create a view with a name that has previously been given to a view, the new view will overwrite the old one. However, if you try to create a view with the same name as a SAS dataset that already exists in the same library, you will get an error.
4. You cannot sort a view to itself because that creates a table with the same name as the view, so you must use an OUT= clause when applying PROC SORT to a view.
5. Be careful when referencing a View, as each reference will cause the data to be processed again.
6. You may create nested Views.

Conclusion

Understanding the types of views in SAS can enhance your data manipulation and retrieval capabilities. DATA Step and PROC SQL views each have their own advantages and use-cases.