Just-in-time data prep in SAS Enterprise Guide

This post was kindly contributed by The SAS Dummy - go there to comment and to read the full post.

On this blog, I’ve delivered a few tips about using SAS formats to stratify your data values “in place” without having to actually change your data. The most recent example addressed date and datetime variables.

My previous examples included simple SAS programs that you can run in SAS display manager or in SAS Enterprise Guide. But did you know that you can apply SAS formats in SAS Enterprise Guide without writing a lick of code? “Applying formats” is just one of the just-in-time data prep steps that you can perform in most SAS Enterprise Guide tasks, and no programming is required. I call these “just-in-time” because they don’t require a separate step to manipulate the data (as you might do using the Query Builder), and they are efficient in that they don’t create a separate physical copy of the data.

Here are the “just-in-time” data prep features offered within most SAS tasks:

Filter the input data

On the Data page within most tasks, the top section shows the currently selected data set and a “task filter”, if any. You can change the active data, or specify a filter (that is, a WHERE clause) by clicking the Edit button. The Edit Data and Filter window provides an intuitive method to add filter conditions, which you can combine with simple AND or OR operators. (If you need more sophisticated combinations including grouping/nested operators, then you have to go to the Query Builder task first.)

The filter is added to the SAS program that the task generates, and doesn’t create a copy of the data. In fact, if the active data resides in an DBMS, the filter is passed down to the database, thus reducing the amount of data that SAS has to move before performing the analysis or creating your report.

TIP: Do you want the filter to appear in your report as part of the title or footnote?  Simply use the &_CLIENTTASKFILTER macro variable within the text that you specify on the Titles page in the task.

Apply SAS formats to input columns

On the Data page for most tasks, you’ll find a common control that we call the “variable selector”.  The variable selector allows you to choose which columns from your input data to use for certain roles within the task.

Did you know that the variable selector also allows you to change attributes of those data columns within the task?  Simply right-click on a variable name, and choose Properties from the context menu.  The variable Properties window appears, and you can review the current variable attributes (length, format, informat, label, and more).

To change the variable’s format for just this task, click Change.  The Formats window appears, and from here you can select a different format to apply.  The Formats window shows all of the built-in formats, plus any user-defined formats that you might have access to.

Add/change column labels

If you change the format for a variable within a task, you probably will also want to change the label.  For example, if you change the format for a continuous Date variable to YYQ6. (thus grouping date values into yearly quarters), then you might change the label to “Quarter”.  To change the label, use the variable Properties window as described above to specify a new label.  Here’s an example report with the label, format, and filter in place:

 

tags: formats, in-database, SAS Enterprise Guide

This post was kindly contributed by The SAS Dummy - go there to comment and to read the full post.