Claim Adjustments, Reversals, and Denials

What claim adjustments, reversals, and denials are — and why they are the most common silent data bug in claims research

When a hospital, pharmacy, or physician bills an insurer, the first transaction submitted is rarely the last. Payers reject claims for coding errors, authorization failures, or missing documentation. Providers then correct and resubmit. Pharmacies reverse and re-adjudicate fills when a patient never picks up a prescription. Each of these administrative events generates a new claim row in the underlying database, and the raw extract may therefore contain two, three, or more rows representing a single service event — some with positive amounts, some with negative amounts, and some with zero paid. An analyst who runs a naive `GROUP BY patient_id, service_date` sum over raw rows will silently double-count costs, overstate utilization, and inflate adherence estimates in ways that are invisible without deliberate QC profiling.

The four raw claim transaction types

Understanding the four categories is the prerequisite for correct deduplication:

(1) Original claim (institutional TOB frequency digit 1; pharmacy claim_type "B1" or "21"): The initial billing submission. In a well-curated research file this is what analysts intend to count — but raw files include originals that were subsequently voided or replaced.

(2) Void / full reversal (institutional TOB frequency digit 8; pharmacy claim_type "B2" reversal transaction): A transaction that cancels the original in its entirety. Institutional voids carry negative charge and payment amounts that exactly mirror the original. Pharmacy B2 reversals carry a zero or negative quantity and negative amounts. If both the original and the void are present and not resolved, they arithmetically cancel — but only if the analyst adds them, not if the analyst counts rows (utilization is double-counted even when costs net to zero).

(3) Replacement / adjustment claim (institutional TOB frequency digit 7): A corrected resubmission that supersedes the original. The replacement may change diagnosis codes, billed amounts, dates of service, or provider identifiers. A naively processed file that retains both the original and its replacement will double-count the claim. When a claim goes through multiple rounds of correction, the lineage is: original → replacement-1 → replacement-2, and only the terminal replacement is the authoritative record.

(4) Denied claim (any claim type, paid_amount = 0, with a claim adjustment reason code such as CO-4 "service inconsistent with modifier" or CO-97 "payment is included in another service"): The payer adjudicated the claim and refused payment. The service may or may not have actually occurred — a denial on a facility claim for a hospital stay that genuinely happened (but was denied for lack of authorization) is informative for utilization but contributes nothing to the cost numerator.

Institutional claims: the TOB frequency digit and pre-processing variability

For institutional claims (UB-04/837I), the Type of Bill (FL4) frequency digit is the key deduplication signal. Frequency 1 = admit-through-discharge (the normal complete bill); 2, 3, 4 = interim bills for long stays (drop before counting admissions); 7 = replacement of a prior claim (keep as the authoritative version, discard all earlier versions for the same claim control number); 8 = void/cancel (drop the void and its predecessor).

The critical vendor question is whether the research database has already applied this logic. MedPAR (Medicare inpatient) is pre-processed to the admit-through-discharge level and generally does not contain voids or replacements as separate rows. Medicare Outpatient Standard Analytical Files (SAF), commercial databases (MarketScan, Optum), and raw FFS claims from CMS do contain raw adjustment transactions and require analyst-side deduplication. Applying a second round of deduplication to a pre-processed file (e.g., MedPAR) that has already resolved replacements can incorrectly remove valid records — always read the vendor data dictionary before deduplicating.

Pharmacy claims: the NCPDP B2 reversal transaction and phantom fills

In the NCPDP pharmacy billing ecosystem, the transaction type code "B2" (or service type code "B2" in some payer systems) denotes a reversal of a previously adjudicated fill. A same-day B2 reversal for the same person, NDC, and pharmacy as a prior B1 fill means the patient never picked up the prescription — the pharmacist adjudicated the claim, then reversed it when the patient did not return. These are phantom fills that inflate medication adherence metrics if not removed.

