How to Easily Create Summary Tables with Proc Tabulate in SAS

In this comprehensive guide, we will explore the fundamental steps required to utilize PROC TABULATE through practical examples. We will demonstrate how to move from simple univariate summaries to complex multivariate reports, highlighting the flexibility and power inherent in this robust SAS tool.

Setting Up the Sample Dataset

To illustrate the functionalities of PROC TABULATE, we will utilize a sample dataset containing information about 12 basketball players. This dataset includes three key variables: the player’s team (`team`), their position (`position`), and the total points scored (`points`). The creation of this initial dataset is a prerequisite for running any subsequent tabulation procedures.

The following SAS code block demonstrates the necessary steps to construct and subsequently view the `my_data` dataset using the `DATA` and `PROC PRINT` steps, respectively. This setup ensures that all examples function correctly by establishing the necessary input foundation.

/*create dataset*/
data my_data;
    input team $ position $ points;
    datalines;
A Guard 15
A Guard 12
A Guard 29
A Forward 13
A Forward 9
A Forward 16
B Guard 25
B Guard 20
C Guard 34
C Forward 19
C Forward 3
C Forward 8
;
run;

/*view dataset*/
proc print data=my_data;

Upon successful execution of the code above, the resulting output table confirms the structure of the `my_data` dataset, which contains twelve observations ready for analysis.

Example 1: Generating Univariate Descriptive Statistics

Our first demonstration involves calculating basic descriptive statistics for a single quantitative variable: `points`. This is the simplest application of PROC TABULATE, where we specify the variable we wish to analyze and the specific statistical measures we want to display. This method provides a rapid overview of the central tendency, dispersion, and shape of the data distribution for the score totals.

The syntax below utilizes the `VAR` statement to identify `points` as the analysis variable. Crucially, the `TABLE` statement specifies the exact structure of the output. In this univariate case, we request a row summary of the `points` variable, cross-tabulated with a list of summary statistics enclosed in parentheses. The order of the statistics within the parentheses directly determines the column structure of the final report.

/*create table that displays descriptive stats for points variable*/
proc tabulate data=my_data;
    var points;
    table points * (N Min Q1 Median Mean Q3 Max);
run; 

Executing this code produces a concise table summarizing the key metrics for the `points` variable across all 12 observations in the dataset.

Understanding the Output Metrics

The selection of keywords within the parentheses in the `TABLE` statement dictates which descriptive statistics are calculated and displayed. These keywords are powerful tools for quickly grasping the distribution characteristics of the variable under study.

Below is a detailed breakdown of the most frequently used statistical keywords employed in `PROC TABULATE` for summarizing quantitative data:

• N: Represents the total number of non-missing observations used in the calculation. This provides context regarding the sample size.
• Min: Reports the minimum observed value in the data distribution.
• Q1: Refers to the first quantile, which is equivalent to the 25th percentile. This value indicates that 25% of the data points fall below this score.
• Median: Denotes the median value, which is the 50th percentile. It represents the midpoint of the distribution when data is ordered.
• Mean: Calculates the arithmetic mean, or average, of the observed values.
• Q3: Refers to the third quantile, equivalent to the 75th percentile. This value indicates that 75% of the data points fall below this score.
• Max: Reports the maximum observed value in the data distribution.

Analyzing the output from Example 1, we can interpret the summary statistics for the points scored:

• The total number of observations (N) analyzed is 12.
• The minimum score recorded is 3, and the maximum score is 34.
• The median number of points is 15.5, suggesting that half of the players scored 15.5 points or less.
• The arithmetic mean score is 16.92.
• The interquantile range (Q3 – Q1) is calculated using the 25th percentile (Q1 = 10.5) and the 75th percentile (Q3 = 22.5).

Example 2: Cross-Tabulation Using One Classification Variable

A more common application of SAS reporting involves subgroup analysis. In this second example, we extend the tabulation to calculate descriptive statistics for `points`, but this time grouped by the categorical variable `team`. This allows us to compare the scoring distributions across the different teams (A, B, and C).

To implement grouping in `PROC TABULATE`, we must introduce the `CLASS` statement. The `CLASS` statement specifies the categorical variable(s) used for defining the groups (in this case, `team`). The `TABLE` statement is then modified to include the classification variable first, followed by a comma (,) to separate the row dimension from the column dimension. The syntax `team, points * (statistics)` creates a row for each unique value in the `team` variable, with the requested statistics forming the columns.

/*create table that displays descriptive stats for points, grouped by team*/
proc tabulate data=my_data;
    class team;
    var points;
    table team, points * (N Min Q1 Median Mean Q3 Max);
run; 

The resulting table structure is significantly richer than the univariate example, clearly showing the requested statistics for each team separately. This powerful visualization helps in instantly identifying variations in performance metrics across different groups.

A quick inspection of the output reveals distinct differences between the teams. For example:

• Team A has 6 observations.
• The minimum value for points on Team A is 9.
• The number of points on Team A at the 25th percentile is 12.
• The median number of points on Team A is 14.

This allows for direct comparison of statistical distributions across the defined categories.

Example 3: Complex Grouping with Multiple Classification Variables

To produce highly granular reports, `PROC TABULATE` allows for grouping by multiple categorical variables simultaneously, creating nested or crossed tables. In this final example, we calculate the descriptive statistics for `points`, grouped first by `team` and then nested by `position` (Guard or Forward) within each team.

When utilizing multiple classification variables, both must be listed in the `CLASS` statement. The `TABLE` statement is then structured using the asterisk (*) operator to specify nesting. The syntax `team, position * points * (statistics)` defines the rows based on `team`, and then nests the `position` variable within the `team` rows. The statistics requested are calculated specifically for the intersection of team and position.

/*create table that shows descriptive stats for points, grouped by team and position*/
proc tabulate data=my_data;
    class team position;
    var points;
    table team, position * points * (N Min Q1 Median Mean Q3 Max);
run; 

This complex table structure provides the most detailed statistical summary yet, allowing analysts to compare, for example, the performance of Guards on Team A versus Guards on Team C. The use of the asterisk operator in the `TABLE` statement is fundamental to building these multi-dimensional tables, representing concatenation within dimensions.

Interpreting this output requires careful attention to the nested labels. The resulting table shows the mean and median scores for each unique Team/Position combination. Note that the cells for team “B” and position “Forward” are empty. This is because there were no players in the input data belonging to Team B who had a Forward position, demonstrating how `PROC TABULATE` naturally handles sparse data by reporting N=0 for those combinations.

Conclusion: The Power of PROC TABULATE

The `PROC TABULATE` procedure is an indispensable tool in the SAS programming environment, specifically engineered for the creation of elegant and statistically robust summary reports. Whether generating a simple count (N), calculating a median, or producing complex cross-tabulations involving multiple variables, `PROC TABULATE` provides the necessary control over statistical calculations and aesthetic presentation.

By mastering the use of the `VAR`, `CLASS`, and `TABLE` statements, users can quickly transform raw data into actionable insights, making it a cornerstone for statistical reporting and business intelligence within the SAS platform. We encourage further exploration into advanced formatting options, such as customizing labels and applying standard statistical formats, to enhance the professionalism of your summary tables.

For those looking to expand their SAS toolkit, reviewing additional tutorials on data manipulation, specifically handling missing values or calculating standard deviations and interquartile ranges, will further enhance your reporting capabilities.