11 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.
11.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_01_00-0_112 | Fail | Y | MPatID variable type does not conform to specification | MPatID |
| MIL_1_02_00-0_110 | Fail | Y | EncounterID variable does not exist | EncounterID |
| MIL_1_02_00-0_112 | Fail | Y | EncounterID variable type does not conform to specification | EncounterID |
| MIL_1_03_00-0_110 | Fail | Y | Age variable does not exist | Age |
| MIL_1_03_00-0_112 | Fail | Y | Age variable type does not conform to specification | Age |
| MIL_1_03_00-0_113 | Fail | Y | Age variable length does not conform to specifications | Age |
| MIL_1_03_00-0_126 | Fail | Y | Age variable contains an invalid, out of range, non-missing value | Age |
| MIL_1_04_00-0_110 | Fail | Y | MBirth_Date variable does not exist | MBirth_Date |
| MIL_1_04_00-0_112 | Fail | Y | MBirth_Date variable type does not conform to specification | MBirth_Date |
| MIL_1_04_00-0_113 | Fail | Y | MBirth_Date variable length does not conform to specifications | MBirth_Date |
| MIL_1_05_00-0_110 | Fail | Y | EncType variable does not exist | EncType |
| MIL_1_05_00-0_112 | Fail | Y | EncType variable type does not conform to specification | EncType |
| MIL_1_05_00-0_113 | Fail | Y | EncType variable length does not conform to specifications | EncType |
| MIL_1_05_00-0_121 | Fail | Y | EncType variable contains an invalid non-missing value | EncType |
| MIL_1_06_00-0_110 | Fail | Y | ADate variable does not exist | ADate |
| MIL_1_06_00-0_112 | Fail | Y | ADate variable type does not conform to specification | ADate |
| MIL_1_06_00-0_113 | Fail | Y | ADate variable length does not conform to specifications | ADate |
| MIL_1_07_00-0_110 | Fail | Y | DDate variable does not exist | DDate |
| MIL_1_07_00-0_112 | Fail | Y | DDate variable type does not conform to specification | DDate |
| MIL_1_07_00-0_113 | Fail | Y | DDate variable length does not conform to specifications | DDate |
| MIL_1_08_00-0_110 | Fail | Y | CPatID variable does not exist | CPatID |
| MIL_1_08_00-0_112 | Fail | Y | CPatID variable type does not conform to specification | CPatID |
| MIL_1_09_00-0_110 | Fail | Y | CBirth_Date variable does not exist | CBirth_Date |
| MIL_1_09_00-0_112 | Fail | Y | CBirth_Date variable type does not conform to specification | CBirth_Date |
| MIL_1_09_00-0_113 | Fail | Y | CBirth_Date variable length does not conform to specifications | CBirth_Date |
| MIL_1_10_00-0_110 | Fail | Y | Sex variable does not exist | Sex |
| MIL_1_10_00-0_112 | Fail | Y | Sex variable type does not conform to specification | Sex |
| MIL_1_10_00-0_113 | Fail | Y | Sex variable length does not conform to specifications | Sex |
| MIL_1_10_00-0_121 | Fail | Y | Sex variable contains an invalid non-missing value | Sex |
| MIL_1_11_00-0_110 | Fail | Y | CEnr_Start variable does not exist | CEnr_Start |
| MIL_1_11_00-0_112 | Fail | Y | CEnr_Start variable type does not conform to specification | CEnr_Start |
| MIL_1_11_00-0_113 | Fail | Y | CEnr_Start variable length does not conform to specifications | CEnr_Start |
| MIL_1_12_00-0_110 | Fail | Y | MatchMethod variable does not exist | MatchMethod |
| MIL_1_12_00-0_112 | Fail | Y | MatchMethod variable type does not conform to specification | MatchMethod |
| MIL_1_12_00-0_113 | Fail | Y | MatchMethod variable length does not conform to specifications | MatchMethod |
| MIL_1_12_00-0_121 | Fail | Y | MatchMethod variable contains an invalid non-missing value | MatchMethod |
| MIL_1_13_00-0_110 | Fail | Y | Birth_Type variable does not exist | Birth_Type |
| MIL_1_13_00-0_112 | Fail | Y | Birth_Type variable type does not conform to specification | Birth_Type |
| MIL_1_13_00-0_113 | Fail | Y | Birth_Type variable length does not conform to specifications | Birth_Type |
| MIL_1_13_00-0_121 | Fail | Y | Birth_Type variable contains an invalid non-missing value | Birth_Type |
| MIL_1_14_00-0_110 | Fail | Y | Birth_Type_Primes variable does not exist | Birth_Type_Primes |
| MIL_1_14_00-0_112 | Fail | Y | Birth_Type_Primes variable type does not conform to specification | Birth_Type_Primes |
| MIL_1_14_00-0_113 | Fail | Y | Birth_Type_Primes variable length does not conform to specifications | Birth_Type_Primes |
| MIL_1_14_00-0_121 | Fail | Y | Birth_Type_Primes variable contains an invalid non-missing value | Birth_Type_Primes |
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
11.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.
11.2.1 Table of Contents
Jump to: Mother-Infant Linkage | NA | NA | NA |
Mother-Infant Linkage Level 2 Quality Checks
| FlagID | FlagType | AbortYN | Flag_Descr | Variable1 | Variable2 | Variable3 | Variable4 |
|---|---|---|---|---|---|---|---|
| MIL_2_00_00-0_211 | Fail | Y | Duplicate record(s) present for unique key variable(s): MPatID, EncounterID, CPatID | ||||
| 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-ENR_2_01_00-0_201 | Fail | Y | MPatID value present in MIL table, but not in the ENR table | MPatID | PatID | ||
| MIL-DEL_2_01_00-0_202 | Fail | Y | MPatID value present in DEL table, but not in the MIL table | MPatID | EncounterID | ||
| MIL-DEM_2_01_00-0_203 | Fail | Y | MPatID variable length is not consistent between tables | MPatID | PatID | ||
| 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, 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, 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, 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-DEL_2_02_00-0_201 | Fail | Y | EncounterID value present in MIL table, but not in the DEL table | EncounterID | MPatID | ||
| 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_202 | Fail | Y | EncounterID value present in DEL table, but not in the MIL table | EncounterID | MPatID | ||
| MIL-ENC_2_02_00-0_203 | Fail | Y | EncounterID variable length is not consistent between tables | EncounterID | EncounterID | ||
| 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_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_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-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_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_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_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-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-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_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-DEM_2_08_00-0_201 | Fail | Y | CPatID value present in MIL table, but not in the DEM table | CPatID | PatID | ||
| MIL-ENR_2_08_00-0_201 | Fail | Y | CPatID value present in MIL table, but not in the ENR 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-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_08_00-0_203 | Fail | Y | CPatID variable length is not consistent between tables | CPatID | PatID | ||
| 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_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_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 | ||
| 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_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 |
Return to Table of Contents
NA 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
NA 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
NA 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
11.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 |