The phantom-fill adherence inflation pathway: if a research extract captures the original fill (B1) but the reversal (B2) arrives in a different claim batch or data period and is not linked and netted, the fill appears as dispensed and contributes days_supply to the PDC denominator or numerator. A patient coded as adherent (PDC ≥ 0.80) because of phantom fills that were never actually dispensed represents a measurement error that cannot be detected without checking for unmatched B2 reversals. The rule: match each B2 to its parent B1 on (person_id, NDC, pharmacy_id, fill_date) and remove both; any unmatched B2 (reversals without a same-batch original) should also be removed.

Denied claims: the estimand-dependent decision

Whether a denied claim should count toward the numerator of a research variable is not a data-cleaning question — it is an estimand question that must be pre-specified:

• For cost estimands (PPPM, PPPY, budget impact): a denied claim contributes zero dollars. Including denied claims in cost sums adds phantom spend. Net all claims by paid_amount = 0 exclusion for cost outcomes.
• For utilization estimands (did the patient attempt to access this service?): a denied claim may legitimately count as an encounter — the patient sought care, the provider submitted a bill, and the service may have occurred even if the payer refused payment. Emergency department denied claims are the paradigmatic example: denial for an out-of- network ED visit does not mean the visit did not happen.
• For exposure phenotypes (did the patient receive this drug or procedure?): pharmacy denials typically mean the fill was rejected at point-of-sale and the drug was not dispensed, so denied pharmacy claims should generally be excluded from exposure counts. Institutional denials for surgical procedures are ambiguous — the surgery may still have been performed if the denial was for a documentation issue resolved post-service.

The safest approach is to build two analytic flags on each claim: `final_action_flag` (excluding voids, retaining only terminal replacements) and `paid_flag` (paid_amount > 0), and let the estimand definition determine which filter applies to each variable.

Final-action logic: deduplication by claim ID lineage

The canonical algorithm for resolving a raw claims file to its final-action state:

Step 1 — Identify and remove void claims (TOB freq = 8; pharmacy B2). For institutional voids, also remove the predecessor original they reference (tracked via claim control number or ICN). For pharmacy, match and remove both the B2 and its parent B1.

Step 2 — For replacement chains (TOB freq = 7 or equivalent commercial claim adjustment codes), keep only the terminal replacement for each claim control number. Drop all earlier originals and intermediate replacements. If the vendor uses a separate "original_claim_id" cross-reference field, join on that field rather than parsing the claim_id string.

Step 3 — Drop interim bills (TOB freq = 2, 3, 4) for institutional claims; these are sub-period bills for long stays subsumed by the final admit-through-discharge claim.

Step 4 — Retain denied claims as a separate analytic flag rather than deleting them outright; the estimand determines whether they contribute to the variable.

Negative-value rows breaking naive sums

A void claim with a -$10,000 allowed amount will arithmetically cancel the original $10,000 if both are included in a GROUP BY sum. But three failure modes remain even when costs net to zero:

1. row counts still double, inflating utilization;
2. if the replacement ($11,000) is also present but is not linked to the void, the net sum is $1,000 not $11,000 — a cost undercount;
3. if the replacement is present but the void is not (partial extract or lag), the naive sum is $21,000 — a cost overcount. Negative amounts in any numeric aggregation on claims are a diagnostic signal that final-action processing has not been applied. QC step: check for any sum(allowed_amount) < 0 at the patient-service level before proceeding.

Vendor differences: pre-processed vs raw files

| Database | Pre-processed to final action? | Notes | |---|---|---| | Medicare MedPAR | Yes (inpatient) | Deduplication applied; voids and replacements resolved | | Medicare Outpatient SAF | Partial | Claim status field (CLM_QUERY_CODE) identifies final action; analyst must filter | | Optum / MarketScan commercial | Partial | Vendor-specific; read the data dictionary; some files include a "claim_status" or "pay_status" field | | Raw CMS 5% sample / 100% Medicare FFS | No | Full adjustment history present; analyst must apply frequency-digit deduplication | | Medicaid T-MSIS/MAX | Highly variable | State-specific preprocessing; treat as raw until verified |

