Missing ICD9 codes from d_icd_diagnoses (that are in diagnoses_icd)

[mghassem]

There are some icd9_codes in diagnoses_icd are not in d_icd_diagnoses (144 out of 6985) and things in procedures_icd are not in d_icd_procedures (16 out of 2032).

SELECT count(t1.icd9_code)
FROM (select distinct icd9_code from diagnoses_icd) t1
LEFT JOIN d_icd_diagnoses t2
ON t2.icd9_code = t1.icd9_code
WHERE t2.icd9_code IS NULL;

count

144

If you look at one of those codes from diagnoses_icd:
mimic=# select * from diagnoses_icd where icd9_code = '6009';
row_id | subject_id | hadm_id | seq_num | icd9_code
--------+------------+---------+---------+-----------
256059 | 22983 | 138085 | 8 | 6009
(1 row)

mimic=# select * from d_icd_diagnoses where icd9_code = '6009';
row_id | icd9_code | short_title | long_title
--------+-----------+-------------+------------
(0 rows)

[tompollard]

Thanks for highlighting this @mghassem.

The dictionaries (d_icd_diagnoses and d_icd_procedures) were sourced from: https://www.cms.gov/Medicare/Coding/ICD9ProviderDiagnosticCodes/codes.html.

A direct link to the dictionaries is: https://www.cms.gov/Medicare/Coding/ICD9ProviderDiagnosticCodes/Downloads/ICD-9-CM-v32-master-descriptions.zip

We'll investigate why some of the codes don't appear in them.