Filter your SAS Enterprise Guide data with a WHERE clause

20

Are you a VIEWTABLE fan from the SAS Windowing Environment (a.k.a. Display Manager, DMS, PC SAS)? If so, the latest version of SAS Enterprise Guide has a new feature that you'll love.

With the latest update to SAS Enterprise Guide 7.1 (7.11), you can now subset your data in the data grid by typing a WHERE clause filter. The new Where button appears at the top of the data grid.

Simply click the button, and type a filter expression into the text field. Hit the Enter key to apply the filter:

where_ex1
Just as you might expect from SAS, you can build more complex expressions too. For example, you can transform the variable that you're comparing to lower case so that case-insensitive filters are easier to build:

where_ex2
And if you have continuous variables such as a date variable, you can subset by using BETWEEN to specify the boundaries you want to see:

where_ex3
Or transform that continuous range to a category (such as a YEAR) with a SAS function, on the fly:

where_ex4

SAS Enterprise Guide has always allowed you to subset data easily, but only by adding a step in the process flow (either a Query Builder task or Filter and Sort task). Now, you can accomplish this "in place", saving time and storage as you explore. Let us know what you think of the new feature -- either here in the blog comments or within the SAS Enterprise Guide community.

Share

About Author

Chris Hemedinger

Senior Manager, SAS Online Communities

+Chris Hemedinger is the manager of SAS Online Communities. Since 1993, Chris has worked for SAS as an author, a software developer, an R&D manager and a consultant. Inexplicably, Chris is still coasting on the limited fame he earned as an author of SAS For Dummies.  He also hosts the SAS Tech Talk webcasts each year from SAS Global Forum, connecting viewers with smart people from SAS R&D and the impressive work that they do.

20 Comments

  1. Michelle Homes

    Very excited to see his feature. Missed it from DMS days...
    Thanks for sharing. I imagine this will please many SAS users who will be keen to get the latest version - SAS Admins beware! ;-)

  2. Like the "function"-ality
    Didn't know sas included LOWER() as well as LOWCASE()!

    A Great new facility - I look forward to being able to use it.
    is there any prospect of having the facility as an add-in for earlier releases of EG?

    • Chris Hemedinger
      Chris Hemedinger on

      Peter, it's unlikely that the team would back-port this to an earlier version. However, I know that some enterprising folks (like Phil Holland) have created custom tasks with similar capability. And I created a simple standalone example (independent of SAS Enterprise Guide) that has a similar feature.

  3. Bill Benton on

    I have SAS EG version 7.1 HF1 (7.100.0.2002) (64-bit) but I don't see the button.

    Is there an option that I have to check to show this "WHERE" button?

    • Chris Hemedinger
      Chris Hemedinger on

      Sorry Bill, "update 1" (v7.11) isn't the same as "Hotfix 1". You'll need to request and install the new version of EG; it's not a hotfix update. The version number will then show as "7.100.1.nnnn".

      v7.11 was just released earlier this week from SAS.

  4. Hi Chris,

    Does it work "exactly" like DMS, i.e. where, where also, where undo, where clear, stores the last 50 (???) where clauses, autocomplete based on the where clause history, etc, etc.?

    Anything that allows ***quick*** investigation of our data via drilldown is a welcome thing!!! As you said, we could always do this in past versions - this will just make it so much quicker.

    Finally, following on from your comment to @Bill Benton, can you go into more detail on how we download the install files? Do we submit a new order request via our Account Rep and/or Contracts, similar to a maintenance release?

    Regards,
    Scott

    • Chris Hemedinger
      Chris Hemedinger on

      Scott,

      It doesn't work exactly like VIEWTABLE in DMS. It does store recently used filters for the current session. But it isn't a full-on WHERE command like in DMS with UNDO, ALSO, etc (as far as I know).

      You can get the latest release via a new order request, yes -- similar to other maintenances. Keep in mind that SAS 9.4 Maint 3 is due out soon (in the next couple of months). Depending on how your IT organization works, you might want to "save up" goodwill and wait to get it all at once.

  5. I love the where clause filter from PC SAS, and am thrilled to hear the news! The where clause filter in PC SAS often helped me do debugging step by step quickly, and to detect outliers. Does this new where clause feature in EG also supply a list of all possibilities to choose from, without typing?

    • Chris Hemedinger
      Chris Hemedinger on

      Aileen,

      No, the new feature doesn't provide all of the possibilities (which are nearly infinite thanks to the combination of functions and operators). But of course you can use the Filter and Sort task or Query Builder task to generate a filter sequence that you like, then copy/paste that into the WHERE box in the data viewer.

  6. Hi Chris, I just updated to 7.11 HF2 32-bit, but the new WHERE button is greyed out and disabled. Any ideas? Is this a bug or collision with other versions (I have 5.x, 6.x also installed)?

    • Chris Hemedinger
      Chris Hemedinger on

      José, is this for input or output data? I know that there is a problem with output data results, where the WHERE= is available only for the first output data set in the list.

  7. You are right - it only happens on "output" datasets, where "output" means that running a piece of code renders a dataset that is linked to it in the Process Flow. If you delete that link in Process Flow and open the dataset directly, it is considered "input" and the WHERE command line works!

    Thanks for the tip.
    Regards,

  8. Daniel Boisvert on

    Is there a list of the syntax for the WHERE ? I tried WHERE substr(name,1,1)='A' and it threw an error; also where date GT '01JAN2016'D didn't work.

    • Chris Hemedinger
      Chris Hemedinger on

      Both of these work for me. Don't include the WHERE keyword in your filter. That is, use just:
      substr(name,1,1)='A'

  9. Pingback: Video: Demonstrating the new features in SAS Enterprise Guide 7.1 - The SAS Dummy

  10. how you check CHG= ., it doesn't recognize numeric blank, is ok with character blank, for example avalc= ' ', but not aval= ., the dot is not recognize, what I can use instead? thanks.

    • Chris Hemedinger
      Chris Hemedinger on

      Try the "IS MISSING" syntax (works with both). Example, "CHG is missing" (or "CHG not is missing" for the inverse). Or shorthand: simple specify "CHG" and it will show all non-missing, non-zero values.

  11. This functionality, although very useful for spot-checks, is bugged IMHO. If you apply a filter to the data you are viewing, close the dataset and the reopen it, the filter has still been applied but the TEXT BOX INDICATES THERE IS NO FILTER. Click on the X and the filter is removed.

    If a filter is being applied then this should be reflected in the UI, otherwise the user could be mislead into thinking their program has removed observations.

Leave A Reply

Back to Top