QC recipes

Run these checks before any aggregation on a new claims extract:

• Count rows with paid_amount < 0 or allowed_amount < 0 by year and claim type — any negative amounts indicate unresolved voids or reversals.
• Compute the reversal rate (void/reversal claims ÷ total claims) by file-year — a sudden increase signals extract-period lag where reversals from the prior period arrived late.
• For pharmacy files: count B2 (or reversal claim_type) rows as a fraction of all fills by NDC or drug class — high B2 rates (> 5%) in a drug class may indicate formulary barriers or patient non-pickup that inflates apparent dispensing volume.
• Join claim_id to original_claim_id and count orphaned replacements (replacements without a matching original in the extract) — these indicate claim receipt lag and should be treated as the authoritative record for that service.
• For pharmacy adherence metrics: compare PDC computed before and after B2 removal — a material difference (> 2 percentage points) indicates phantom-fill inflation that requires correction.

Pros, cons, and trade-offs

Applying final-action logic (pros): eliminates double-counted costs and utilization; produces a defensible analytic file with one row per service event; enables correct cost sums and adherence metrics; is the standard expected in regulatory and HTA submissions.

Applying final-action logic (cons): requires knowledge of the vendor's preprocessing state — over-deduplicating a pre-processed file removes valid records; requires a cross-reference field (claim control number, ICN, or original_claim_id) that is not always populated in commercial databases; pharmacy B2 matching requires same-batch or multi-period linking that can be complex in large extracts.

Not applying final-action logic (the silent-bug scenario): costs may be double-counted (original + replacement) or understated (void without replacement resolved); utilization counts are inflated; adherence metrics are inflated by phantom fills; and none of these errors are visible in the final analytic table without deliberate QC.

Denied claims trade-off: including denied claims in cost analyses overstates spend; excluding them from utilization analyses misses genuine service attempts. The choice must be pre-specified in the SAP with estimand justification.

When NOT to use — and when skipping this step is actively misleading

• Do not re-deduplicate MedPAR or other pre-processed files: applying frequency-digit logic to a file that has already resolved adjustment chains will incorrectly remove valid admit-through-discharge claims whose claim_id ends in "R1" or similar suffixes from prior replacement rounds. Always confirm preprocessing state before applying deduplication.
• Do not assume all negative-amount rows are voids: some commercial vendors encode coordination-of-benefits (COB) adjustments as negative lines on the same claim; these are not voids and should not be dropped. Read the vendor data dictionary.
• Do not exclude all denied claims from utilization without estimand justification: for ED visits, denied claims represent real encounters. Excluding them from the utilization numerator will undercount ED utilization in analyses of high-cost patients.
• Do not apply pharmacy B2 removal without matching to the parent B1: removing B2 rows without also removing the matched B1 leaves an orphaned "dispensed" fill in the data — the opposite of the intended correction. The pair must be removed together.
• Do not skip this step for "small" analyses: the error is not proportional to sample size. A single high-cost original + replacement pair (e.g., a $10,000 original and an $11,000 replacement) inflates that patient's cost by $10,000 regardless of cohort size. In a 50-patient pilot, this error could shift the mean PPPM by hundreds of dollars.

Interpreting the output

The canonical QC output of final-action processing is a reconciliation table: raw row count, rows removed by category (voids, interim bills, superseded originals, denials flagged), and final-action row count with cost totals.

For the worked example (five raw rows for one admission with allowed amounts of $10,000, -$10,000, $11,000, $2,000, and $0), the reconciliation produces: naive sum = 13,000 vs final-action allowed = 11,000 — a $2,000 discrepancy.

(1) Formal interpretation. The naive sum of the `allowed_amt` column across all five raw rows is $13,000. This figure is arithmetically incorrect as a cost measure because it retains the original claim ($10,000) alongside its replacement ($11,000), does not net the void (-$10,000), and includes the denied line at billed face value ($2,000) despite zero reimbursement. After final-action deduplication: the void removes the original (net: 0); the replacement is the authoritative cost record ($11,000); the denied line is excluded from the cost numerator (paid_amount = 0). The correct cost numerator is $11,000. The denied $2,000 line may count as a utilization event depending on the pre-specified estimand but contributes nothing to cost.

