8.5 Custom Tabular Reports with PROC REPORT & PROC TABULATE

8.5 Custom Tabular Reports with PROC REPORT & PROC TABULATE

When standard row listings from PROC PRINT or basic summary outputs from PROC MEANS do not satisfy complex business reporting requirements, Base SAS provides two sophisticated custom reporting procedures: PROC REPORT and PROC TABULATE. Both procedures can calculate summary statistics, format hierarchical matrix structures, and produce publication-ready reports across ODS destinations. Mastering their statements, usage types, dimension operators, and compute block rules is a key focus area on the SAS Certified Specialist Base Programming exam.

---

1. PROC REPORT Architecture & Mechanics

PROC REPORT combines the detail-listing capabilities of PROC PRINT with the statistical summary features of PROC MEANS and the formatting power of PROC TABULATE in a single unified interface.

Essential Statements in PROC REPORT:

• COLUMN variable-list;: A mandatory statement that specifies all variables and calculated items to include in the report, strictly defining their left-to-right visual column sequence.
• DEFINE variable / usage-type 'column-header' options;: Specifies how a particular variable is processed, its header label, formatting, and alignment.

proc report data=sashelp.shoes;
   column Region Product Sales Returns Profit;
   define Region / group "Sales Region";
   define Product / display "Product Line";
   define Sales / analysis sum format=dollar14.2 "Gross Sales";
run;

---

2. PROC REPORT DEFINE Usage Types

The usage type specified after the slash (/) in a DEFINE statement dictates how PROC REPORT evaluates and renders that column during report execution:

/* Demonstrating ACROSS vs GROUP */
proc report data=sashelp.shoes;
   column Region Product, (Sales Returns);
   define Region / group;
   define Product / across;
   define Sales / analysis sum format=dollar12.;
   define Returns / analysis sum format=dollar12.;
run;

Exam Tip - ORDER vs. GROUP: ORDER preserves every detail row in the dataset (it merely suppresses repeating text in column 1). GROUP collapses multiple detail rows into a single aggregated summary row!

---

3. Compute Blocks in PROC REPORT

PROC REPORT allows custom programming logic using COMPUTE ... ENDCOMPUTE blocks to calculate new column values, insert text lines, or format cells conditionally.

proc report data=sashelp.shoes;
   column Region Sales Returns NetSales;
   define Region / group;
   define Sales / analysis sum;
   define Returns / analysis sum;
   define NetSales / computed format=dollar14.2 "Net Sales";
   
   /* Compute block for target computed column */
   compute NetSales;
      NetSales = Sales.sum - Returns.sum;
   endcompute;
run;

Rules for Compute Blocks:

1. Referencing Variables: When referencing an ANALYSIS variable inside a compute block, specify both the variable name and statistic (e.g., Sales.sum or Returns.mean).
2. Execution Timing: Compute blocks execute during the report formatting phase after data grouping and statistic calculations are completed.
3. LINE Statement: Inserts custom text lines above or below summary breaks (e.g., compute after Region; line "End of Region"; endcompute;).

---

4. PROC TABULATE Architecture & Dimensions

PROC TABULATE constructs complex multi-dimensional tables (hierarchical tables with page, row, and column dimensions) using concise expression syntax.

Core Statements in PROC TABULATE:

• CLASS variable-list;: Identifies categorical variables used for grouping into row, column, or page headers.
• VAR variable-list;: Identifies numeric analysis variables to be summarized.
• TABLE <page-expression,> <row-expression,> column-expression / options;: Defines the structural layout and dimensions of the report table.

proc tabulate data=sashelp.shoes;
   class Region Product;
   var Sales;
   table Region, Product * Sales * mean;
run;

---

5. Dimension Syntax and Operators in PROC TABULATE

The TABLE statement uses specific punctuation operators to construct table dimensions:

1. Dimension Separator: The Comma (,)

Commas separate table dimensions in the TABLE statement. A table can have up to 3 dimensions:

• 1 Dimension: TABLE column-expression;
• 2 Dimensions: TABLE row-expression , column-expression;
• 3 Dimensions: TABLE page-expression , row-expression , column-expression;

2. Nesting Operator: The Asterisk (*)

The asterisk nests elements within a dimension, placing categories inside another category.

3. Concatenation Operator: Blank Space ( )

A space concatenates elements side-by-side within the same dimension.

4. Grouping Operator: Parentheses ()

Parentheses group elements to apply an operator (such as nesting or a statistic) across multiple variables simultaneously.

5. Summary Keyword: ALL

Adding ALL creates summary total rows, columns, or pages across categorical levels.

/* Advanced PROC TABULATE Example with Nesting, Concatenation, and ALL */
proc tabulate data=sashelp.shoes;
   class Region Product;
   var Sales Returns;
   
   /* Row dimension: Region concatenated with ALL */
   /* Column dimension: Product nested with Sales and Returns statistics */
   table Region all,
         Product * (Sales*sum Returns*mean)
         / box="Sales Analysis" misstext="N/A";
run;

• BOX="text": Customizes the top-left empty corner box of a 2-D table.
• MISSTEXT="text": Replaces blank missing value cells with custom text (e.g., N/A or 0).
• KEYLABEL Statement: Relabels default statistic keywords (e.g., keylabel sum="Total" mean="Average";).

---

6. Comprehensive Comparison of PROC REPORT & PROC TABULATE