Why the Reason for an Absent Value Matters
A blank in a data extract is an observation about the file, not an explanation of what happened. The value may be absent because the field did not apply to that record, because the study or system was not designed to collect it, or because a value that was expected was lost or never entered. Those situations have different meanings. Counting all blanks alike can make a well-populated field appear incomplete, or hide a genuine capture problem.
For example, a deductible for an optional insurance rider is not applicable to a policy that does not include the rider. That is different from a missing deductible on a policy that does include it. The first is a design-defined absence; the second is an unexpected gap that may require investigation. In either case, a blank should not automatically be replaced by zero.
“Not applicable” and “not collected by design” are both design-absent, but they describe different reasons. A field may not apply to a record at all, such as a vehicle’s engine size for a home insurance policy. Alternatively, a field may have a meaningful value but be unavailable because a data system introduced it only from a particular date. The reason should be retained where possible, since it affects what can be concluded from the data.
Define Applicability Before Counting Blanks
For a particular field, first specify which records are eligible to have a value. Eligibility is field-specific: a record can be eligible for one field but not another. Then compare the eligible records with the records that have a usable value. This keeps design-absent records out of the denominator for a capture-completeness calculation.
This calculation assumes that eligibility has been defined consistently and that “observed” means a usable value under the field’s specification. If the extract does not include enough information to determine applicability, the eligible denominator is not known; a blank-count calculation cannot repair that uncertainty. The data specification, source documentation, or a targeted audit may be needed to classify the records.
The denominator matters because the question is conditional: among records where the field was expected, how often was it captured? The justification follows directly from the count. There are \(N_E\) eligible opportunities for capture, of which \(N_O\) succeeded and \(N_E-N_O\) did not. Therefore the success fraction is \(N_O/N_E\), while the failure fraction is \((N_E-N_O)/N_E\). Adding inapplicable records would count cases with no opportunity for capture as failures.
Worked Example: An Optional Coverage Field
A hypothetical insurer is reviewing a data extract of 120 policies. An optional water-backup rider is included on 72 policies and absent from 48. The rider deductible is recorded for 66 of the rider policies and blank for the other 6. It is blank for all 48 policies without the rider. Calculate the appropriate completeness and missingness measures. The common mistake is to count every blank as a capture failure.
Method: define the eligible records first. The rider deductible applies only when the rider is included, so the 72 rider policies are eligible. The 48 policies without the rider have a design-absent deductible, not a missing deductible.
Unexpected missing count. Among the 72 eligible policies, 66 have a recorded deductible. Thus \(72-66=6\) eligible values are unexpectedly missing.
Completeness among eligible policies. The calculation is \(66/72=11/12=0.91666\ldots\), or 91.7% when rounded to one decimal place.
Unexpected missing proportion. The calculation is \(6/72=1/12=0.08333\ldots\), or 8.3% to one decimal place. The two percentages sum to 100.0% after rounding.
There are \(48+6=54\) blank cells in the full extract. Dividing 66 recorded values by all 120 policies gives 55.0%, but that is not capture completeness: it mixes the design decision not to have the rider with the failure to record an applicable deductible. The correct result for capture among eligible policies is 91.7%.
Planned Collection Changes Are Not Capture Failures
A field can be relevant to every record yet still be absent by design for some records. For example, an older system may not have collected a particular measurement, while a replacement system does. Such cases should not be labelled “not applicable” if the concept applies; the reason is instead that collection was not part of the design during that period. This distinction is useful when interpreting records across system versions or study periods.
A designed collection boundary does not establish the value that would have been recorded had collection occurred. Do not treat the absence as a zero, or assume it can be reconstructed. When comparing or summarizing data, state which records had the opportunity for the field to be collected. Otherwise a change in data collection can be mistaken for a change in the underlying population.
Worked Example: A Field Introduced in a Later System
A hypothetical study has 80 records. A repair-complexity score was not collected by the original system for the first 30 records. The later system was designed to collect the score for the remaining 50 records; 48 of those have a value and 2 are blank. Calculate completeness and missingness for the field. The common mistake is to call all 32 blank cells unexpected missing values.
Method: separate the collection design from the capture result. The 30 records handled by the original system are design-absent because the field was not collected under that system. The 50 later-system records are eligible for the calculation.
Unexpected missing count. There are \(50-48=2\) eligible records with no score.
Completeness among eligible records. \(48/50=0.96\), so completeness is 96.0%.
Unexpected missing proportion. \(2/50=0.04\), so the unexpected missing proportion is 4.0%. These figures sum to 100.0%.
The extract contains \(30+2=32\) blank scores. Although \(32/80=40.0\%\) of all cells are blank, that is not the unexpected missing rate. It combines 30 records for which the system was not designed to collect the score with 2 failures among records for which collection was expected. The appropriate capture result is 4.0% missing among eligible records.
Record the Reason, Not Just the Blank
If the data format permits, preserve a reason for each absent value. For example, a field specification might distinguish “not applicable,” “not collected in this system period,” and “expected but not captured.” These labels should be based on documented rules or evidence, not guesses made from the blank itself. If the source provides only a generic null, avoid silently assigning one reason to every record.
A useful data review asks three questions for each important field:
State the condition under which a record is in scope for the field. If the condition is false, classify the value as not applicable rather than as a capture failure.
Check the study protocol, source definition, or system period. If collection was not planned for that record, document the design boundary.
Count eligible records with usable values and eligible records without them. Investigate the latter as unexpected missingness.
A value can also be present but unusable, such as a malformed date or a code that is not in the field’s permitted set. Whether such a value is treated as missing for a particular calculation should follow the field specification; it should not be counted as a valid observation merely because the cell is nonblank. Keep the original value and the reason for any exclusion where feasible.
Worked Example: Repair Duration in Claims
A hypothetical claims review contains 100 claims. Repair duration applies to 60 claims that required physical repairs. The other 40 required no repair, so duration is not applicable. Of the 60 eligible claims, 54 have a usable duration and 6 do not. A source check shows that 4 durations were absent from the vendor file and 2 were not entered into the claims system. Calculate the eligible-record measures and explain the correct interpretation. The common mistake is to report the 46 blank cells as if they all represented failed capture.
Method: classify by applicability, then measure capture. The 40 claims with no repair required are not eligible for a repair-duration value. The 60 claims with repairs are eligible.
Completeness. \(54/60=0.90\), so 90.0% of eligible repair claims have a usable duration.
Unexpected missingness. The count is \(60-54=6\), and the proportion is \(6/60=0.10\), or 10.0%. The source check explains the six gaps: four vendor-file omissions and two system-entry omissions. Both are unexpected capture gaps, though their operational causes differ.
There are \(40+6=46\) blank cells in the full set of 100 claims. The 40 claims with no repair are design-absent; they do not represent failed duration capture. The correct statement is that duration completeness is 90.0% among claims where repair duration applies, with 6 unexpected gaps requiring attention.
Common Traps and Practical Consequences
- Using the full data set as the denominator. This mixes records with no opportunity for collection into a capture rate. Define eligibility for the field before calculating a rate.
- Assuming every blank means zero. A missing deductible, duration, or measurement does not mean the quantity was zero. Zero is a recorded value with a specific meaning; absence is not that value.
- Calling all design-absent values “not applicable.” A field not collected by an older system may still apply conceptually. Preserve the distinction between applicability and planned collection.
- Imputing before identifying the cause. Replacing design-absent and unexpected missing values alike can invent values for cases where the field has no meaning, or conceal a data-capture problem. First establish why values are absent and what the analysis requires.
- Inferring the reason from the pattern alone. A cluster of blanks in older records may suggest a system change, but the reason should be verified against the data design or source documentation.
These checks support sound study design and clear interpretation: a reported completeness rate should state both the field and its eligible population. Explicit reasons also make it easier to investigate capture problems without confusing them with planned exclusions from collection. This topic is aligned with the SOA Exam PA (Predictive Analytics) syllabus.
Check Your Understanding
For each problem, identify design-absent records separately from unexpected missing values, then calculate the requested rate.
- A hypothetical portfolio has 90 policies. A flood-coverage limit applies to 72 policies with flood coverage; 69 of those have a recorded limit. The other 18 policies do not have flood coverage. What is eligible-record completeness and unexpected missingness? (Answer: 95.8% complete; 4.2% unexpectedly missing among eligible policies.)
- A study includes 140 records. A field was not collected by design for 60 records in an older system period. It was expected for the other 80 records, and 74 values are recorded. What is the eligible-record missing proportion? (Answer: 6/80 = 7.5% unexpectedly missing; the 60 older-period records are design-absent.)
- Among 50 hypothetical patient records, a test result applies to 40 patients and is present for 36. The test was not relevant for the other 10. What are completeness and unexpected missingness among eligible patients? (Answer: 90.0% complete and 10.0% unexpectedly missing among eligible patients.)
- A claims extract has 200 records. A repair-cost field applies to 120 claims, of which 108 have usable values; it does not apply to the other 80 claims. What is the eligible-record completeness, and why is the all-record blank proportion not the capture rate? (Answer: 108/120 = 90.0% complete; 92 blanks include 80 design-absent values and 12 unexpected gaps, so the all-record blank proportion mixes different reasons.)
- A database shows 25 null values for a field, but the extract contains no applicability flag, system-period indicator, or reason code. Can the unexpected missing proportion be calculated from these facts alone? What should be done next? (Answer: No; the eligible denominator and reasons for the nulls are unknown. Consult the field specification or source records and classify applicability and collection design before calculating the rate.)