(2) Practical interpretation. For a payer or HEOR analyst, the $2,000 gap between the naive sum ($13,000) and the final-action allowed amount ($11,000) represents a 15% overstatement of this patient's costs — a systematic error that scales across a full cohort and produces inflated PPPM rates, biased incremental cost estimates, and budget-impact models that overstate the financial burden. The denied-claim decision (include in utilization, exclude from cost) must be documented in the SAP before unblinding results, because changing it post-hoc constitutes selective outcome reporting.

Decision diagram

flowchart TD
  Raw[Raw claims extract<br/>originals + voids + replacements + denials] --> QC1{Any allowed_amt < 0?}
  QC1 -->|Yes| Void[Remove voids and matched originals<br/>TOB freq = 8 for institutional<br/>B2 for pharmacy]
  QC1 -->|No| Interim
  Void --> Interim[Drop interim bills<br/>TOB freq 2 / 3 / 4]
  Interim --> Repl[Keep terminal replacement only<br/>TOB freq = 7<br/>discard superseded originals]
  Repl --> Denied{Denied claims<br/>paid_amt = 0?}
  Denied -->|Cost estimand| ExcludeDenied[Exclude from cost sum<br/>paid_amt = 0 contributes nothing]
  Denied -->|Utilization estimand| IncludeDenied[Include as encounter attempt<br/>estimand decision, document in SAP]
  ExcludeDenied --> FinalCost[Final-action cost numerator]
  IncludeDenied --> FinalUtil[Final-action utilization count]
  FinalCost --> PPPM[PPPM / PPPY calculation<br/>see healthcare-costs-pppm-pppy-pmpm]

Worked example

Scenario

A researcher is computing total allowed costs for a single inpatient admission (patient 2042) to use in a PPPM analysis. She pulls the raw institutional claims extract and finds five rows for this admission. Before summing the allowed amounts, she needs to identify which rows represent the final authoritative cost record, which are administrative corrections that should be removed, and how to handle the one denied line when building cost versus utilization variables.

Dataset

Five raw claim rows for patient 2042, one inpatient admission, as they appear in the raw extract before final-action deduplication. allowed_amt is the negotiated allowed amount; denial_code is blank when the claim was paid.

Steps

Result

Naive sum of raw allowed_amt = 10000 - 10000 + 11000 + 2000 + 0 = 13000. Final-action allowed (cost numerator) = 11000 (replacement only; void negates original; denied line excluded from cost). Overstatement if naive sum used = 13000 - 11000 = 2000. Utilization estimate: 1 inpatient admission (the replacement episode) + 1 potential outpatient service attempt (the denied OP-2023-002 claim), subject to estimand.

Trade-offs

Runnable example

import pandas as pd

# ── 1. INSTITUTIONAL CLAIMS FINAL-ACTION DEDUPLICATION ──────────────────────────────────
#
#  Input DataFrame columns:
#    claim_id          : str  (unique row ID; replacement rows may share a prefix with original)
#    original_claim_id : str  (None/NaN for originals; filled for voids and replacements)
#    tob_freq          : str  ('1'=final, '2'/'3'/'4'=interim, '7'=replacement, '8'=void)
#    allowed_amt       : float
#    paid_amt          : float
#    denial_code       : str  (empty/None when paid)
#
#  Returns: deduplicated DataFrame with one row per final-action claim.

