Filtering DataYou 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.
Here are the filtering tasks:
Creating a Simple FilterHere is the Filter window for a user's COURSES SAS dataset. To create a filter to run against this dataset, follow these steps:
Adding Additional FiltersAfter 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:
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". Deleting a FilterTo delete a filter, either:
Changing the Filter OrderWhen 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:
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 FiltersIf 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:
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.
|