BookmarkSubscribeRSS Feed

Filtering Every Which Way in SAS Enterprise Guide

Started ‎07-12-2017 by
Modified ‎07-18-2018 by
Views 8,704

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 -> DataThe 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:

options in qry bldr.png

 

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

 limit.png

 

­­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

propre.png

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:

EG1.png

 

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

EG2.png

 

Data viewer after WHERE DATE IS MISSING is applied:

EG3.png

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:

list data.png

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.

Version history
Last update:
‎07-18-2018 12:26 PM
Updated by:
Contributors

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Article Tags