Missing values are the silent killers of reporting pipelines. One day your dashboard shows a clean “Total Sales,” the next day it’s blank because a new upstream feed started sending partial rows. I’ve seen this exact issue stall releases: a derived metric depends on four possible source columns, and when the “primary” column is missing, the whole calculation collapses even though a perfectly good fallback value exists.
When I’m building SAS data steps (or PROC SQL views) for real production data, I treat “pick the first available value” as a first-class operation. That’s where COALESCE earns its keep: you give it a list of expressions, and it returns the first one that isn’t missing. The result is cleaner transformation code, fewer IF/THEN ladders, and a clearer contract for how your dataset handles incomplete records.
You’ll walk away knowing how COALESCE behaves for numeric vs character values, how to use it across a range of variables, how to simulate “last non-missing” selection, and how to avoid the subtle traps that show up in joins, type conversions, and business rules.
The mental model: a left-to-right fallback chain
Think of COALESCE as a row-level “fallback chain.” For each row, SAS evaluates the arguments from left to right and returns the first value that is not missing.
- For numeric values, missing is
.(and special missings like.A,.B, etc.). - For character values, missing is a blank string (effectively "").
If every argument is missing, COALESCE returns missing.
A simple analogy I use when explaining this to teammates: you’re trying phone numbers in order—work, then mobile, then home—until someone picks up. COALESCE does the same thing, except it’s checking for “non-missing” rather than “answered.”
One more important detail: COALESCE is for numeric arguments. For character strings, use COALESCEC. I’ll show both, plus patterns for mixed-type data.
Build a sample dataset you can run locally
I like to start with a dataset where each row has several candidate columns, some missing and some present. Here’s a small, runnable example.
/ Sample data: four assessment values with gaps /
data temp;
input roll_no a1-a4;
datalines;
12 . 98 52 .
23 79 . 67 .
14 90 82 88 85
;
run;
proc print data=temp noobs;
run;
Expected shape (your exact formatting may vary):
a1
a3
—:
—:
.
52
79
67
90
88
This is the classic “multiple sources for the same concept” pattern. In real projects, those columns might be:
- primary score vs regrade score vs imported score
- event timestamp from device vs server vs batch backfill
- address from billing vs shipping vs CRM
First non-missing across columns (the common case)
When you want “the first value that exists,” COALESCE is exactly the tool.
Data step: COALESCE across a variable range
/ First non-missing value among a1-a4 /
data exam_first;
set temp;
firstnonmiss_val = coalesce(of a1-a4);
run;
proc print data=exam_first noobs;
run;
For roll_no=12, SAS evaluates:
a1is missing (.) → keep goinga2is 98 → return 98 and stop
You’ll get:
a1
a3
firstnonmissval
—:
—:
—:
.
52
98
79
67
79
90
88
90### PROC SQL: same logic inside a query
If you’re building a view or shaping data during a join, PROC SQL is often the cleanest place to apply the rule.
proc sql;
create table examfirstsql as
select
roll_no,
a1, a2, a3, a4,
coalesce(a1, a2, a3, a4) as firstnonmiss_val
from temp;
quit;
proc print data=examfirstsql noobs;
run;
A practical guideline: if the fallback chain is part of your business meaning (not just formatting), I keep it close to where I define the dataset contract—often the transformation step that produces the “gold” table.
Simulating “last non-missing” (reverse the list)
SAS doesn’t provide a built-in “last non-missing across these columns” function, but you can get it by reversing the argument order and still using COALESCE. That forces evaluation from right to left.
/ Last non-missing value among a1-a4 /
data exam_last;
set temp;
lastnonmiss_val = coalesce(of a4-a1);
run;
proc print data=exam_last noobs;
run;
This works because:
coalesce(of a4-a1)is equivalent tocoalesce(a4, a3, a2, a1)- the first non-missing in that reversed sequence is the last non-missing in the original sequence
Expected results:
a1
a3
lastnonmissval
—:
—:
—:
.
52
52
79
67
67
90
88
85### When reversing isn’t enough: arrays and “last by condition”
Sometimes “last” isn’t purely positional. Maybe you want the last valid measurement (non-missing and within range), or the last value matching a flag.
Here’s a pattern I use with arrays because it’s explicit and easy to extend:
/ Last non-missing AND non-negative value /
data examlastrule;
set temp;
array scores[4] a1-a4;
last_valid = .;
do idx = dim(scores) to 1 by -1;
if not missing(scores[idx]) and scores[idx] >= 0 then do;
last_valid = scores[idx];
leave;
end;
end;
drop idx;
run;
proc print data=examlastrule noobs;
run;
If your rule will evolve (and it will), the array approach saves you from constantly reshuffling argument lists.
Character data: COALESCEC, blanks, and “real” missingness
Numeric missing is straightforward. Character missing is trickier because real-world feeds often contain:
- empty strings
- strings full of spaces
- placeholders like "N/A", "UNKNOWN", "-"
COALESCEC for character columns
data contacts;
length customerid 8 email $60 phone $20 backupphone $20;
input customerid email $ phone $ backupphone $;
datalines;
101 . 555-0101 .
102 [email protected] . 555-0199
103 . . .
;
run;
data contacts_best;
set contacts;
bestphone = coalescec(phone, backupphone);
best_email = coalescec(email, "[email protected]");
run;
proc print data=contacts_best noobs;
run;
Two notes from experience:
1) In raw text inputs, a dot (.) might literally be a character, not missing. If you read with $ informat, "." is just a string. You should normalize it.
2) COALESCEC treats a blank string as missing, but it does not treat "N/A" as missing. You have to clean that yourself.
Normalizing placeholders before coalescing
I often normalize first, then coalesce. That keeps the fallback logic honest.
data contacts_clean;
set contacts;
/ Normalize common placeholders to real missing /
email_clean = strip(email);
if upcase(emailclean) in (".", "N/A", "NA", "NULL", "NONE", "UNKNOWN") then emailclean = "";
phone_clean = strip(phone);
if upcase(phoneclean) in (".", "N/A", "NA", "NULL") then phoneclean = "";
backupphoneclean = strip(backup_phone);
if upcase(backupphoneclean) in (".", "N/A", "NA", "NULL") then backupphoneclean = "";
bestphone = coalescec(phoneclean, backupphoneclean);
bestemail = coalescec(emailclean, "[email protected]");
drop email phone backup_phone;
run;
If you skip this step, COALESCEC can “successfully” return garbage—because technically it isn’t missing.
Mixed types: avoid implicit conversions that hide bugs
A common mistake is coalescing values that “look compatible” but aren’t.
- Numeric column:
amount - Character column:
amount_text(from a CSV)
You cannot safely coalesce them without deciding on a canonical type.
Pattern: canonical numeric output
data payments;
length invoiceid 8 amounttext $20;
input invoiceid amount amounttext $;
datalines;
9001 125.50 .
9002 . 89.99
9003 . N/A
;
run;
data payments_fixed;
set payments;
/ Normalize and parse the text amount /
amttxt = strip(amounttext);
if upcase(amttxt) in (".", "N/A", "NA", "NULL", "") then amtfrom_text = .;
else amtfromtext = input(amt_txt, best32.);
/ Prefer numeric amount, fallback to parsed text /
amountfinal = coalesce(amount, amtfrom_text);
drop amttxt amtfrom_text;
run;
proc print data=payments_fixed noobs;
run;
I’m deliberately strict here: if parsing fails, input(..., best32.) can still yield missing, which is what you want—bad text shouldn’t silently become zero.
Real-world scenarios where COALESCE pays off fast
1) Post-join defaulting (the “optional dimension” pattern)
You left join a dimension table for enrichment, but you still need a populated attribute for downstream grouping.
data fact_sales;
input orderid customerid region_code $ revenue;
datalines;
1 10 NE 125
2 11 . 90
3 12 SW 210
;
run;
data dim_customer;
input customerid preferredregion $;
datalines;
10 NE
11 SE
12 .
;
run;
proc sql;
create table sales_enriched as
select
f.order_id,
f.customer_id,
f.revenue,
/ Prefer fact value, fallback to dimension, then a final default /
coalescec(f.regioncode, d.preferredregion, "UNKNOWN") as region_final
from fact_sales f
left join dim_customer d
on f.customerid = d.customerid;
quit;
If you don’t set a stable default, you’ll end up with missing group keys and confusing totals.
2) Hierarchical sourcing (golden record fields)
When you maintain a “golden record,” you usually trust sources in a specific order.
Example rule:
- Use the verified email if present
- else the CRM email
- else the billing email
- else blank (and you handle it downstream)
That becomes a single line:
bestemail = coalescec(emailverified, emailcrm, emailbilling);
I like this because the order is obvious in code review. With nested IF blocks, it’s easy to miss that someone swapped precedence.
3) Conditional fallbacks (coalesce after validation)
Sometimes you want “first non-missing” only after checking validity. You can do this with expressions inside COALESCE.
/ Prefer a1 only if it‘s within 0-100; otherwise treat it as missing /
firstvalidscore = coalesce(
ifn(not missing(a1) and a1 between 0 and 100, a1, .),
ifn(not missing(a2) and a2 between 0 and 100, a2, .),
ifn(not missing(a3) and a3 between 0 and 100, a3, .),
ifn(not missing(a4) and a4 between 0 and 100, a4, .)
);
That pattern looks “wordy,” but it’s still less fragile than a maze of IF/ELSE branches, and it keeps the validity rule adjacent to the fallback chain.
Understanding missingness like SAS does (numeric, character, and special missing values)
When people say “missing,” they often mean five different things:
- truly absent (
.for numeric, blank for character) - present but unusable ("N/A", "UNKNOWN", "0" as a placeholder)
- present but out of range (score 999)
- present but not applicable (a legitimate category)
- present but stale (timestamp older than X days)
COALESCE only cares about SAS missingness, not your business definition. That’s why I treat it as a final selection operator after I’ve decided what “counts” as missing.
Special numeric missings (.A–.Z)
SAS supports special missing values like .A, .B, etc. They’re still missing, but they can represent different reasons (for example: “Not collected,” “Refused,” “System error”).
Two practical implications:
COALESCEtreats special missings as missing and will keep scanning.- If you want to preserve a special missing reason (instead of falling back), you need a rule.
Example: keep .A if it means “Refused,” otherwise fall back.
/ Keep .A as a meaningful missing; treat other missings as missing /
score_final = coalesce(
ifn(score_primary = .A, .A,
ifn(missing(scoreprimary), ., scoreprimary)),
score_backup
);
That’s a niche case, but it shows the theme: COALESCE is a selection tool, not a policy engine.
Counting missings: NMISS and CMISS
When I’m debugging sparse records, I often count missingness to validate assumptions.
missingnumericcount = nmiss(of a1-a4);
missingtotalcount = cmiss(of all);
NMISScounts missing numeric values.CMISScounts missing values across numeric and character.
This complements COALESCE nicely: I can select the best value and also measure how “complete” the row is.
Result attributes: length, format, and why argument order matters
There’s a subtle trap with COALESCEC that bites people in production: the result’s attributes are driven by the first argument in many contexts.
Character length gotcha
If the first argument is short (say $10) and later arguments can be longer (say $60), the result may be truncated unless you explicitly set the length.
I prevent this by declaring length for the output column.
data contacts_safe;
set contacts_clean;
length bestemail $60 bestphone $20;
bestphone = coalescec(phoneclean, backupphoneclean);
bestemail = coalescec(emailclean, "[email protected]");
run;
My rule: if a COALESCEC output becomes a dimension key, ID, or user-visible label, I always declare the output length. It’s cheap insurance.
Numeric format and readability
Numeric values don’t have length the same way, but formats matter (especially for dates/datetimes). If you coalesce date variables with different formats—or worse, mix a date with a datetime—you can create confusion.
The fix is to standardize types before you coalesce (more on that in the timestamp section).
COALESCE vs related tools: what I reach for and why
I think of COALESCE as one tool in a small family of “missing-handling” patterns.
COALESCE vs IFN/IFC
If I’m choosing between two values with a single missing check, I sometimes use IFN (numeric) or IFC (character) to make intent explicit.
amountfinal = ifn(not missing(amount), amount, amountbackup);
phonefinal = ifc(not missing(strip(phone)), strip(phone), strip(backupphone));
If the chain is 3+ options, I almost always prefer COALESCE/COALESCEC because it scales and reads like a policy.
COALESCE vs SUM
SUM(x,y) ignores missing values and returns the sum of non-missing values. That’s a completely different behavior.
- If you want “use backup when primary is missing,” use
COALESCE. - If you want “treat missing as zero for arithmetic,” consider
SUM(but be careful—this can hide missingness).
Example:
/ Fallback selection /
revenue = coalesce(revenueprimary, revenuebackup);
/ Arithmetic with missing treated as 0 /
revenuetotal = sum(revenueprimary, revenue_backup);
These produce very different results when both columns are present.
COALESCE vs MAX/MIN
Some folks try max(of a1-a4) to get “the best value.” That’s fine only if “best” literally means highest numeric value, not “first non-missing by trust order.”
If precedence matters (primary source wins even if it’s smaller), use COALESCE.
COALESCE for dates and timestamps (and how I keep it sane)
Time fields are where pipelines quietly go wrong because SAS has distinct concepts:
- DATE: days since 01JAN1960
- DATETIME: seconds since 01JAN1960
They’re both numeric under the hood, so COALESCE will happily run, but the meaning can be nonsense if you mix them.
Pattern: standardize to DATETIME
Suppose you have:
event_dt(a SAS datetime)event_date(a SAS date)ingest_dt(a SAS datetime)
You want one eventtsfinal datetime.
data events;
length id 8;
format eventdt ingestdt datetime19. event_date date9.;
input id eventdt :datetime19. eventdate :date9. ingest_dt :datetime19.;
datalines;
1 . 01JAN2026 04FEB2026:10:03:00
2 04FEB2026:09:55:00 . 04FEB2026:10:02:00
3 . . 04FEB2026:10:05:00
;
run;
data events_fixed;
set events;
format eventtsfinal datetime19.;
/ Convert DATE to DATETIME (midnight) before coalescing /
eventdateasdt = ifn(missing(eventdate), ., dhms(event_date, 0, 0, 0));
eventtsfinal = coalesce(eventdt, eventdateasdt, ingest_dt);
drop eventdateas_dt;
run;
What I like about this is the explicit conversion step. Anyone reviewing the code can see that we’re making a deliberate choice: “if we only have a date, treat it as midnight.” If that’s not acceptable, you can encode a different rule.
Pattern: standardize to DATE
If downstream expects a date key, I convert datetimes to dates first.
eventdatefinal = coalesce(
ifn(missing(eventdt), ., datepart(eventdt)),
event_date
);
format eventdatefinal date9.;
Again: standardize type first, then coalesce.
Choosing the winning source (auditability and debugging)
COALESCE gives you the chosen value, but in production I often need to answer:
- Which column won?
- How often are we falling back?
- Did fallback patterns change this week?
Pattern: store a “source label” alongside the value
Here’s a simple, explicit approach for numeric values.
data exam_audited;
set temp;
firstnonmiss_val = coalesce(of a1-a4);
length first_source $2;
if not missing(a1) then first_source = "a1";
else if not missing(a2) then first_source = "a2";
else if not missing(a3) then first_source = "a3";
else if not missing(a4) then first_source = "a4";
else first_source = "--";
run;
Yes, it’s a bit repetitive, but it’s incredibly easy to interpret when someone pings you with a weird record.
Pattern: compute the winner index using arrays
When the list gets long (say a1-a40), I prefer an array approach that scales.
data examauditedarray;
set temp;
array scores[*] a1-a4;
chosen = .;
chosen_idx = .;
do i = 1 to dim(scores);
if not missing(scores[i]) then do;
chosen = scores[i];
chosen_idx = i;
leave;
end;
end;
drop i;
run;
Now you can run simple checks:
proc freqonchosen_idx- compare distributions across days
Tracking fallback rates as a pipeline health metric
If you already have a scheduled SAS job, tracking fallback rates is one of the highest-leverage “cheap monitoring” additions.
proc freq data=exam_audited;
tables first_source / missing;
run;
If you see a3 suddenly becoming the primary source for 40% of rows, that’s a strong signal upstream changed.
Coalescing across wide tables: variable lists that scale
SAS’s OF syntax is a superpower here. I use it to avoid hand-writing long argument lists.
Contiguous ranges: of a1-a200
Best when variables are truly contiguous and you mean “scan this block.”
best_val = coalesce(of score1-score200);
Prefix lists: of score:
Best when variables share a prefix but aren’t contiguous.
best_val = coalesce(of score:);
That : suffix means “all variables whose names start with this prefix,” which is great for evolving schemas.
numeric and character (use with care)
You can coalesce across all numeric variables, but I only do this for debugging/profiling—not for business logic.
/ Debug only: first non-missing numeric value among all numeric vars /
firstanynumeric = coalesce(of numeric);
In real transformations, I prefer to explicitly define the candidate set so I don’t accidentally pull in unrelated fields.
COALESCE in PROC SQL: joins, computed columns, and grouping
PROC SQL is where COALESCE becomes more than a convenience—it becomes a way to keep join logic and defaulting rules readable.
Safer derived keys after a left join
A common pattern: you need a grouping key that must never be missing.
proc sql;
create table grouped_sales as
select
coalescec(f.regioncode, d.preferredregion, "UNKNOWN") as region_final,
sum(f.revenue) as revenue_total
from fact_sales f
left join dim_customer d
on f.customerid = d.customerid
group by calculated region_final;
quit;
I like calculated region_final here because it avoids duplicating the coalesce expression in multiple places.
Conditional coalesce inside SQL
Sometimes you only want to accept a value if it passes a rule.
proc sql;
create table examvalidsql as
select
roll_no,
coalesce(
case when a1 between 0 and 100 then a1 else . end,
case when a2 between 0 and 100 then a2 else . end,
case when a3 between 0 and 100 then a3 else . end,
case when a4 between 0 and 100 then a4 else . end
) as firstvalidscore
from temp;
quit;
This reads like a policy: “try each source, but only if it’s valid.”
Join conditions: don’t use COALESCE to paper over bad keys
You can do things like:
/ This is often a smell /
on coalesce(f.customerid, -1) = d.customerid
But I try not to. If a join key is missing, that’s usually a data quality issue or a modeling choice, not something to hide.
A better approach:
- keep missing join keys missing
- output a quality flag
- decide downstream how to treat unmatched records
Common mistakes (and how I prevent them)
Mistake 1: Treating zero as missing
COALESCE does not treat 0 as missing. If your business rule considers 0 invalid (common with “unknown” placeholders), you must convert it to missing before coalescing.
amount_clean = ifn(amount = 0, ., amount);
amountfinal = coalesce(amountclean, amount_backup);
Mistake 2: Forgetting type-specific functions
- Numeric:
COALESCE - Character:
COALESCEC
If you mix them up, you can get unexpected conversions or errors.
Mistake 3: Coalescing raw strings with spaces
A character column that contains spaces is effectively missing to humans, but not always to code unless you normalize.
phone_norm = strip(phone);
bestphone = coalescec(phonenorm, backupphonenorm);
Mistake 4: Hiding bad data with defaults too early
Defaults are helpful, but they can also mask data quality issues.
My rule of thumb:
- In your staging layer, keep true missing values and add data quality flags.
- In your serving layer (reports, extracts), apply defaults for stability.
Example:
isemailmissing = missing(email_clean);
bestemail = coalescec(emailclean, "");
That way you can still measure how often the pipeline relied on fallbacks.
Mistake 5: Putting expensive expressions first
Because COALESCE evaluates left to right until it finds a non-missing value, argument order can matter when expressions are expensive (string parsing, regex-like logic, repeated UPCASE/STRIP, date conversions).
If I know 95% of rows have primary_value, I put it first and push heavier parsing later.
valuefinal = coalesce(primaryvalue, parsedvaluefromtext, fallbackvalue);
The primary reason is readability, but you can also avoid unnecessary work at scale.
Mistake 6: Truncation from COALESCEC result length
If the first argument is shorter than later candidates, you can silently cut data.
Prevention:
- declare output
length - or ensure the first argument has sufficient length
length region_final $20;
regionfinal = coalescec(regioncode, preferred_region, "UNKNOWN");
Alternative approaches (and when they’re actually better)
I use COALESCE a lot, but there are cases where I reach for different tools.
Approach 1: Explicit IF/ELSE ladders (when rules are contextual)
If the selection depends on multiple columns (flags, dates, statuses), an explicit ladder can be clearer.
Example: “Use device timestamp only when the device is trusted and the value is within a plausible window; otherwise use server timestamp.” That’s often easier to audit in explicit logic than in a dense expression.
Approach 2: Lookup-driven precedence (when precedence changes)
If precedence changes by segment (region, product type, source system), hardcoding argument order becomes fragile.
In those cases, I sometimes model precedence as data:
- a small table that maps segment → preferred source order
- logic that selects based on that order
This is more work, but it prevents constant code edits when the business changes its mind.
Approach 3: Arrays with scoring/priority (when it’s not “first non-missing”)
Sometimes you want “best” based on multiple criteria:
- most recent timestamp
- highest confidence score
- preferred source
That becomes a mini decision engine, not a coalesce chain.
Performance and maintainability (what matters in 2026 workflows)
COALESCE itself is fast. The bigger cost is usually the surrounding work: IO, joins, parsing, and heavy string normalization. Still, I care about performance because coalescing often sits in wide tables with millions of rows.
Here’s what I’ve learned holds up in production:
- Prefer
coalesce(of a1-a200)over manualcoalesce(a1,a2,...)when columns are contiguous and the intent is “scan this block.” It’s shorter and less error-prone. - If your fallback rule includes validation logic, consider an array loop when the same condition repeats across many columns. It’s often easier to test and change.
- Keep normalization steps (like placeholder cleanup) close to ingestion so downstream code stays simple.
I also care about how code gets maintained now that teams expect strong automation and reviews.
Traditional approach
—
Implicit IF/ELSE chains
COALESCE fallback order Fixed by hand after reports break
Done in an editor, run once
Print a few rows
Manual search through code
A workflow I recommend if you’re shipping SAS code like software:
- Put fallback rules in one transformation step (or a macro/function) so precedence stays consistent.
- Add a tiny “known cases” dataset that you can rerun in seconds.
- Track how often each fallback path triggers (even if it’s a simple
PROC FREQ). If the distribution changes suddenly, you catch upstream feed issues earlier.
Performance ranges are usually dominated by your environment, but in typical warehouse-style runs I’ve seen “add a few coalesce columns” remain negligible compared to reading and sorting data. The moment you add heavy parsing or repeated UPCASE/STRIP on dozens of columns, you can feel it—so normalize once, reuse normalized columns.
A practical testing mindset: how I keep coalesce rules from regressing
The most common coalesce regression isn’t syntax—it’s a rule change that nobody noticed:
- a new upstream column appears
- a placeholder value changes from "N/A" to "NULL"
- a “primary” field becomes sparse
Here’s what I do to keep control.
1) Build a tiny “row audit” dataset
I maintain a small dataset with representative cases:
- primary present
- primary missing, secondary present
- all missing
- placeholder strings
- out-of-range values
Then I run transformation logic on it in the same job (or at least the same repo) to quickly see if outputs change.
2) Assert distributions (lightweight)
Even without a full test framework, you can build simple checks:
- number of missing finals
- frequency of source winners
- percent of default "UNKNOWN"
If those numbers move significantly, treat it as a pipeline change.
3) Keep “defaulting” separate from “selection”
I try to separate:
- selecting best available real value (
COALESCE) - filling a final reporting default ("UNKNOWN", 0, etc.)
That separation makes it easier to spot data quality issues and avoid accidentally masking them.
When I reach for COALESCE (and when I don’t)
I reach for COALESCE/COALESCEC when:
- multiple columns represent the same business concept at different trust levels
- joins can produce sparse enrichment fields
- upstream feeds are inconsistent and you need stable downstream keys
I avoid it when:
- missingness is meaningful and should remain visible to downstream logic
- the “right value” depends on complex context (dates, flags, priorities) that deserves explicit code or a lookup table
A helpful question to ask yourself: “If I show this fallback order to a domain expert, would they agree that this is the rule?” If yes, COALESCE is a great fit.
Key takeaways and next steps
If you only remember one thing, remember this: COALESCE is a row-level fallback chain that returns the first non-missing argument, evaluated left to right.
What I’d keep on a sticky note:
- Use
COALESCEfor numeric andCOALESCECfor character. - Normalize placeholders ("N/A", ".", "NULL", whitespace) before you coalesce.
- Reverse the argument list to simulate “last non-missing.”
- Standardize types before coalescing dates vs datetimes.
- Declare output
lengthforCOALESCECresults to avoid truncation. - In production, store how you chose the value (source label/index) so you can debug and monitor fallback rates.
Next steps I’d do in a real pipeline:
1) Identify 3–5 “golden record” fields where fallback order matters most.
2) Implement COALESCE/COALESCEC rules in one place (a transformation step or view) and treat them as part of your dataset contract.
3) Add a tiny audit metric: how often each fallback path wins, and how often you hit final defaults like "UNKNOWN".
Once you start thinking in fallback chains, you’ll notice how many reporting bugs are really “missingness policy bugs.” COALESCE is one of the cleanest ways to make that policy explicit, testable, and easy to maintain.