def deduplicate_institutional_claims(df: pd.DataFrame) -> pd.DataFrame:
    df = df.copy()
    df["tob_freq"] = df["tob_freq"].astype(str).str.strip()

    # QC: report negative amounts before any action (should only be voids)
    neg = df[df["allowed_amt"] < 0]
    if not neg.empty:
        print(f"QC: {len(neg)} rows with negative allowed_amt (expected = void rows only)")

    # Step 1: Identify void claims (freq=8) and their predecessors.
    void_original_ids = set(
        df.loc[df["tob_freq"] == "8", "original_claim_id"].dropna()
    )
    # Remove void rows and the originals they cancel
    df = df[~df["tob_freq"].isin(["8"])]
    df = df[~df["claim_id"].isin(void_original_ids)]

    # Step 2: For replacement chains (freq=7), keep only the terminal replacement.
    # Group by original_claim_id; keep the replacement row, drop superseded originals.
    replacement_ids = set(
        df.loc[df["tob_freq"] == "7", "original_claim_id"].dropna()
    )
    df = df[~(
        df["claim_id"].isin(replacement_ids) & ~df["tob_freq"].isin(["7"])
    )]

    # Step 3: Drop interim bills (freq 2, 3, 4) — subsumed by the final bill.
    df = df[~df["tob_freq"].isin(["2", "3", "4"])]

    print(f"QC: {len(df)} final-action rows after deduplication.")
    return df.reset_index(drop=True)


# ── 2. PHARMACY B2 REVERSAL REMOVAL ────────────────────────────────────────────────────
#
#  Input DataFrame columns:
#    person_id       : str/int
#    ndc             : str  (11-digit NDC)
#    pharmacy_id     : str
#    fill_date       : datetime
#    days_supply     : int
#    claim_type      : str  ('B1' = dispensed fill; 'B2' = reversal)
#    allowed_amt     : float
#
#  A B2 reversal on the same (person_id, ndc, pharmacy_id, fill_date) cancels the B1.
#  Both are removed. Unmatched B2s (no matching B1) are also removed — they represent
#  reversals from prior extract periods.

def remove_pharmacy_b2_reversals(rx: pd.DataFrame) -> pd.DataFrame:
    rx = rx.copy()
    key = ["person_id", "ndc", "pharmacy_id", "fill_date"]

    b2 = rx[rx["claim_type"] == "B2"].set_index(key).index
    b1_matched = rx[
        (rx["claim_type"] == "B1") & rx.set_index(key).index.isin(b2)
    ].index

    n_b2 = (rx["claim_type"] == "B2").sum()
    n_matched_b1 = len(b1_matched)
    print(
        f"QC: {n_b2} B2 reversal rows; {n_matched_b1} matched B1 fills removed "
        f"as phantom fills. Reversal rate = {n_b2 / max(len(rx), 1):.1%}"
    )

    # Remove all B2 rows and matched B1 rows
    b2_idx = rx[rx["claim_type"] == "B2"].index
    remove_idx = b2_idx.union(b1_matched)
    rx = rx.drop(index=remove_idx)
    return rx.reset_index(drop=True)


# ── 3. DENIED CLAIM FLAG (estimand-dependent inclusion/exclusion) ──────────────────────
#
#  Adds a boolean column so downstream code can apply its own filter:
#    final_paid = True  -> claim was reimbursed (include in COST numerators)
#    final_paid = False -> denied claim (include in UTILIZATION numerators if warranted)

def flag_denied_claims(df: pd.DataFrame) -> pd.DataFrame:
    df = df.copy()
    df["final_paid"] = df["paid_amt"] > 0
    n_denied = (~df["final_paid"]).sum()
    print(f"QC: {n_denied} denied claim rows flagged (paid_amt = 0). "
          f"Exclude from cost sums; apply estimand rule for utilization counts.")
    return df


# ── EXAMPLE USAGE: worked-example dataset ─────────────────────────────────────────────

if __name__ == "__main__":
    raw = pd.DataFrame({
        "claim_id":          ["IP-2023-001", "IP-2023-001-V", "IP-2023-001-R",
                               "OP-2023-002", "IP-2023-001-LC"],
        "original_claim_id": [None, "IP-2023-001", "IP-2023-001", None, "IP-2023-001"],
        "tob_freq":          ["1", "8", "7", "1", "1"],
        "allowed_amt":       [10000.0, -10000.0, 11000.0, 2000.0, 0.0],
        "paid_amt":          [8500.0, -8500.0, 9350.0, 0.0, 0.0],
        "denial_code":       ["", "", "", "CO-4", ""],
    })

    print(f"Naive sum of allowed_amt: {raw['allowed_amt'].sum():.0f}")  # 13000
    clean = deduplicate_institutional_claims(raw)
    clean = flag_denied_claims(clean)
    cost_sum = clean.loc[clean["final_paid"], "allowed_amt"].sum()
    print(f"Final-action cost numerator (paid claims only): {cost_sum:.0f}")  # 11000

library(data.table)

# ── 1. INSTITUTIONAL CLAIMS FINAL-ACTION DEDUPLICATION ──────────────────────────────────

deduplicate_institutional_claims <- function(dt) {
  dt <- copy(dt)
  dt[, tob_freq := as.character(tob_freq)]

  # QC: count negative allowed amounts (expected only for voids)
  n_neg <- dt[allowed_amt < 0, .N]
  cat(sprintf("QC: %d rows with negative allowed_amt (expected = void rows only)\n", n_neg))

  # Step 1: remove voids (freq=8) and their original predecessors
  void_original_ids <- dt[tob_freq == "8" & !is.na(original_claim_id), original_claim_id]
  dt <- dt[tob_freq != "8"]
  dt <- dt[!claim_id %chin% void_original_ids]

  # Step 2: for replacements (freq=7), drop the superseded originals
  replacement_ids <- dt[tob_freq == "7" & !is.na(original_claim_id), original_claim_id]
  dt <- dt[!(claim_id %chin% replacement_ids & tob_freq != "7")]

  # Step 3: drop interim bills (freq 2, 3, 4)
  dt <- dt[!tob_freq %chin% c("2", "3", "4")]

  cat(sprintf("QC: %d final-action rows after deduplication.\n", nrow(dt)))
  dt
}


# ── 2. PHARMACY B2 REVERSAL REMOVAL ────────────────────────────────────────────────────

remove_pharmacy_b2_reversals <- function(rx) {
  rx <- copy(rx)
  key_cols <- c("person_id", "ndc", "pharmacy_id", "fill_date")

  b2_keys <- rx[claim_type == "B2", .SD, .SDcols = key_cols]
  # Flag B1 rows that match a B2 reversal key
  rx[, row_id := .I]
  b1_matched_ids <- rx[
    claim_type == "B1",
  ][b2_keys, on = key_cols, nomatch = 0L, row_id]

  n_b2 <- rx[claim_type == "B2", .N]
  cat(sprintf(
    "QC: %d B2 reversals; %d matched B1 phantom fills removed. Reversal rate = %.1f%%\n",
    n_b2, length(b1_matched_ids), 100 * n_b2 / nrow(rx)
  ))

  b2_ids  <- rx[claim_type == "B2", row_id]
  drop_ids <- union(b2_ids, b1_matched_ids)
  rx[!row_id %in% drop_ids][, row_id := NULL]
}


# ── 3. DENIED CLAIM FLAG ─────────────────────────────────────────────────────────────

flag_denied_claims <- function(dt) {
  dt <- copy(dt)
  dt[, final_paid := paid_amt > 0]
  n_denied <- dt[final_paid == FALSE, .N]
  cat(sprintf(
    "QC: %d denied rows (paid_amt = 0). Exclude from cost sums; estimand governs utilization.\n",
    n_denied
  ))
  dt
}


# ── WORKED EXAMPLE ────────────────────────────────────────────────────────────────────

raw <- data.table(
  claim_id          = c("IP-2023-001","IP-2023-001-V","IP-2023-001-R","OP-2023-002","IP-2023-001-LC"),
  original_claim_id = c(NA, "IP-2023-001", "IP-2023-001", NA, "IP-2023-001"),
  tob_freq          = c("1","8","7","1","1"),
  allowed_amt       = c(10000, -10000, 11000, 2000, 0),
  paid_amt          = c(8500, -8500, 9350, 0, 0),
  denial_code       = c("","","","CO-4","")
)

