Articles
SAS PROC REPORT Interview Questions: GROUP, ORDER, ACROSS, and Unexpected Totals
Practice SAS PROC REPORT interview questions by tracing GROUP, ORDER, ACROSS, formatted values, computed columns, and unexpected totals through original data.
SAS PROC REPORT interview questions are usually clearer when you ask what one output cell represents before explaining its value. GROUP, ORDER, and ACROSS are not interchangeable ways to make a report attractive. They affect whether observations combine, whether detail rows remain, and how categories become columns. A plausible-looking total can therefore be wrong even when every input value is correct.
This article walks through an original five-observation dataset, then traces computed columns and totals. For a related aggregation warm-up, try Write SQL Data Transformation Queries.
Evidence boundary: linked SAS documentation establishes procedure semantics. Candidate reports: none are used to predict an employer's questions. Worked examples and preparation guidance: original and labeled. Arithmetic was checked independently; the SAS examples are illustrative programs and output predictions, not claims of local SAS execution or a captured ODS report.
SAS PROC REPORT interview questions: define the report grain
A report's grain is the set of observations represented by one displayed row or cell. In an interview, identify that set before discussing sorting, formatting, or a statistic.
Official SAS semantics: GROUP consolidates observations using combinations of formatted group values. ORDER arranges detail observations, while ACROSS creates columns for category values. ANALYSIS computes a statistic over the observations represented by a report cell. SAS report layout
Preparation guidance: read the COLUMN statement from left to right, then annotate each item's DEFINE usage. Next, state the expected number of detail or grouped rows, the category columns, and the statistic. This gives you a concrete prediction to compare with the actual report.
Do not use “GROUP is like SQL GROUP BY” as the whole answer. That analogy leaves out formatted grouping, interactions with detail variables, and ACROSS expansion. The useful interview answer names which observations combine and which statement determines their output position.
Original data: five observations, four region-month combinations
Use this synthetic dataset for every exercise below. Each record represents one batch of service requests. attempts and successes are counts; they are not already-calculated percentages.
data requests;
input region $ month attempts successes;
datalines;
East 1 10 9
East 1 90 45
East 2 20 10
West 1 5 5
West 2 5 0
;
run;
The two East-month-1 observations are intentionally distinct batches with the same grouping keys. They are not duplicates to delete. Across all five records there are 130 attempts and 69 successes.
Original reconciliation question: if a report shows only four ordinary rows, which input records must have combined? The answer is the first two: the East-month-1 cell contains 100 attempts and 54 successes. If a report retains five detail rows, the first two still have their separate values, even if a repeated label is visually blank.
That distinction is the foundation for every later calculation. A blank repeated label is a display choice; it does not mean that the corresponding observation disappeared.
GROUP versus ORDER: predict the change before running it
The following original program requests a summary at region-month grain:
proc report data=requests nowd;
column region month attempts successes;
define region / group;
define month / group order=internal;
define attempts / analysis sum;
define successes / analysis sum;
run;
Expected numeric rows: East-1 has 100 attempts and 54 successes; East-2 has 20 and 10; West-1 has 5 and 5; West-2 has 5 and 0. Label repetition and styling can vary with the destination, but those contributing observations determine the sums.
Official SAS fact: grouping tries to consolidate observations into one row per group. An ORDER variable, or a DISPLAY variable without an associated statistic, prevents that consolidation in the documented nonwindowing situation. SAS REPORT concepts
Now change only define month / group to define month / order. Original prediction: the report retains the five detail observations rather than collapsing the two East-1 records. The sorted arrangement and suppressed repeated labels can still make it look grouped. That visual resemblance is why the input-to-output trace matters.
Does adding a transaction identifier help?
Suppose you add a unique batch ID as a DISPLAY column to help investigate the report. It can change the consolidation behavior you were trying to inspect. A diagnostic column is not automatically neutral.
Preparation inference: use a separate input listing when you need record-level evidence without changing the summary program. If a DISPLAY item caused the report to retain detail, explain that interaction before rewriting every GROUP statement.
Also distinguish the placement of DEFINE statements from the placement of items in COLUMN. The latter governs column order and the nesting of ordered items. SAS's simple-report examples show ordered detail records and repeated-label suppression. SAS simple reports
ACROSS: map categories to physical columns
Original exercise: put month values across the report and show both counts beneath each month:
proc report data=requests nowd;
column region month,(attempts successes) total_attempts;
define region / group;
define month / across order=internal;
define attempts / analysis sum;
define successes / analysis sum;
define total_attempts / computed;
compute total_attempts;
total_attempts=sum(_c2_,_c4_);
endcomp;
run;
For the stated input and unformatted numeric month values 1 and 2, annotate the expanded columns before interpreting the computed expression:
_c1_: region
_c2_: month 1 attempts
_c3_: month 1 successes
_c4_: month 2 attempts
_c5_: month 2 successes
_c6_: total_attempts
Original prediction: East has 100, 54, 20, 10, and total attempts 120. West has 5, 5, 5, 0, and total attempts 10. The computed sum deliberately uses the attempts columns, not the adjacent successes columns.
Official SAS reference rule: items beneath an ACROSS variable can require absolute column-number references. Nonprinted columns still count toward those positions. SAS's compute-block primer explains these references and analysis-variable compound names. SAS compute-block primer
Follow-up inference: insert a hidden item to the left or introduce another month category, and the positional assumptions may change. A hard-coded _c4_ means the fourth physical report column; it does not mean “February attempts” under every layout.
In a real report, either deliberately stabilize the category/layout contract or generate and verify the appropriate column mapping. An expression that happened to work for two observed categories is insufficient evidence that it supports all future categories.
Formats can change grouping, not just appearance
Official SAS fact: formatted values participate in grouping and ACROSS categories. SAS demonstrates a format that maps several departments into two categories, producing two ACROSS columns instead of four. SAS format-created groups
Original extension: map month values 1 and 2 to the same label, “First period,” and use that formatted category in the summary. Those categories can combine rather than remain separate columns. East's counts then represent both months: 120 attempts and 64 successes.
This is a different problem from formatting an analysis result to two decimal places. One affects membership in a category; the other affects how a numeric result appears. Say which operation your format is performing.
For ordering, state whether you want formatted values, internal values, or input order. The DEFINE documentation describes ordering options and defaults; labels that are alphabetically convenient may not express the intended numeric or business order. SAS DEFINE statement
Preparation guidance: when categories appear merged or oddly ordered, list raw values alongside formatted labels in a separate check. Inspect the mapping before concluding that observations were dropped. A format change should trigger a category-membership review as well as a visual review.
Unexpected totals: compute a ratio from its components
The original East-1 batches have success rates of 90% and 50%. Their unweighted average is 70%. But the combined batch rate is 54 / 100 = 54% because the second batch contains nine times as many attempts.
That arithmetic is an original worked example, not an assertion about a SAS default. A report must explicitly define whether its measure means average batch rate or overall request success rate. Both can be mathematically valid while answering different questions.
For the overall request rate, calculate the numerator and denominator at the current report grain:
proc report data=requests nowd;
column region attempts successes rate;
define region / group;
define attempts / analysis sum;
define successes / analysis sum;
define rate / computed format=percent8.2;
compute rate;
if attempts.sum > 0 then rate=successes.sum/attempts.sum;
else rate=.;
endcomp;
rbreak after / summarize;
run;
Original numeric predictions: East is 64 / 120, or approximately 53.33%; West is 5 / 10, or 50%; the overall rate is 69 / 130, approximately 53.08%. It is not the sum of regional percentages or their unweighted average.
Official SAS semantics: computed report items are calculated through their compute blocks. Summary lines use the appropriate statistics for their contributing observations and computed values from the corresponding blocks. SAS RBREAK statement
Preparation guidance: defend the denominator and zero-denominator rule before defending the decimal format. Here a zero denominator produces a missing result, not a fabricated 0% success rate. If the business definition calls for something else, make that rule explicit and test it separately.
Computed values have dependencies and positions
Official SAS fact: PROC REPORT assigns column values from left to right. A computed item cannot safely base its calculation on a report variable to its right at that point in processing. SAS COMPUTE statement
In the rate example, attempts and successes precede rate. Move rate to the left and you have changed the dependency order, not merely its presentation. A good interview response identifies that change before proposing a missing-value workaround.
An analysis item also needs the correct reference form in its compute context. In this example, attempts.sum refers to the aggregate statistic used by the report. Writing a plausible bare variable name can leave the computation referring to the wrong thing or an uninitialised value. Aliases and ACROSS-expanded items have their own reference requirements. SAS compute-block primer
Original check: for each computed cell, write its dependency names, physical positions when relevant, and contributing observation set. This small ledger catches a wrong category reference, a wrong statistic, and a wrong total grain through different evidence.
Debug a report with a reconciliation sequence
Preparation guidance: begin with the filtered input: record its observation count and independent sums. Then predict the groups using the actual formatted keys. Next, expand ACROSS columns and map their positions. Finally, check computed dependencies and summary rows.
For this fixture, the checkpoints are five input observations, four region-month combinations, two region summaries, 130 total attempts, and 69 total successes. If a visible total differs, locate the first checkpoint that fails.
Check the SAS log as well as the rendered table. A grouping note or uninitialised-variable message can explain a result that looks like an innocent formatting problem. Keep observations, ordinary report rows, subtotal rows, and grand-total rows distinct when reconciling an output dataset; adding them all together can count the same contributing observations repeatedly.
A useful final answer is: “This cell contains these two batches, uses SUM for these counts, and divides the resulting numerator by the resulting denominator.” That is more informative than saying that the report “groups correctly.”