4.4 Accumulating Totals & Subtotals (RETAIN & Sum Statement)

4.4 Accumulating Totals & Subtotals (RETAIN & Sum Statement)

Quick Answer: By default, at the start of each DATA step iteration, SAS resets all non-dataset variables in the Program Data Vector (PDV) to missing (. for numeric, blank for character). To accumulate running totals or preserve values across rows, you must prevent SAS from re-initializing variables. You can achieve this using either an explicit RETAIN statement or an implicit Sum Statement (variable + expression;).

Default DATA Step Re-initialization

To understand accumulation, you must understand the PDV lifecycle:

1. Input Variables (read via SET, MERGE, MODIFY, or INPUT): Retain their values as new rows are read.
2. Created Variables (computed via assignment statements): Reset to missing at the top of every DATA step iteration loop.

/* FAILS TO ACCUMULATE: RunningTotal resets to missing at the start of every row! */
data work.bad_accumulator;
   set work.transactions;
   RunningTotal = RunningTotal + Amount; /* Always evaluates to missing (.) */
run;

In work.bad_accumulator, RunningTotal starts as missing (.). On row 1, . + Amount evaluates to missing. On row 2, SAS resets RunningTotal back to missing before executing the assignment, failing to accumulate.

The RETAIN Statement

The RETAIN statement is a compile-time directive that tells SAS not to set specified variables to missing at the beginning of each execution iteration. Instead, variables keep their previous values until new values are assigned.

Syntax

RETAIN variable-1 <initial-value-1> ... variable-n <initial-value-n>;

• Without Initial Value: Numeric variables initialize to numeric missing (.), and character variables initialize to blank (' ').
• With Initial Value: The variable is set to the specified constant before the first observation is processed.

data work.retained_total;
   set work.transactions;
   
   /* Preserve RunningTotal across rows, starting at 0 */
   retain RunningTotal 0;
   
   /* Explicit accumulator assignment using SUM function to ignore missing Amount values */
   RunningTotal = sum(RunningTotal, Amount);
run;

The SAS Sum Statement

SAS provides a special abbreviated syntax designed specifically for accumulation called the Sum Statement:

variable + expression;

CRITICAL EXAM CONCEPT: Notice there is no equal sign (=) in a Sum Statement! Total + Sales; is a Sum Statement, whereas Total = Total + Sales; is an assignment statement.

The Four Implicit Behaviors of a Sum Statement

When SAS compiles a Sum Statement (variable + expression;), it automatically applies four rules:

1. Implicit Creation: Creates variable as a numeric variable if it does not already exist.
2. Implicit Initial Value: Sets variable initial value to 0 before processing the first observation.
3. Implicit RETAIN: Retains variable across all DATA step iterations.
4. Missing Value Immunity: Adds the value of expression to variable, treating missing values in expression as 0 (ignoring missing values).

data work.sum_statement_demo;
   set work.transactions;
   
   /* Replaces RETAIN, assignment, and SUM function in one concise statement */
   RunningTotal + Amount;
run;

Comparison: Explicit Retention vs. Sum Statement

BY-Group Subtotal Accumulation (FIRST. and LAST.)

In business reporting, you often need to calculate running totals per category (e.g., total sales per Department or Customer). This requires combining BY group processing with accumulation logic.

Automatic Variables: FIRST.variable and LAST.variable

When a BY statement is included in a DATA step, SAS creates two temporary automatic numeric variables for each BY variable:

• FIRST.variable: Equals 1 on the first observation of a BY group, and 0 for all subsequent observations in that group.
• LAST.variable: Equals 1 on the last observation of a BY group, and 0 for all other observations in that group.

Note: Input datasets MUST be pre-sorted by the BY variable (using PROC SORT) before executing BY-group processing in a DATA step.

Subtotal Reset Pattern

/* Step 1: Pre-sort input data */
proc sort data=sashelp.shoes out=work.shoes_sorted;
   by Region;
run;

/* Step 2: Accumulate subtotals per Region */
data work.region_subtotals;
   set work.shoes_sorted;
   by Region;
   
   /* Reset accumulator on the FIRST row of each Region group */
   if First.Region then RegionSales = 0;
   
   /* Accumulate sales for rows within the Region */
   RegionSales + Sales;
   
   /* Subset: Output ONLY the LAST row of each Region group containing final subtotal */
   if Last.Region;
   
   keep Region RegionSales;
run;

Step-by-Step Execution Trace

1. When Region changes to 'Asia', First.Region is 1. SAS sets RegionSales = 0.
2. RegionSales + Sales; adds the current row's sales.
3. Intermediate rows (First.Region = 0, Last.Region = 0) accumulate sales without outputting because if Last.Region; filters them out.
4. On the final row of 'Asia', Last.Region is 1. The subsetting IF evaluates to true, and SAS outputs the summary record containing Region and the accumulated RegionSales subtotal.