Filtering Data

You can specify filters to subset the data being returned by a query. By specifying filters, you create a set of conditions against which the data is compared. Only data that meets the specified conditions passes through the filter and is included in the results.

Filters that are applied at the record level are called Data Filters. Data Filters are the SQL WHERE clause. Filters that are applied at the group level are called Group Filters.  Group Filters are only available when the query returns aggregate values and compose the SQL HAVING clause. Both types of filters are created and manipulated in the same way.

To help you specify and understand filters, the Filter window graphically displays the filters you specify. The process starts at the source, which is the SAS dataset to which you are applying the filter. The data from the source passes through one or more filters, which are connected by pipes. The data that passes through all the filters and pipes is included in the output.

Filter Components

Here are the filtering tasks:

Creating a Simple Filter

Here is the Filter window for a user's COURSES SAS dataset. To create a filter to run against this dataset, follow these steps:

Filter window

  1. In the left-hand panel, double-click the name of a column whose values you want to filter. The Filter Edit window appears and allows the definition of one filter to be entered.

    Filter Edit window

  2. In the Filter Edit window, select an operator from the drop-down list. The list only displays valid operators for the selected column type.
  3. In the text box, enter a value the operator can compare against. To choose a value from a list of possible values for the column, click the Distinct Values button. If one of the values in the list is a quote ("), selecting the quote tells the the filter to look for data where the selected column is blank. (For example, CUSTID equal to " finds records that contain a blank for the CUSTID column.)

    In this example, our filter will only pass through rows which have a value of "ROCKVILLE, MD" for the LOCATION column.

    Filter Edit window

  4. If you only want rows that do not meet the specified criteria to through this filter, select the Not checkbox.
  5. If you want unknown values to be included, select the Include Unknown Values checkbox.

    If you want to further define the filter by editing it as a WHERE clause expression, click the Edit SQL button and type your changes. When switching to SQL mode the dialog is transformed to look like the Expression Builder .

  6. Click OK. The defined filter appears in the filter area of the dialog box.

    To modify a filter you have already defined, either double-click the filter or select the filter, click the right mouse button, and select Edit. The Filter Edit window appears and displays the filter's current definition, which you can then modify. However, if you have used the Edit SQL button to modify the filter as a WHERE clause expression, the Filter Edit window only allows you to view and edit the filter as an expression.

    Filter example 1

Adding Additional Filters

After you have defined a filter, you can add additional filters to further refine your queries. By default, new filters are added to existing filters with AND pipes, so a second filter operates only on the data that passes through the first filter. For information on changing the AND relationship to an OR relationship, see Changing filter order.

To add more filters, follow these steps:

  1. Select a filter by clicking on it. The new filter will be added after the filter you select.
  2. Double click on a column name whose data you want to filter, then define filter as before.

    The new filter is added after the selected filter.

  3. If you want to add additional filters, repeat the previous steps to select a location in the filter chain and define a new filter.

As an example, we have added a filter to the one created in Creating a Simple Filter to further refine our results. The first filter only selects rows whose LOCATION value is "Rockville, MD". The second filter selects rows from the results of the first filter whose SERVICE value is "Consulting".

Filter example 2

Deleting a Filter

To delete a filter, either:

  • Select the filter and press the Delete key
  • Select the filter, then click the right mouse button and select Delete from the pop-up menu.

Changing the Filter Order

When you add a filter, by default the new filter is added after the currently selected filter. However, you can change the positions of the filters and the order in which they are applied.

Moving a filter below another filter sets up an OR relationship between the two filters (data is included in the results if it passes through either one of the filters).

Moving a filter before another filter changes the order in which the data passes through the filters.

To change filter locations, follow these steps:

  1. Select the filter you want to move by clicking on it, then hold the mouse button down.
  2. Drag the filter to the new location. The pointer changes to an arrow with the outline of a box. When you point to a valid location to move the filter (below or to the left or right of an existing filter), an insertion mark appears to let you know you are at a valid location. Filter example 5
  3. When the insertion mark is at the location for the filter, release the mouse button. The filter appears in its new location.

    Filter example 6

In this example, we have changed the location of the filter we added in Adding Additional Filters. Before we moved the filter, rows were included in the results only if both filter conditions were matched. After moving the filter, rows are included in the results if either condition is matched.

If you add several filters, you may have unwanted connections between filters. To remove these connections, see Changing connections between filters.

Changing Connections Between Filters

If you add several filters to a filter chain, you may end up with unwanted connections between filters. These connections may change the logic of the filtering operation and prevent you from finding the data you are looking for.

To remove a connection between filters, follow these steps:

  1. Move the pointer over a connection you want to remove.

    The pointer changes to a pair of scissors and pipe turns red. If the pointer does not change and the pipe does not turn red, then the connection cannot be changed.

    Filter example 7

  2. To remove the connection, click the mouse button.

In this example, we have added a filter to pass courses which have a service date of August 17-19, 1998. What we are trying to find is all courses taught on a consulting basis or are taught in Rockville on August 17-19, 1998. However, as created, the filter actually finds all courses who are either taught as consulting or are taught in Rockville and which have a service date of August 17-19, 199.

By removing the pipe leaving the output of the SERVICE filter, we prevent that data from passing through the SERVDATE filter and achieve the results we want.