12 Quality Checks
This chapter provides an overview of the quality checks found in the QA MIL Package, including:
Please note that the MIL table needs to have the ETL # stored in the MIL table dataset label in order to check that the MIL table is from the same ETL as all other production SCDM tables. If the ETL # is not stored in the MIL table dataset label, the package will abort with an error message.
12.1 Level 1 Quality Checks
Level 1 data quality checks review the completeness and content of each variable in each table to ensure that the required variables contain data and conform to the formats specified by the SCDM specifications. For each SCDM variable, Level 1 data checks verify that data types, and variable lengths, are correct and reported values are acceptable.
Mother-Infant Linkage Level 1 Quality Checks
| FlagID | FlagType | AbortYN | Flag_Descr | Variable1 |
|---|---|---|---|---|
| MIL_1_00_00-0_102 | Fail | Y | MIL table is not sorted by the following variables: MPatID, ADate, CPatID | |
| MIL_1_01_00-0_110 | Fail | Y | MPatID variable does not exist | MPatID |
| MIL_1_02_00-0_110 | Fail | Y | EncounterID variable does not exist | EncounterID |
| MIL_1_03_00-0_110 | Fail | Y | Age variable does not exist | Age |
| MIL_1_04_00-0_110 | Fail | Y | MBirth_Date variable does not exist | MBirth_Date |
| MIL_1_05_00-0_110 | Fail | Y | EncType variable does not exist | EncType |
| MIL_1_06_00-0_110 | Fail | Y | ADate variable does not exist | ADate |
| MIL_1_07_00-0_110 | Fail | Y | DDate variable does not exist | DDate |
| MIL_1_08_00-0_110 | Fail | Y | CPatID variable does not exist | CPatID |
| MIL_1_09_00-0_110 | Fail | Y | CBirth_Date variable does not exist | CBirth_Date |
| MIL_1_10_00-0_110 | Fail | Y | Sex variable does not exist | Sex |
| MIL_1_11_00-0_110 | Fail | Y | CEnr_Start variable does not exist | CEnr_Start |
| MIL_1_12_00-0_110 | Fail | Y | MatchMethod variable does not exist | MatchMethod |
| MIL_1_13_00-0_110 | Fail | Y | Birth_Type variable does not exist | Birth_Type |
| MIL_1_14_00-0_110 | Fail | Y | Birth_Type_Primes variable does not exist | Birth_Type_Primes |
| MIL_1_01_00-0_112 | Fail | Y | MPatID variable type does not conform to specification | MPatID |
| MIL_1_02_00-0_112 | Fail | Y | EncounterID variable type does not conform to specification | EncounterID |
| MIL_1_03_00-0_112 | Fail | Y | Age variable type does not conform to specification | Age |
| MIL_1_04_00-0_112 | Fail | Y | MBirth_Date variable type does not conform to specification | MBirth_Date |
| MIL_1_05_00-0_112 | Fail | Y | EncType variable type does not conform to specification | EncType |
| MIL_1_06_00-0_112 | Fail | Y | ADate variable type does not conform to specification | ADate |
| MIL_1_07_00-0_112 | Fail | Y | DDate variable type does not conform to specification | DDate |
| MIL_1_08_00-0_112 | Fail | Y | CPatID variable type does not conform to specification | CPatID |
| MIL_1_09_00-0_112 | Fail | Y | CBirth_Date variable type does not conform to specification | CBirth_Date |
| MIL_1_10_00-0_112 | Fail | Y | Sex variable type does not conform to specification | Sex |
| MIL_1_11_00-0_112 | Fail | Y | CEnr_Start variable type does not conform to specification | CEnr_Start |
| MIL_1_12_00-0_112 | Fail | Y | MatchMethod variable type does not conform to specification | MatchMethod |
| MIL_1_13_00-0_112 | Fail | Y | Birth_Type variable type does not conform to specification | Birth_Type |
| MIL_1_14_00-0_112 | Fail | Y | Birth_Type_Primes variable type does not conform to specification | Birth_Type_Primes |
| MIL_1_03_00-0_113 | Fail | Y | Age variable length does not conform to specifications | Age |
| MIL_1_04_00-0_113 | Fail | Y | MBirth_Date variable length does not conform to specifications | MBirth_Date |
| MIL_1_05_00-0_113 | Fail | Y | EncType variable length does not conform to specifications | EncType |
| MIL_1_06_00-0_113 | Fail | Y | ADate variable length does not conform to specifications | ADate |
| MIL_1_07_00-0_113 | Fail | Y | DDate variable length does not conform to specifications | DDate |
| MIL_1_09_00-0_113 | Fail | Y | CBirth_Date variable length does not conform to specifications | CBirth_Date |
| MIL_1_10_00-0_113 | Fail | Y | Sex variable length does not conform to specifications | Sex |
| MIL_1_11_00-0_113 | Fail | Y | CEnr_Start variable length does not conform to specifications | CEnr_Start |
| MIL_1_12_00-0_113 | Fail | Y | MatchMethod variable length does not conform to specifications | MatchMethod |
| MIL_1_13_00-0_113 | Fail | Y | Birth_Type variable length does not conform to specifications | Birth_Type |
| MIL_1_14_00-0_113 | Fail | Y | Birth_Type_Primes variable length does not conform to specifications | Birth_Type_Primes |
| MIL_1_05_00-0_121 | Fail | Y | EncType variable contains an invalid non-missing value | EncType |
| MIL_1_10_00-0_121 | Fail | Y | Sex variable contains an invalid non-missing value | Sex |
| MIL_1_12_00-0_121 | Fail | Y | MatchMethod variable contains an invalid non-missing value | MatchMethod |
| MIL_1_13_00-0_121 | Fail | Y | Birth_Type variable contains an invalid non-missing value | Birth_Type |
| MIL_1_14_00-0_121 | Fail | Y | Birth_Type_Primes variable contains an invalid non-missing value | Birth_Type_Primes |
| MIL_1_03_00-0_126 | Fail | Y | Age variable contains an invalid, out of range, non-missing value | Age |
Return to Table of Contents
Delivery Level 1 Quality Checks
| FlagID | FlagType | AbortYN | Flag_Descr |
|---|---|---|---|
| DEL_1_00_00-0_102 | Fail | Y | DEL table is not sorted by the following variables: MPatID, EncounterID |
Return to Table of Contents
Infant Level 1 Quality Checks
| FlagID | FlagType | AbortYN | Flag_Descr |
|---|---|---|---|
| INF_1_00_00-0_102 | Fail | Y | INF table is not sorted by the following variables: CPatID |
Return to Table of Contents
12.2 Level 2 Quality Checks
Level 2 data quality checks assess the logical relationship and integrity of data values within a variable or between two or more variables within and between tables.
12.2.1 Table of Contents
Jump to: Mother-Infant Linkage | Enrollment | Demographic | Encounter |
Mother-Infant Linkage Level 2 Quality Checks
| FlagID | FlagType | AbortYN | Flag_Descr | Variable1 | Variable2 | Variable3 | Variable4 |
|---|---|---|---|---|---|---|---|
| MIL-ENR_2_01_00-0_201 | Fail | Y | MPatID value present in MIL table, but not in the ENR table | MPatID | PatID | ||
| MIL-DEM_2_01_00-0_201 | Fail | Y | MPatID value present in MIL table, but not in the DEM table | MPatID | PatID | ||
| MIL-DEL_2_01_00-0_201 | Fail | Y | MPatID value present in MIL table, but not in the DEL table | MPatID | EncounterID | ||
| MIL-ENC_2_02_00-0_201 | Fail | Y | EncounterID value present in MIL table, but not in the ENC table | EncounterID | EncounterID | ||
| MIL-DEL_2_02_00-0_201 | Fail | Y | EncounterID value present in MIL table, but not in the DEL table | EncounterID | MPatID | ||
| MIL-ENR_2_08_00-0_201 | Fail | Y | CPatID value present in MIL table, but not in the ENR table | CPatID | PatID | ||
| MIL-DEM_2_08_00-0_201 | Fail | Y | CPatID value present in MIL table, but not in the DEM table | CPatID | PatID | ||
| MIL-INF_2_08_00-0_201 | Fail | Y | CPatID value present in MIL table, but not in the INF table | CPatID | CPatID | ||
| MIL-DEL_2_01_00-0_202 | Fail | Y | MPatID value present in DEL table, but not in the MIL table | MPatID | EncounterID | ||
| MIL-DEL_2_02_00-0_202 | Fail | Y | EncounterID value present in DEL table, but not in the MIL table | EncounterID | MPatID | ||
| MIL-INF_2_08_00-0_202 | Fail | Y | CPatID value present in INF table, but not in the MIL table | CPatID | CPatID | ||
| MIL-DEM_2_01_00-0_203 | Fail | Y | MPatID variable length is not consistent between tables | MPatID | PatID | ||
| MIL-ENC_2_02_00-0_203 | Fail | Y | EncounterID variable length is not consistent between tables | EncounterID | EncounterID | ||
| MIL-DEM_2_08_00-0_203 | Fail | Y | CPatID variable length is not consistent between tables | CPatID | PatID | ||
| MIL-DEM_2_04_00-0_208 | Fail | Y | Tables linked on (MPatID,PatID). Birth_Date is populated, but MBirth_Date not equal to Birth_Date in table DEM | MBirth_Date | Birth_Date | MPatID | PatID |
| MIL-ENC_2_06_00-0_208 | Fail | Y | Tables linked on (EncounterID,EncounterID). ADate is populated, but ADate not equal to ADate in table ENC | ADate | ADate | EncounterID | EncounterID |
| MIL-DEL_2_06_00-0_208 | Fail | Y | Tables linked on (EncounterID,EncounterID). ADate is populated, but ADate not equal to ADate in table DEL | ADate | ADate | EncounterID | EncounterID |
| MIL-ENC_2_07_00-0_208 | Fail | Y | Tables linked on (EncounterID,EncounterID). DDate is populated, but DDate not equal to DDate in table ENC | DDate | DDate | EncounterID | EncounterID |
| MIL-DEL_2_07_00-0_208 | Fail | Y | Tables linked on (EncounterID,EncounterID). DDate is populated, but DDate not equal to DDate in table DEL | DDate | DDate | EncounterID | EncounterID |
| MIL-DEM_2_09_00-0_208 | Fail | Y | Tables linked on (CPatID,PatID). Birth_Date is populated, but CBirth_Date not equal to Birth_Date in table DEM | CBirth_Date | Birth_Date | CPatID | PatID |
| MIL-INF_2_09_00-0_208 | Fail | Y | Tables linked on (CPatID,CPatID). CBirth_Date is populated, but CBirth_Date not equal to CBirth_Date in table INF | CBirth_Date | CBirth_Date | CPatID | CPatID |
| MIL-DEM_2_10_00-0_208 | Fail | Y | Tables linked on (CPatID,PatID). Sex is populated, but Sex not equal to Sex in table DEM | Sex | Sex | CPatID | PatID |
| MIL-INF_2_10_00-0_208 | Fail | Y | Tables linked on (CPatID,CPatID). Sex is populated, but Sex not equal to Sex in table INF | Sex | Sex | CPatID | CPatID |
| MIL_2_00_00-0_211 | Fail | Y | Duplicate record(s) present for unique key variable(s): MPatID, EncounterID, CPatID | ||||
| MIL_2_01_00-0_217 | Fail | Y | For non-linked records, rows are not distinct by key variables MPatID, MBirth_Date, ADate | MPatID | MBirth_Date | ADate | |
| MIL_2_01_00-0_218 | Fail | Y | For linked records, rows are not distinct by key variables MPatID, EncounterID, ADate, CPatID | MPatID | EncounterID | ADate | CPatID |
| MIL_2_01_00-0_218 | Fail | Y | For linked records, rows are not distinct by key variables MPatID, CPatID, NA, NA | MPatID | CPatID | ||
| MIL_2_01_00-0_219 | Fail | Y | For linked records, value of MPatID not consistently populated by row and combinations of key variables MPatID, EncounterID, EncType, NA | MPatID | EncounterID | EncType | |
| MIL_2_01_00-0_219 | Fail | Y | For linked records, value of MPatID not consistently populated by row and combinations of key variables MPatID, EncounterID, ADate, NA | MPatID | EncounterID | ADate | |
| MIL_2_01_00-0_219 | Fail | Y | For linked records, value of MPatID not consistently populated by row and combinations of key variables MPatID, ADate, MBirth_Date, NA | MPatID | ADate | MBirth_Date | |
| MIL_2_01_00-0_219 | Fail | Y | For linked records, value of MPatID not consistently populated by row and combinations of key variables MPatID, EncounterID, Birth_Type, NA | MPatID | EncounterID | Birth_Type | |
| MIL_2_01_00-0_219 | Fail | Y | For linked records, value of MPatID not consistently populated by row and combinations of key variables MPatID, EncounterID, ADate, MBirth_Date | MPatID | EncounterID | ADate | MBirth_Date |
| MIL_2_01_00-0_219 | Fail | Y | For linked records, value of MPatID not consistently populated by row and combinations of key variables MPatID, ADate, EncounterID, NA | MPatID | ADate | EncounterID | |
| MIL_2_01_00-0_219 | Fail | Y | For linked records, value of MPatID not consistently populated by row and combinations of key variables MPatID, ADate, Birth_Type, NA | MPatID | ADate | Birth_Type | |
| MIL_2_01_00-0_221 | Fail | Y | MPatID is populated but EncounterID = missing ( . or blank) | MPatID | EncounterID | ||
| MIL_2_01_00-0_221 | Fail | Y | MPatID is populated but Age = missing ( . or blank) | MPatID | Age | ||
| MIL_2_01_00-0_221 | Fail | Y | MPatID is populated but MBirth_Date = missing ( . or blank) | MPatID | MBirth_Date | ||
| MIL_2_04_00-0_221 | Fail | Y | MBirth_Date is populated but EncType = missing ( . or blank) | MBirth_Date | EncType | ||
| MIL_2_04_00-0_221 | Fail | Y | MBirth_Date is populated but Birth_Type = missing ( . or blank) | MBirth_Date | Birth_Type | ||
| MIL_2_04_00-0_254 | Fail | Y | If both variables are filled, then CBirth_Date must be later than MBirth_Date and by >= 10 years for linked records | MBirth_Date | CBirth_Date | ||
| MIL_2_06_00-0_255 | Warn | N | CBirth_Date must be between 3 days before the ADate through DDate; if DDate is null, then CBirth_Date must be between 3 days before, through 1 day after the ADate | ADate | CBirth_Date | DDate | |
| MIL_2_04_00-0_258 | Fail | Y | MBirth_Date value is not between the min and max, inclusive, of MBirth_Date in the Mothers delivery file | MBirth_Date | |||
| MIL_2_06_00-0_258 | Fail | Y | ADate value is not between the min and max, inclusive, of ADate in the Mothers delivery file | ADate | |||
| MIL_2_07_00-0_258 | Fail | Y | DDate value is not between the min and max, inclusive, of DDate in the Mothers delivery file | DDate | |||
| MIL_2_09_00-0_258 | Fail | Y | CBirth_Date value is not between the min and max, inclusive, of CBirth_Date in the Mothers delivery file | CBirth_Date | |||
| MIL_2_11_00-0_258 | Fail | Y | CEnr_Start value is not between the min and max, inclusive, of CEnr_Start in the Mothers delivery file | CEnr_Start | |||
| MIL_2_12_00-0_274 | Fail | Y | For linked records, MatchMethod contains invalid non-missing values | MatchMethod | |||
| MIL_2_12_00-0_275 | Fail | Y | For non-linked records, MatchMethod contains invalid non-missing values | MatchMethod | |||
| MIL_2_09_00-0_280 | Warn | N | If both variables are populated, the difference between CBirth_Date and ADate must be less than or equal to 180 days | CBirth_Date | ADate |
Return to Table of Contents
Enrollment Level 2 Quality Checks
| FlagID | FlagType | AbortYN | Flag_Descr |
|---|---|---|---|
| ENR-MIL_2_00_00-0_211 | Fail | Y | Duplicate record(s) present for unique key variable(s): PatID |
Return to Table of Contents
Demographic Level 2 Quality Checks
| FlagID | FlagType | AbortYN | Flag_Descr |
|---|---|---|---|
| DEM-MIL_2_00_00-0_211 | Fail | Y | Duplicate record(s) present for unique key variable(s): PatID |
Return to Table of Contents
Encounter Level 2 Quality Checks
| FlagID | FlagType | AbortYN | Flag_Descr |
|---|---|---|---|
| ENC-MIL_2_00_00-0_211 | Fail | Y | Duplicate record(s) present for unique key variable(s): PatID, EncounterID |
Return to Table of Contents
12.3 Level 3 Quality Checks
Level 3 data quality checks examine data distributions and trends over time, both within a Data Partner’s database (by examining output by year and year/month) and across a Data Partner’s databases (by comparing updated SCDM tables to previous versions of the tables).
| FlagID | FlagType | AbortYN | FlagDescr | Variable1 | Variable2 |
|---|---|---|---|---|---|
| MIL_3_00_00-0_370 | Warn | N | Birth_Type (0) not consistent with number of linkages; confirmation required | BirthType | |
| MIL_3_00_00-0_371 | Warn | N | Birth_Type (1) not consistent with number of linkages; confirmation required | BirthType | |
| MIL_3_00_00-0_372 | Warn | N | Birth_Type (2) not consistent with number of linkages; confirmation required | BirthType | |
| MIL_3_00_00-0_373 | Warn | N | Birth_Type (3) not consistent with number of linkages; confirmation required | BirthType | |
| MIL_3_00_00-0_374 | Warn | N | Birth_Type (4) not consistent with number of linkages; confirmation required | BirthType | |
| MIL_3_00_00-0_375 | Warn | N | Birth_Type (5) not consistent with number of linkages; confirmation required | BirthType | |
| MIL_3_00_00-0_376 | Warn | N | Birth_Type (6) not consistent with number of linkages; confirmation required | BirthType | |
| MIL_3_00_00-0_377 | Warn | N | Birth_Type (7) not consistent with number of linkages; confirmation required | BirthType | |
| MIL_3_00_00-0_378 | Warn | N | Birth_Type (8) not consistent with number of linkages; confirmation required | BirthType | |
| MIL_3_00_00-0_379 | Warn | N | Birth_Type (9) not consistent with number of linkages; confirmation required | BirthType | |
| MIL_00_00-0_394 | Warn | N | Birth_Type = 2–8 and no CPatIDs are linked | BirthType | |
| MIL_00_00-0_396 | Warn | N | MPatID not linked to CPatID | MPatID | CPatID |
| MIL_00_00-0_397 | Warn | N | CPatID not linked to MPatID | CPatID | MPatid |