SAS Base Programming: The merge that finished with no ERROR but doubled the paid amount

The merge that finished with no ERROR but doubled the paid amount

WORK.ELIG holds 432,000 rows - one row per member per month (36,000 members x 12 months) - sorted by member_id, with provider_name declared as length 12. WORK.CLAIMS holds 310,000 rows, up to nine per member, also sorted by member_id, with provider_name declared as length 30. A nightly job runs:

data work.recon; merge elig claims; by member_id; run;

then sums paidamt. The step completes with no ERROR, but total paidamt is about 1.85 times finance's figure, and the log carries a note about repeats of BY values. The scheduler marks the job successful. A separate audit step then runs data audit; set elig claims; run; and provider names in the output are cut off after 12 characters.

1. The MERGE ran to completion but the paidamt total is roughly 1.85x the expected figure. Explain what the DATA step actually did and where the extra money came from.

Read the worked answer

The DATA step MERGE is not a SQL join. For each BY group it reads one observation from every input in parallel and writes one output observation per step, taking as many steps as the longest input in that group. When a shorter input runs out, SAS keeps re-using its last observation, including its values, for the remaining steps.

Because ELIG is a monthly eligibility table, a member with 3 claims forms a BY group with 12 eligibility rows and 3 claim rows. The step takes 12 steps:

step	elig	claim	paidamt written
1-3	months 1-3	claims 1-3	real amounts
4-12	months 4-12	claim 3 (retained)	claim 3's amount, 9 more times

So the last claim of every low-claim member is counted up to 12 times. That is also exactly what the log note MERGE statement has more than one data set with repeats of BY values is warning about - a NOTE, not an ERROR, so &SYSERR stays 0 and the scheduler reports success.

The root cause is not the merge syntax; it is that member_id is not unique in ELIG. Any join, SQL or DATA step, will fan out until ELIG is reduced to one row per member.

2. Rewrite the step so paidamt reconciles, and say how you would have caught this before finance did.

Read the worked answer

Reduce ELIG to one row per member first, then treat CLAIMS as the driver:

proc sort data=elig out=elig_latest; by member_id descending month; run;
data elig_latest; set elig_latest; by member_id; if first.member_id; run;

proc sort data=claims; by member_id; run;

data work.recon;
  merge elig_latest(in=a) claims(in=b);
  by member_id;
  if b;            /* one row per claim */
  if not a then matched=0; else matched=1;
run;

A PROC SQL left join from CLAIMS to a de-duplicated ELIG subquery is equally valid, and a hash object is better if ELIG were the large table. The point is that exactly one side of the join must be unique on the key.

Detection, all of it cheap:

proc sort data=elig nodupkey out=_chk dupout=_dups; by member_id; run; - non-empty _dups means the key is not unique.
Compare count(*) and count(distinct member_id) in both inputs.
Reconcile the output row count and paidamt total against the source before the step is allowed to publish.
A NOTE never fails a job, so the reconciliation must be an explicit check, not a log review.

3. The audit file built with `set elig claims` loses the last 18 characters of provider_name. Why, and how do you prevent it?

Read the worked answer

In a DATA step, a variable's length in the program data vector is fixed by the first place the variable is encountered during compilation. elig appears first in the SET statement, so provider_name is compiled as length 12 and that is the length of the output variable; values arriving from claims at length 30 are silently truncated - no TRUNCATION note is produced for variables supplied by a SET statement.

Prevent it by declaring the length before the first reference:

data audit;
  length provider_name $30;
  set elig claims;
run;

Statement order matters: a LENGTH statement placed after the SET is too late. A KEEP/DROP or dataset option on the inputs does not change this. The same trap applies to a MERGE where two inputs declare different lengths for the same variable - check proc contents on the result rather than assuming the widest input wins.