Skip to content
Tutorials Center
My learning

Topics

Lesson 34 of 37 ยท Statistics and Reporting

PROC REPORT and PROC TABULATE

Build presentation-quality tables with PROC REPORT (DEFINE, GROUP, ACROSS, COMPUTE, BREAK) and PROC TABULATE (CLASS, VAR, TABLE dimensions and statistics), with a runnable Python stand-in.

PROC PRINT lists rows and PROC MEANS prints a plain statistics table. For finished reports with grouped rows, subtotals, computed columns and colored cells, SAS has two tools: PROC REPORT (flexible, row-and-column design, programmable) and PROC TABULATE (compact multidimensional summary tables). They overlap, and each has fans.

SAS is not available on this site. The sas blocks are read-only and I do not print real SAS reports. The TryIts are Python stand-ins that produce the same shape of table from a small list, so you can understand the layout logic.

PROC REPORT: the basic idea

You describe each column with a define statement and give it a usage:

SAS
proc report data=work.sales nowd;
  column region product units amount;
  define region  / group "Region";
  define product / group "Product";
  define units   / analysis sum "Units" format=comma8.;
  define amount  / analysis sum "Revenue" format=dollar12.2;
run;
  • column lists the columns in display order.
  • group: rows with the same value collapse into one line.
  • analysis: a numeric column summarized with a statistic (sum by default; add mean, max, and so on).
  • display: show each row's value as is (the default for character columns without a define).
  • order: sort and show each value once; across: spread the values across the top as columns.
  • computed: a column calculated in a compute block.
  • nowd (no windowing) is old advice for running without the interactive window; modern SAS ignores it.

Across and spanning headers

SAS
proc report data=work.sales;
  column region product, amount;      /* comma nests: product over amount */
  define region  / group;
  define product / across "Product";
  define amount  / analysis sum format=dollar10.;
run;

This produces one row per region with a column for each product, a cross-tab of revenue. You can add a total with a second analysis column: column region product, amount amount;.

Break lines and computed columns

break and rbreak insert subtotal and grand-total lines:

SAS
proc report data=work.sales;
  column region amount share;
  define region / group;
  define amount / analysis sum;
  define share  / computed format=percent8.1 "Share";

  compute before;
    total = amount.sum;           /* grand total captured on the first pass */
  endcomp;

  compute share;
    share = amount.sum / total;
  endcomp;

  rbreak after / summarize;       /* grand total line */
run;
  • A compute block runs for each row as it is built; within it, an analysis column is referred to as name.statistic (amount.sum).
  • Report variables created in compute before must be re-read; the pattern above works but requires care with the order of evaluation. Columns to the right can use columns to the left.
  • break after region / summarize; adds a subtotal line after each region (it needs region to be an order or group variable).
  • call define(_col_, "style", "style=[background=lightred]"); inside a compute block colors cells conditionally: for example highlight shares under 10%.

Stand-in: group, sum and share

Python Stand-in: PROC REPORT with GROUP and a computed share

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

That table is what the group, analysis, computed and rbreak after / summarize pieces above would produce: rows collapsed by region, sums, a share column, and a grand total line.

PROC TABULATE

PROC TABULATE builds a table from a dimension expression in a table statement. class variables classify; var variables are analyzed.

SAS
proc tabulate data=work.sales format=comma10.;
  class region product;
  var amount units;
  table region all,                       /* rows */
        product all * (amount * (sum mean)) ;   /* columns */
run;

How to read a table statement:

  • Commas separate dimensions: page, row, column (with two commas you get page, row, column; with one you get row, column).
  • * crosses items (a variable with a statistic: amount * sum).
  • A space concatenates items side by side (region all).
  • Parentheses group items so an operator applies to all of them.
  • all adds a total.
  • pctn, pctsum, rowpctsum and colpctn give percentages; n, mean, sum, min, max, std work like in PROC MEANS.
SAS
proc tabulate data=work.sales;
  class region product;
  var amount;
  table (region all) * (n * f=5. amount * (sum mean) * f=dollar10.),
        product all
        / box="Sales";
  keylabel all="Total" sum="Sum" mean="Average";
run;

f= sets a format for a cell, box= writes text into the corner cell, and keylabel renames the statistic keywords. Use classlev to style row labels. In the output, the class variable values fill the row and column headings.

REPORT or TABULATE?

Need Choose
Detail rows with a few summary lines PROC REPORT
Computed columns, conditional colors PROC REPORT
Compact cross-tab of statistics, many dimensions PROC TABULATE
Percentages of totals in a grid PROC TABULATE
Quick check while exploring PROC FREQ / PROC MEANS

Both write through ODS, so the choice of destination (HTML, PDF, Excel) from ODS and Output Destinations applies equally to both. Style details go in a style= option on statements such as proc report ... style(header)=[background=lightgray].

Stand-in: a crossed table

Python Stand-in: TABLE region * product with totals

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The comma in a table statement separates row from column dimensions; all supplied the margin totals.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. In PROC REPORT, what does a GROUP column do?

    GROUP variables define the report rows; analysis columns are summarized within each group.
  2. 2. How do you refer to the sum of an analysis column called amount inside a COMPUTE block?

    Analysis columns are referenced as variable.statistic in compute blocks.
  3. 3. In a TABULATE table statement, what does the comma do?

    Commas separate dimensions; * crosses and a space concatenates.
  4. 4. Which PROC is usually better for a compact multidimensional table of statistics and percentages?

    TABULATE specializes in crossed classification tables with statistics and percentages.

Summary

  • PROC REPORT builds a table column by column with define (group, analysis, across, computed, order, display).
  • compute blocks, break/rbreak and call define add calculations, subtotals and conditional styling.
  • PROC TABULATE uses class, var and a table expression: comma separates dimensions, * crosses, space concatenates.
  • Both deliver through ODS to HTML, PDF and Excel.
  • Pick REPORT for detail-plus-summary layouts and TABULATE for statistic grids.

Next: Project: Sales Reporting Pipeline