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:
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:
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:
Or transform that continuous range to a category (such as a YEAR) with a SAS function, on the fly:
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.
22 Comments
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! ;-)
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?
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.
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?
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.
Yes that's needed for us DMS folk.
Can we type in Metabrowse in there too? ;)
Sorry Pat, not quite yet. Don't hold your breath for that... BUT, the Metacoda custom tasks are quite nice for what they do!
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
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.
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?
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.
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)?
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.
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,
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.
Both of these work for me. Don't include the WHERE keyword in your filter. That is, use just:
substr(name,1,1)='A'Pingback: Video: Demonstrating the new features in SAS Enterprise Guide 7.1 - The SAS Dummy
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.
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.
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.
Chris, my where text box is not receiving any input when I type into it. I have Enterprise version 8.3, the latest, and am eager to use the where feature to subset the data as I've lived with this feature in the past with my original SAS for Windows vs 9.4.. Is there an extra setting that is not enabled? Does anyone else have this issue?
Mary, not sure what's going on here for you. It should work -- I use the feature all of the time. You might need to open a track with SAS Tech Support.