SAS Base Programming: Running balance that resets, survives blanks, and produces an ending balance

Running balance that resets, survives blanks, and produces an ending balance

WORK.TRANSACT has 120,000 rows sorted by account_id and txn_date; WORK.OPENING has exactly one row per account with the opening balance. The analyst writes:

data run; set transact; by account_id; retain balance; balance = balance + amount; if balance lt 0 then od=1; run;

Every balance is missing. She changes the RETAIN to retain balance 0;. Balances now look plausible inside an account, but the first row of account 4471 shows 19,204.55 - the closing balance of the account before it - and in the 900 accounts that contain a reversal with a blank amount, balance goes missing from that transaction onward. She still needs the opening balance folded in and one ending-balance row per account.

1. Explain both missing-value behaviours, and give the idiom that fixes them without a RETAIN statement.

Read the worked answer

retain balance; with no initial value only tells SAS not to reset the variable each iteration. At N=1 the variable is still missing, so balance = balance + amount evaluates to missing on the first row and stays missing forever, because missing plus anything is missing.

retain balance 0; fixes the start value but not the blanks: a reversal with a blank amount makes balance + amount missing for that row, and the retained missing value propagates to every later row in the account.

The SUM statement does all three jobs - it retains automatically, initialises to 0, and treats missing as 0:

balance + amount;   /* equivalent to: retain balance 0; balance = sum(balance, amount); */

The explicit equivalent balance = sum(balance, amount); is equally correct because the SUM function ignores missing arguments. balance = balance + amount; never is.

2. Show the corrected DATA step that starts each account from its opening balance and flags overdrafts.

Read the worked answer

proc sort data=opening; by account_id; run;   /* one row per account */

data run;
  merge opening(in=o) transact;
  by account_id;
  if first.account_id then balance = opening_bal;   /* reset, not carry */
  balance + amount;                                 /* SUM statement */
  od = (balance < 0);                               /* fresh every row */
  if o=0 then put "WARN: no opening balance for " account_id=;
run;

Why each piece matters:

first.account_id is true on the first observation of each BY group; resetting there is the only correct place to stop the carry-over.
Opening the merge from opening works because OPENING is unique per account, so no fan-out occurs. Sorting both inputs by account_id is mandatory - otherwise the step errors with "BY variables are not properly sorted".
od = (balance < 0) assigns 0 or 1 on every row. if balance < 0 then od = 1; alone leaves stale or missing values, because a variable created by assignment is reset each iteration but never explicitly cleared.
Any account without an OPENING row would silently start from 0; the IN= flag and PUT make that visible.

3. She now wants one row per account holding the ending balance, and plans to end the step with `if last.account_id then output;`. What must be true for that to work, and what would break it?

Read the worked answer

It works only if the DATA step never skips the iteration before reaching that statement.

Correct: last.account_id is available because the step has a BY statement on sorted data; output writes the current program data vector, which at that moment holds the final balance, the last transaction's values and the accumulated od. No explicit OUTPUT elsewhere in the step, or the account gets extra rows.
Broken by a subsetting IF anywhere earlier. A statement such as if amount > 0; deletes the observation and terminates that iteration immediately, so for any account whose last transaction is a reversal the balance is never written. The same is true of an earlier explicit output.
Broken by an unsorted input, in which case there are no FIRST./LAST. variables at all and the step stops.

If filters are needed, apply them as dataset options on the input (set transact(where=(amount ne .));) or write the balance for every row and pick last.account_id in a later step, rather than mixing a subsetting IF with trailing output logic.