SAS Base Programming: The load that stopped at observation 4,502 and still reported success

The load that stopped at observation 4,502 and still reported success

A nightly job loads a 2.3-million-row pipe-delimited extract. Step 3 is:

data work.load; infile raw dlm='|' dsd truncover; input id $12. amount 12.2 dt :yymmdd10.; run;

Last night it stopped at observation 4,502. The log shows an invalid-data note for amount, then a message that the SAS System stopped processing the step. WORK.LOAD exists and contains 4,501 rows. Step 4 appended WORK.LOAD to the warehouse table, which is now 4,501 rows short of the source, and the scheduler still reported SUCCESS. The offending byte was a currency symbol in one amount field. There is no re-run window before the downstream report is due.

1. Why did the step stop there, and why does WORK.LOAD still exist with 4,501 rows instead of being deleted?

Read the worked answer

When the INPUT statement cannot convert the field to a number, SAS sets the automatic variable _ERROR_ to 1 and writes an invalid-data message. At the end of that iteration the DATA step terminates, because the default behaviour for an execution-time error is to stop processing rather than continue.

WORK.LOAD survives because the step compiled and began executing. The output dataset is created before the first iteration, one observation was written for each of the 4,501 good rows, and nothing rolls that back when the step aborts. Contrast this with a syntax/compile error - there the dataset is dropped at the end of the step, which is why "no dataset at all" is the usual symptom of the other class of failure. A partial dataset is therefore the more dangerous case: it is well-formed, just truncated, and nothing downstream can tell by looking at it.

2. Rewrite the input so one bad amount cannot stop the step, and so the bad rows can be inspected afterwards.

Read the worked answer

Read the raw token as character, convert it explicitly, and route rows to two outputs:

data work.load work.bad;
  infile raw dlm='|' dsd truncover lrecl=32767;
  length id $12 rawamt $24;
  input id $ rawamt $ dt :yymmdd10.;
  amount = input(rawamt, ?? best32.);
  if missing(amount) then output work.bad;
  else output work.load;
run;

Key choices:

?? on the INPUT function suppresses the message and prevents _ERROR_ from being set, so the step keeps running and amount becomes missing. Because no error is raised, the bad rows must be detected by testing the value - missing(amount) or notdigit(strip(rawamt)) - not by testing _ERROR_.
Reading into a character variable first also stops FLOWOVER from pulling the next line into the field; keep truncover and dsd so short or quoted records behave.
Two output datasets keep the good rows clean while preserving the raw text of the rejects for the vendor query.
If you must keep the numeric informat, the alternative is input ... amount ?? 12.2; plus a check, but you lose the original text.

3. The scheduler said SUCCESS on a job that lost 2.3 million minus 4,501 rows. What do you change so this cannot happen again?

Read the worked answer

The job reported success because SAS's process return code does not fail on this class of condition by default. An invalid-data error stops a step but SAS continues with the next step, finishes the program and exits normally. Fix it at three levels:

1. Make errors fatal.

options errorabend;   /* terminate and return a non-zero code on error */

This is the single highest-value change; it means a step that aborts cannot be followed by a successful publish.

2. Reconcile before publishing. Count the source with a pass-through step, then compare:

%if %eval(&src_rows - &load_rows) ne 0 %then %do;
  %put ERROR: row count mismatch &src_rows vs &load_rows;
  %abort cancel;
%end;

Also assert nmiss(amount) = 0 on the loaded data and that work.bad is empty.

3. Never append a partial dataset. Step 4 should run only after the checks pass; if the job must be resumable, load to a staging member and swap, rather than appending into the warehouse table.

Note that &SYSCC at the end of a program is the reliable signal; scanning the log for notes is not, because this step emitted a note and still exited cleanly.