Filtering and Sorting Data

In the table viewer, you can right-click a column heading to filter and sort the data by that column. You can sort the data in ascending or descending alphabetical order or display the columns in the order in which they appear in the data table.

Pop-up Menu for Sorting Data or Adding a Filter

You can also filter the data in order to display only rows that meet certain criteria, based on values in the data. The filter options vary depending on the type of column that you have selected and the number of distinct values that a column has.

The Add Filter window for a numeric column enables you to specify one or two filter criteria for each column. To add a second filter criterion, click Add to filter button

Example of the Add Filter Window with Entry Boxes for a Numeric Column

The Add Filter window for a character column is case sensitive and searches for values that contain the text that you enter.

Example of the Add Filter Window with Entry Boxes for a Character Column

When a numeric column has 10 or fewer distinct values or a character column has 30 or fewer distinct values, the Add Filter window displays a list of values to choose from. The list of values includes both the unformatted and formatted values. If no format has been applied to the data, then the unformatted and formatted values are the same.

Example of the Add Filter Window with a List of Numeric Values

Example of the Add Filter Window with a List of Character Values

Note: SAS Studio always uses the unformatted values in the filter expression.

When you create a filter on a numeric column by selecting values from a list, SAS Studio filters the data differently depending on whether any of the values in the column have a fractional component.

If the values are all integers, then SAS Studio creates the filter by using the equality operator. For example, suppose your data includes integer values between 12 and 17, and you want to create a filter for all values equal to 15. SAS Studio creates the following filter expression:

column-name = 15

If any of the values include a fractional component, then SAS Studio creates the filter by using a range of values to ensure that there are no rounding errors that might exclude the selected value. For example, suppose your data includes a value of 123.45678. If you select that value to use in a filter, SAS Studio creates the following filter expression:

(column-name >= 123.456775 and column-name < 123.456785)

Note: Generating a list of values for a filter can take a long time for large tables and Hadoop tables and can affect performance. If you are creating a filter on a very large table or on a Hadoop table, you cannot select the filter values. You must enter them in the value box. By default, if a table has more than 50,000 rows or the total number of rows is unknown, then you must enter filter values for it. The default value is controlled by a setting in the config.properties file. For more information, see SAS Studio: Administrator’s Guide.

The Add Filter window for a date column enables you to select a date value from a pop-up calendar.

Example of the Add Filter for a Date Column

When you create a filter on your data, the filter criteria are displayed at the top of the workspace. You can click Filter table rows button

to edit the filter and Clear filter button

to delete the filter. SAS Studio remembers any filters that you create each time you open the data.