Filtering Every Which Way in SAS Enterprise Guide

[#1]

Did you miss the Ask the Expert session on Filtering Every Which Way in SAS Enterprise Guide? Not to worry, you can catch it on-demand at your leisure.

Watch the webinar

By the end of this hour, you will know the secrets to exploiting key data management, integration and filtering capabilities of SAS Enterprise Guide. We’ll cover:

• Finding the tasks that conduct data management.
• Four ways to conduct filtering.
• Transposing data – restructuring observations and deriving new variables.
• Using the Query Builder’s advanced expression editor. 

 The data management tasks are primarily found under Tasks -> Data

Here are some questions and answers that were submitted during this webinar: 

­

­How can I run the query builder and return only the first 100 rows for example? Just to get quick results.

Click on Options in the Query Builder:

Then click on one or both of the ‘Limit number of’ options at the bottom of the results window:

 

­­Where does it tell you how many observations you have in this search?­

Touch on the right margin in the data viewer, or right mouse click on the data node in the process flow -> properties -> advanced

https://blogs.sas.com/content/sasdummy/2012/03/15/how-many-obs-sysnobs

­https://communities.sas.com/t5/Base-SAS-Programming/How-to-count-the-number-of-observations-in-a-dat...

­

Using WHERE in the data viewer, is there any way to filter for missing values?­

Yes, for character data, specify a blank ' '.  for numeric data, use  . in the expression­. If specifying . in the expression causes an error message, place quotes around the period.  Also, WHERE accepts IS MISSING. See highlighted areas below:

Data viewer without WHERE specified (all observations) – note the first row has missing data in the last variable, date:

Data viewer with WHERE [variable name] IS MISSING specified:

Data viewer after WHERE DATE IS MISSING is applied:

What is the best way to learn SQL? Do you have any suggestions or resources?­

Use Enterprise Guide’s query builder, then review its generated SQL. The Query Builder always generates SQL.

If you want to export into multiple Excel sheets, how would you do that?­

https://communities.sas.com/t5/SAS-Enterprise-Guide/Export-mulitple-data-sets-to-one-workbook-multip...­

­What is the easiest way to limit your results when filtering?  Example Top 20­.

The List Data task has several options in its Rows to List pull-down:

https://communities.sas.com/t5/SAS-Enterprise-Guide/Help-filtering-records-for-12-consecutive-months...­

­

­

 

Will the log show the syntax for the point and click operations performed so we can learn how to write/modify syntax ourselves?­

Yes – the log, preview code button on each task, and code tab of your outputs all reveal the generated code. More details: https://support.sas.com/resources/papers/proceedings14/SAS331-2014.pdf

­Enabling logging: http://support.sas.com/kb/55/414.html­

­I deal with very large files. Is there a size limit on how large a file can be for Query Builder?­

No – SAS is only bounded by the hardware where it’s installed and configured.

What if the customer made more than one purchase in a month – can you compute from the newest date of purchase­?

Yes - https://blogs.sas.com/content/sasdummy/2012/09/18/having-clause-fun-with-sas-enterprise-guide

Recommended Resources

Course: SAS Enterprise Guide 1: Querying and Reporting
Course: Creating Reports and Graphs with SAS Enterprise Guide
Book: The Little SAS® Enterprise Guide® Book

Want more tips? Be sure to subscribe to the Ask the Expert Community Library to receive follow up Q/A, slides and recordings from other SAS Ask the Expert webinars. From the Ask the Expert Library, just click Subscribe from the orange bar underneath the list of the recent articles.

NOTE: For best results when opening the attached slides, click on the “download” icon.