Skip to content
Tutorials Center
My learning

Topics

Lesson 16 of 37 ยท Data Manipulation

RETAIN, the Sum Statement and Running Totals in SAS

Carry values from one observation to the next in SAS: the RETAIN statement, the sum statement (x + y), running totals, counters, LAG and DIF, and the common LAG-in-IF trap.

Normally the DATA step starts every iteration with a clean slate for the variables it created. Often you want the opposite: a running total, a previous value, the largest value so far. SAS has several tools for this. This lesson shows RETAIN, the compact sum statement and the LAG function, with the traps to avoid.

Why values disappear

As you saw in The Program Data Vector, a variable you create is set to missing at the start of each iteration. This step never builds a total:

SAS
data nothing;
   set sales;
   total = total + amount;   /* total is missing every time */
run;

Two statements fix it: retain and the sum statement.

RETAIN

RETAIN tells SAS not to reset a variable at the top of the loop. You can also give it an initial value:

SAS
data running;
   set sales;
   retain total 0;           /* start at 0, keep between rows */
   total = total + amount;
run;

Note that retain total 0; sets the starting value only once, before the first iteration; it does not reset to zero on each row. In this example, if amount is missing for a row, total + amount becomes missing, and the missing result is then retained, so every later total is missing too. That is why the sum statement is usually better.

You can retain several variables (retain a b c;) or give separate initial values (retain a 0 b 100;). RETAIN also forces the order of variables in the output, since it declares them at the point where it appears.

Variables read by SET, MERGE or INPUT from an existing data set are already retained across iterations in a sense (their values stay until the next read), which is why new variables can seem to behave "differently" from the original ones.

The sum statement

The sum statement has the form variable + expression; with no equals sign:

SAS
data running;
   set sales;
   total + amount;
run;

It does three things:

  1. The variable total is retained automatically.
  2. It is initialized to 0, not missing.
  3. A missing amount is treated as 0, so the total survives missing values.

This is almost always what you want for counters and totals. It is equivalent to retain total 0; total = sum(total, amount);.

SAS
data counts;
   set sashelp.class;
   n + 1;                  /* counter: 1, 2, 3, ... */
   if sex = "F" then females + 1;
run;

You cannot use the sum statement with expressions like total + amount * 2 without care for precedence, but total + (amount * 2); works. To subtract, add a negative: balance + (-withdrawal);.

Totals by group

To restart a total for each group, combine the sum statement with BY processing from Sorting and BY-Group Processing:

SAS
data by_dept;
   set sales;
   by dept;
   if first.dept then dept_total = 0;
   dept_total + amount;
   if last.dept then output;
run;

dept_total = 0; resets at the beginning of each group, and the sum statement continues retaining. If you want every row to show its running group total, omit the if last.dept then output;.

Largest value so far

Because max() ignores missing values, a running maximum is simple:

SAS
data highs;
   set daily;
   retain high_so_far;           /* starts missing */
   high_so_far = max(high_so_far, price);
run;

The first row compares missing and price, and max returns price. This pattern also finds the first non-missing value to carry forward: retain last_value; if not missing(x) then last_value = x; fills gaps with the last known value (last observation carried forward).

LAG and DIF: looking back

LAG(x) returns the value x had the previous time the function was called; DIF(x) is x - lag(x). Use them for changes between rows:

SAS
data changes;
   set prices;
   prev = lag(price);
   change = price - prev;      /* same as dif(price) */
run;

LAG works with a queue of values: lag2(x) looks back two rows. The first row has no previous value, so prev is missing.

The LAG trap. LAG remembers the value from the last time the function executed, not the last observation. If you call it inside an if or else, it runs only for some observations, and the queue only updates then. Always call it on every row in an unconditional statement, store it in a variable, and then use that variable in the condition.

SAS
data safe;
   set prices;
   prev = lag(price);               /* executed on every row */
   if sym = "ABC" then change = price - prev;
run;

If you need the previous value within a group use retain plus first., not lag. Reset the value at first.group.

Comparing the tools

Task Best tool
Running total or counter Sum statement total + x;
Carry a value forward retain with a condition
Previous row's value lag() called unconditionally
Reset per group by plus first.

A running-total model

This Python stand-in (not SAS) demonstrates the reset-on-each-row mistake, retain with a missing value, the sum statement, and lag.

Python Python stand-in: reset, RETAIN and the sum statement

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.

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does the sum statement 'total + amount;' do with a missing amount?

    The sum statement retains the variable, starts it at 0 and ignores missing values.
  2. 2. How many times does 'retain total 0;' set total to 0?

    The initial value is set once; reset explicitly with an assignment such as at first.group.
  3. 3. Why should lag() be called unconditionally?

    The lag queue is updated only when the function executes.
  4. 4. Which statement creates a counter n that starts at 0?

    The sum statement initializes to 0 and retains the variable.

Summary

  • Created variables reset to missing each iteration; retain keeps them, with an initial value set once.
  • The sum statement x + y; retains, starts at 0 and ignores missing values: use it for totals and counters.
  • Combine with by and first. to reset per group.
  • lag() and dif() look at earlier rows but must be called on every iteration.

Next: Arrays and DO Loops