26135 - Filtering Data in SAS Enterprise Guide

Sample 26135: Filtering Data in SAS Enterprise Guide

Here are the first 10 rows of the Volcanoes data table. The goal is to create a subset of the data containing only volcanoes in North America and South America. To filter the data, click the data icon in the Project Explorer or Project Designer to make it active, and then select

Data ► Filter and Query

from the menu bar. This opens the Query Builder window, with the Select Data tab on top.

data table

Select all the columns that you want in the query result (in this example every column except Type), then click the Filter Data tab.

query builder

Select the column that you want to use for filtering purposes and drag it over to the box under the Filter Data tab. Notice that you can filter by columns that are not even part of the query result. In this example, drag Region to the filter area. When you drop the column, the Edit Filter window opens automatically.

query builder

Defining the filter
In the Edit Filter window, you can see the name of the column you dragged to the filter area. Initially, the operator is Equal to. You would use this operator if you wanted to filter all the rows that equal a particular value. But you are not limited to the Equal to operator. There are several operators to choose from when filtering your data. When you click the down arrow to the right of the Operator box, you can choose a different operator. In this example, the In a list of values operator is the most useful because it allows you to specify a list of unique values for Region.

edit filter

After choosing an operator, you will need to choose a value or values for your filter condition. You can type the values for your condition in the Values box if you like, but SAS Enterprise Guide can help you select values for your condition. Click Add next to the Values box. This opens a window where you can get all the values for the column. Click Get Values.

edit filter

You will see a list of all possible values for the column you selected, and the label on the Get Values button will change to More Values. Highlight one or more values by holding down the control key as you select values. In this example, select NA and SA, and then click OK. The values will be inserted into the Values box of the Edit Filter window.

edit filter

If you want to compare the value of the column being filtered to the value in a different column, then you can click the Columns tab to get a list of available columns and insert the column into the filter. This is not an option for the In a list of values operator.

Once you have set the filter condition, click OK and the filter will appear in the Filter Data tab of the Query Builder window. Notice that the box for the filter states what the filter does. In this case, it selects all rows from the Volcanoes table where Region has the value of NA or SA. Click Run to run the query.

query builder

Results
Here is the result of the query with just the volcanoes from North and South America. The new data table is given a name starting with the letters QUERY and is stored in a default location. You can change the location or name of the data table by clicking the Change button in the Query Builder window. This new data table is now ready for any analysis or report you want to create.

results

About the Authors
In 1992, Susan Slaughter and Lora Delwiche teamed up to write The Little SAS Book: A Primer, now in its third edition, which quickly became a popular item on SAS users' bookshelves. They have presented numerous papers at local, regional, and international SAS users group meetings. Their interactions with other SAS users have inspired them to write books that are both informative and enjoyable to use. Susan currently works as a consultant through her company, Avocet Solutions, while Lora works in research at the University of California, Davis.

Their books are available from the online bookstore.

These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.

Type:	Sample
Topic:	Non SAS Authors ==> Lora Delwiche Non SAS Authors ==> Susan Slaughter

Date Modified:	2007-01-13 03:03:23
Date Created:	2007-01-13 03:03:23

Product Family	Product	Host	SAS Release
Product Family	Product	Host	Starting	Ending
SAS System	Base SAS	All	9 TS M0	9.1 TS1M3
SAS System	SAS Enterprise Guide	All	9 TS M0	9.1 TS1M3

Support

Sample 26135: Filtering Data in SAS Enterprise Guide

Operating System and Release Information