Five North sections in one report
A monthly sales PDF must show one section per region with a subtotal per region. WORK.SALES has 22,400 rows; the region column was keyed by three different people, so it contains 'North', 'north', 'NORTH' and 'North ' (trailing blank). The programmer runs proc sort data=sales; by region; run; then:
proc report data=sales; column region product sales; define region / group; define product / group; break after region / summarize; run;
The PDF shows five separate North sections. Each section's subtotal is arithmetically correct and the row count in the report is 22,400, so nothing is lost or duplicated - it is only regrouped wrongly. No error or warning appears in the log.
1. Why does REPORT produce five North sections, and why is the trailing blank irrelevant to the problem?
Read the worked answer
Grouping is done on the stored value, and SAS character comparison is case-sensitive under the default collating sequence, so 'NORTH', 'North', 'North ' and 'north' are four distinct values. PROC SORT orders them; PROC REPORT then builds one group per distinct value and fires break after region at each boundary. Hence four North sections from casing alone - five if a leading blank or another variant exists in the data.
Trailing blanks are not the culprit: SAS compares character values after padding to equal length, so 'North' and 'North ' compare equal and fall in the same group. Stripping blanks will not change the output, which is why the fix has to address case (and any leading blanks or double spaces).
The log is silent because nothing is invalid - this is a data-standardisation defect, not a syntax error, so it will never trip &SYSERR or a NOTES check.
2. Give two ways to get a single North section, and recommend one.
Read the worked answer
Option A - standardise the value before it is used anywhere.
data sales_fix;
set sales;
region = upcase(strip(region));
run;
proc sort data=sales_fix; by region; run;
Option B - collapse at report time with a format.
proc format;
value $rgn
'North','NORTH','north' = 'North'
other = 'UNMAPPED';
run;
proc report data=sales;
column region product sales;
define region / group format=$rgn.;
...
run;
GROUP uses the formatted value, so the sections merge; other='UNMAPPED' surfaces any new spelling instead of hiding it.
Recommend A. The region column feeds other sorted BY-processing steps and joins, which Option B does not touch - those would still split into five groups and, if used as a BY variable in a merge, could produce wrong results. Standardise once, at load, and keep the format as a validation net rather than as the fix.
3. A colleague proposes adding `define region / group order=data;` after the sort. Would that help?
Read the worked answer
No. ORDER= controls the sequence in which groups are presented; it does not decide which values constitute a group. The values are still four distinct strings, so four groups remain - they may merely appear in arrival order rather than internal sort order, which can make the output look more scattered, not less.
The same applies to SORTSEQ=LINGUISTIC on PROC SORT: it changes collation order (case-insensitive ordering, possibly placing 'North' and 'north' adjacent), but adjacency is not identity. The two strings are still different values and still form separate groups, subtotals and break boundaries. Both suggestions change presentation; only changing the stored value (or the format used for grouping) changes the grouping.