Susan Slaughter

EG Tip: Pivot Tables

In Enterprise Guide, Everything, Little SAS Book Series, SAS, SPSS on March 25, 2010 at 9:08 am

Sometimes the folks at SAS Institute don’t brag as much as they could.

You won’t hear them boasting about how great the pivot tables are in SAS Enterprise Guide. In fact, if you search for the words “pivot table” in the Enterprise Guide online help, the only entry you will find is something about the OLAP Analyzer. That’s not what I’m talking about. I’m talking about the Summary Tables task. The Summary Tables task creates pivot tables, and it does a great job of it too.

According to Wikipedia “In data processing, a pivot table is a data summarization tool found in data visualization programs such as spreadsheets or business intelligence software. Among other functions, pivot-table tools can automatically sort, count, and total the data stored in one table or spreadsheet and create a second table (called a “pivot table”) displaying the summarized data. Pivot tables are also useful for quickly creating cross tabs. The user sets up and changes the summary’s structure by dragging and dropping fields graphically. This “rotation” or pivoting of the summary table gives the concept its name. The term pivot table is a generic phrase used by multiple vendors.”

SPSS creates pivot tables, and Microsoft Excel creates pivot tables. I’m not an expert on either of those software packages, but from what I’ve seen, there is no comparison between pivot tables in SPSS or Excel and in Enterprise Guide. The Summary Tables task is the clear winner for ease of use, power, and beauty.

Ease of use: A few years ago, Lora Delwiche and I presented a hands-on-workshop about Summary Tables at SAS Global Forum. The presentation was well-attended and well-received. However, when we wanted to repeat it the next year, the Section Chair asked us to present something “a little harder.” That’s right. Summary Tables was too easy to use for SAS Global Forum. It’s a shame too, in my opinion, because being easy to use is not the same as being obvious. When you open the Summary Tables task, it’s not obvious how powerful it is, how beautiful the output can be, or even how easy it is to use. But if someone shows you, then you can learn it all in a flash.

Power: The list of statistics you can produce in Summary Tables is long, to say the least. I think it’s safe to say that you would have a hard time matching it in some other software package. Not only that, but the flexibility you have in arranging your table is amazing. I know the folks at SAS Institute are working on some innovative new reporting features that will greatly exceed Summary Tables in flexibility, but when it comes to arranging summary data in a rectangular table, Summary Tables already does it all.

Beauty: Summary Tables gives you point-and-click access to the Output Delivery System. As anyone familiar with ODS knows, you can always control the overall style of SAS output, but Summary Tables does something special. With Summary Tables you can also control individual parts of the table. You can customize font, size, foreground and background color, centering, and much, much more. In addition, you are not limited to some proprietary format. After you have created your table, you can output it in HTML, PDF, RTF, or even as text output or a SAS data set.

So, if someone asks you whether SAS does pivot tables, the answer is a resounding, “Yes!”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: