Just to set the record straight, I’m a programmer who loves to write code. While SAS Enterprise Guide (EG) does make reporting easier, this time it’s not the displays that caught my eye. EG won this round hands down for ease of use to summarize data. This blog post will discuss three approaches to summarizing your data and offer you the best coding way out.

A student’s question prompted this post. Let’s call him “Mark” from Statcan asked "I took your EG1 course in March. Now my chief wants to analyze certain trends within survey data. I want a report with totals grouped across by EnterpriseNB for Related, Other and the Total column. I also want totals down for each of these columns to create a grand total row."

"My source data with rows and rows of detail data looks like this:"

"How can I get the report my chief is asking for?"

Firstly, I just want to let readers know that this blog post is focussed on ease-of-use. Detailed efficiency comparison is a whole other discussion. You’ll have to do some benchmarking since one size doesn’t fit all.

Let’s compare two approaches: SQL first
One way to approach the business problem is to submit SQL code. See below: the first SELECT creates summary statistics for Related, Other and Total by EnterpriseNB. The second query creates summary statistics down the columns. Stack the result sets of each query, one below the other with the UNION operator. Since I wanted to overlay the columns based on position, I used the CORR modifier. For more, visit SAS Help and documentation.

"HELP! Is there an easier way? I don’t want to keep coming back to you with coding questions."

Enter SAS Enterprise Guide with its GUI–that’s just an acronym for Graphical User Interface, the point and click way to SAS Heaven! And you don’t need to spend time writing code for standard tasks.

Take advantage of an existing procedure called PROC TABULATE where coding can take up to five hours even for experienced SAS programmers. Consider my solution, to save you a fair bit of time. I used the SUMMARY TABLES task in EG. Just bring data into EG and then choose from the task menu palette. My tool of choice was the SUMMARY TABLES task for the sheer amount of work it’s able to do in just a few clicks of your mouse.

The SUMMARY TABLES Wizard then guides you every step of the way. Here are a couple of screen captures. Step 2 of the wizard asks you to select the variables (columns) that you want summarized.

Here’s step 3 where the wizard asks how you want to group your data. I chose EnterpriseNB in the row position.

Stealing just became legal (just EG code for now)
And the grand finale! EG generates SAS code in the background letting SAS coders (who are out of practice with PROC TABULATE) take generated code and modify it to their heart’s content. How clever is that? Take a look.

I have to admit, I believe this one task alone would be sufficient to convert any dyed-in-the-wool SAS programmer to an EG fan. Tell me, how can you resist the irresistible EG with its charming displays? I know some expert SAS programmers (who shall remain nameless) who might prefer writing lengthy PROC TABULATE code. Knowing how much they love efficiency I’m certain they would want to at least try this solution. Write to me if just like Mark, you find that the SUMMARY TABLES WIZARD works beautifully! For more about this task plus other EG tasks check out our Enterprise Guide 1: Querying and Reporting course.