Powerful Proc Tabulate Explained

In this blog, we will use PROC TABULATE , one of the most powerful PROCS for data summarization using SAS.

For creating summary table of the information (similar to Pivot in Excel), we need to define

  • Classification Variables, also called dimensions
  • Measurement Variables, also called Facts
  • Structure of the Output Summary Tables

PROC FREQ, PROC MEANS and PROC SQL could also be used for creating summary of measurement variables by classification variables, but each of these lacks some or other requirements.

Scenario: Spend Value by Category and Months

We have retailer data and we want to create a summary table which gives total $ spend for each of the categories and across different months.  Information on variables. In this scenario, we have almost ready to use data for the report development but in the real life you may have to aggregate data, combine information from various tables/datasets (e.g. category may be present in the other data table) and exclude some of the non required observations.

Tran Variable Desc

Data Sample

Business Requirement and Layout of Report

A report may have multiple dimensions and layout, so important to get clarity from stakeholders before preparing datasets and creating the reports. One of the layout you may want to create for the above requirement.

Report Layout

So, you need to define classification variables, measurement /analysis variables and which variable will be considered for rows (e.g. Category in the above example) and which variable(s) for the columns (Month in the above example). Also,  we need to select statistical measure such as total or average  for summarising the analysis variables (spend in the above case)

Now let's consider, how can we use PROC TABULATE for the above requirements?

Summary Tables using PROC TABULATE

As any other SAS Proc, PROC TABULATE start with PROC TABULATE and ends with RUN statements.

Since we want to define table dimension,we need to provide name of classification variable. And this is done using CLASS statement.  And table statement define the structure of the table. In the below example, a simple scenario of finding out summary statistics of Category variable which is a classification variable.

proc tabulate class statement

Since, Category is class variable, the default summary statistics is count. We can suppress "N" from the output using below code.

If we want to show count in a separate column and all category values as row, we can split "category" and N by comma. Comma split row and column for the output table. We have assigned label to N (which gives count) as well.

proc tabulate count as column

We may want to improve the layout. "Category" and "Personal Care" both seem to be linked to value 320.  We want to push "Category" in the box above and here is how it can be done. box="" option allow use to write in the box  and Category="" suppress the label for Category.

proc tabulate category label in box

Now, we want to add Month value as column. This means we have to add one more dimension. It would be defined in CLASS statement and used in TABLE statement.  But we have transaction date variable (Tran_date) , so we have to format into month values.

proc tabulate two dimension cross tab with counts

We can calculate % /Percent value for the counts.  pctn , rowpctn and colpctn are the options to get overall %, row % or column % respectively.

In the above table, it shows count of transactions by Month and Category. We want to find total spend for the combination. So, we have to use Spend value as analysis variables and find summary statistics as "sum".

Now we have all the main elements, we were looking in the output table. We need to improve the layout and visual of the output. Some of the points are:

  • Format Total Spend as $ spend
  • Give Label for Category and Trans_date
  • Give main title for the table
  • Show period or missing as 0 in the output

And here is the options to get all the above.

Proc tabulate summary table

You want want to explore some of the SAS PROCs

Leave a Comment