Best Practices for Filters

SAS Visual Data Builder provides the Where and Having tabs for filtering data. Follow these best practices:
  • Filter on the Where tab first because the WHERE clause reduces the rows to consider for further subsetting.
  • When you specify a filter on a column that is an index or primary key, avoid using a function whenever it is possible. Using a function, such as CAST(order_id as DOUBLE), risks performing a full-table scan rather than using the index.
  • In some cases, you can improve performance by filtering on the table that has the smallest number of rows first.
  • If you must remove a table that is used on the Where or Having tabs, then remove the reference to the table from the filters before you remove the table. Otherwise, if you remove a table that is referenced in a filter, then all of the filtering conditions are cleared.
  • When adding a subquery using the Libraries tab, an additional step is needed if the column names or table names are written in a language that uses a double-byte character set. After you finish adding the columns, you must manually enter an n-literal to the string on the Code tab for each column name or table name that uses a double-byte character set. For example:
    'table-name'n.'column-name'n
    For more information about editing code manually, see Customizing Code.