cat(sprintf("Naive sum of allowed_amt: %.0f\n", sum(raw$allowed_amt)))  # 13000

clean <- deduplicate_institutional_claims(raw)
clean <- flag_denied_claims(clean)
cost_sum <- clean[final_paid == TRUE, sum(allowed_amt)]
cat(sprintf("Final-action cost numerator (paid claims only): %.0f\n", cost_sum))  # 11000

/* ── Create worked-example dataset ── */
data work.raw_claims;
  length claim_id $20 original_claim_id $20 tob_freq $1 denial_code $10;
  input claim_id $ original_claim_id $ tob_freq $ allowed_amt paid_amt denial_code $;
  datalines;
IP-2023-001    .                 1  10000  8500  .
IP-2023-001-V  IP-2023-001      8 -10000 -8500  .
IP-2023-001-R  IP-2023-001      7  11000  9350  .
OP-2023-002    .                 1   2000     0  CO-4
IP-2023-001-LC IP-2023-001      1      0     0  .
;
run;

/* QC: Naive sum of allowed_amt (should = 13000 before deduplication) */
proc sql;
  select sum(allowed_amt) as naive_sum label='Naive sum (pre-dedup)' from work.raw_claims;
quit;
/* Expected output: naive_sum = 13000 */

/* ── 1. INSTITUTIONAL FINAL-ACTION DEDUPLICATION ── */

/* Step 1a: Identify void predecessors to remove */
proc sql;
  create table work.void_originals as
  select original_claim_id as claim_id
  from work.raw_claims
  where tob_freq = '8' and original_claim_id ne '.';
quit;

/* Step 1b: Identify superseded originals (have a replacement, freq=7) */
proc sql;
  create table work.superseded as
  select original_claim_id as claim_id
  from work.raw_claims
  where tob_freq = '7' and original_claim_id ne '.';
quit;

/* Step 2: Build final-action file — remove voids, superseded originals, interim bills */
proc sql;
  create table work.final_action as
  select r.*
  from work.raw_claims r
  where r.tob_freq not in ('8','2','3','4')                   /* drop voids + interim */
    and r.claim_id not in (select claim_id from work.void_originals)    /* drop voided originals */
    and not (r.claim_id in (select claim_id from work.superseded)       /* drop superseded originals */
             and r.tob_freq ne '7');                          /* keep the replacement itself */
quit;

/* QC: Row count and cost sum after deduplication */
proc sql;
  select count(*) as final_rows, sum(allowed_amt) as final_allowed_sum
  from work.final_action;
quit;
/* Expected: final_rows = 3 (replacement + denied + late_charge), final_allowed_sum = 13000
   NOTE: 13000 because denied ($2000) and late ($0) are still present.           */

/* ── 3. FLAG DENIED CLAIMS (estimand-specific cost vs utilization filter) ── */
data work.final_action;
  set work.final_action;
  final_paid = (paid_amt > 0);   /* 1 = reimbursed; 0 = denied */
run;

/* Cost numerator = only final_paid = 1 claims */
proc sql;
  select sum(allowed_amt) as final_action_cost
  from work.final_action
  where final_paid = 1;
quit;
/* Expected: final_action_cost = 11000 (replacement only) */

/* QC: Denied claim count */
proc freq data=work.final_action;
  tables final_paid / nocum;
  title 'Denied vs paid claims after final-action deduplication';
run;

Citations

1. [1]Schneeweiss S, Avorn J. A review of uses of health care utilization databases for epidemiologic research on therapeutics. Journal of Clinical Epidemiology. 2005;58(4):323-337.
2. [2]Tyree PT, Lind BK, Lafferty WE. Challenges of using medical insurance claims data for utilization analysis. American Journal of Medical Quality. 2006;21(4):269-275.