mimic-code
mimic-code copied to clipboard
Published
20 hours ago •
MIT-LCP
MIT-LCP
Spaces in codes in `elixhauser_ahrq_v37_no_drg.sql`
Prerequisites
- [X] Put an X between the brackets on this line if you have done all of the following:
- Checked the online documentation: https://mimic.mit.edu/
- Checked that your issue isn't already addressed: https://github.com/MIT-LCP/mimic-code/issues?utf8=%E2%9C%93&q=
Description
I've noticed what seems to be a problem in elixhauser_ahrq_v37_no_drg.sql.
I think this is related to the now-closed issue #800 @alistairewj.
When running this code and comparing it with an older version that I had, I noticed a difference, in that:
- This section is no longer present:
icd as ( select hadm_id, seq_num , cast(icd9_code as char(5)) as icd9_code from diagnoses_icd ) , - Trailing spaces in shorter codes have been removed, e.g.:
is now:when icd9_code between '4280 ' and '4289 ' then 1when icd9_code between '4280' and '4289' then 1
This seems to lead to the "non-whitespaced" codes not being picked up in the query.
I have directly compared the results of these two queries:
As the code is now:
DROP MATERIALIZED VIEW IF EXISTS comorbidities_as_is CASCADE;
CREATE MATERIALIZED VIEW comorbidities_as_is as
-- This code uses the latest version of Elixhauser provided by AHRQ
-- However, it does *not* filter based on diagnosis related groups (DRGs)
-- As such, "comorbidities" identified are more likely to be associated with the primary reason for their hospital stay
-- The code:
-- removes "primary" ICD9_CODE (seq_num != 1)
-- uses AHRQ published rules to define comorbidities
with
eliflg as
(
select hadm_id, seq_num, icd9_code
-- note that these codes will seem incomplete at first
-- for example, CHF is missing a lot of codes referenced in the literature (402.11, 402.91, etc)
-- these codes are captured by hypertension flags instead
-- later there are some complicated rules which confirm/reject those codes as chf
, CASE
when icd9_code = '39891' then 1
when icd9_code between '4280' and '4289' then 1
end as chf /* Congestive heart failure */
-- cardiac arrhythmias is removed in up to date versions
, case
when icd9_code = '42610' then 1
when icd9_code = '42611' then 1
when icd9_code = '42613' then 1
when icd9_code between '4262' and '42653' then 1
when icd9_code between '4266' and '42689' then 1
when icd9_code = '4270' then 1
when icd9_code = '4272' then 1
when icd9_code = '42731' then 1
when icd9_code = '42760' then 1
when icd9_code = '4279' then 1
when icd9_code = '7850' then 1
when icd9_code between 'V450' and 'V4509' then 1
when icd9_code between 'V533' and 'V5339' then 1
end as arythm /* Cardiac arrhythmias */
, CASE
when icd9_code between '09320' and '09324' then 1
when icd9_code between '3940' and '3971' then 1
when icd9_code = '3979' then 1
when icd9_code between '4240' and '42499' then 1
when icd9_code between '7463' and '7466' then 1
when icd9_code = 'V422' then 1
when icd9_code = 'V433' then 1
end as valve /* Valvular disease */
, CASE
when icd9_code between '41511' and '41519' then 1
when icd9_code between '4160' and '4169' then 1
when icd9_code = '4179' then 1
end as pulmcirc /* Pulmonary circulation disorder */
, CASE
when icd9_code between '4400' and '4409' then 1
when icd9_code between '44100' and '4419' then 1
when icd9_code between '4420' and '4429' then 1
when icd9_code between '4431' and '4439' then 1
when icd9_code between '44421' and '44422' then 1
when icd9_code = '4471' then 1
when icd9_code = '449' then 1
when icd9_code = '5571' then 1
when icd9_code = '5579' then 1
when icd9_code = 'V434' then 1
end as perivasc /* Peripheral vascular disorder */
, CASE
when icd9_code = '4011' then 1
when icd9_code = '4019' then 1
when icd9_code between '64200' and '64204' then 1
end as htn /* Hypertension, uncomplicated */
, CASE
when icd9_code = '4010' then 1
when icd9_code = '4372' then 1
end as htncx /* Hypertension, complicated */
/******************************************************************/
/* The following are special, temporary formats used in the */
/* creation of the hypertension complicated comorbidity when */
/* overlapping with congestive heart failure or renal failure */
/* occurs. These temporary formats are referenced in the program */
/* called comoanaly2009.txt. */
/******************************************************************/
, CASE
when icd9_code between '64220' and '64224' then 1
end as htnpreg /* Pre-existing hypertension complicating pregnancy */
, CASE
when icd9_code = '40200' then 1
when icd9_code = '40210' then 1
when icd9_code = '40290' then 1
when icd9_code = '40509' then 1
when icd9_code = '40519' then 1
when icd9_code = '40599' then 1
end as htnwochf /* Hypertensive heart disease without heart failure */
, CASE
when icd9_code = '40201' then 1
when icd9_code = '40211' then 1
when icd9_code = '40291' then 1
end as htnwchf /* Hypertensive heart disease with heart failure */
, CASE
when icd9_code = '40300' then 1
when icd9_code = '40310' then 1
when icd9_code = '40390' then 1
when icd9_code = '40501' then 1
when icd9_code = '40511' then 1
when icd9_code = '40591' then 1
when icd9_code between '64210' and '64214' then 1
end as hrenworf /* Hypertensive renal disease without renal failure */
, CASE
when icd9_code = '40301' then 1
when icd9_code = '40311' then 1
when icd9_code = '40391' then 1
end as hrenwrf /* Hypertensive renal disease with renal failure */
, CASE
when icd9_code = '40400' then 1
when icd9_code = '40410' then 1
when icd9_code = '40490' then 1
end as hhrwohrf /* Hypertensive heart and renal disease without heart or renal failure */
, CASE
when icd9_code = '40401' then 1
when icd9_code = '40411' then 1
when icd9_code = '40491' then 1
end as hhrwchf /* Hypertensive heart and renal disease with heart failure */
, CASE
when icd9_code = '40402' then 1
when icd9_code = '40412' then 1
when icd9_code = '40492' then 1
end as hhrwrf /* Hypertensive heart and renal disease with renal failure */
, CASE
when icd9_code = '40403' then 1
when icd9_code = '40413' then 1
when icd9_code = '40493' then 1
end as hhrwhrf /* Hypertensive heart and renal disease with heart and renal failure */
, CASE
when icd9_code between '64270' and '64274' then 1
when icd9_code between '64290' and '64294' then 1
end as ohtnpreg /* Other hypertension in pregnancy */
/******************** End Temporary Formats ***********************/
, CASE
when icd9_code between '3420' and '3449' then 1
when icd9_code between '43820' and '43853' then 1
when icd9_code = '78072' then 1
end as para /* Paralysis */
, CASE
when icd9_code between '3300' and '3319' then 1
when icd9_code = '3320' then 1
when icd9_code = '3334' then 1
when icd9_code = '3335' then 1
when icd9_code = '3337' then 1
when icd9_code in ('33371','33372','33379','33385','33394') then 1
when icd9_code between '3340' and '3359' then 1
when icd9_code = '3380' then 1
when icd9_code = '340' then 1
when icd9_code between '3411' and '3419' then 1
when icd9_code between '34500' and '34511' then 1
when icd9_code between '3452' and '3453' then 1
when icd9_code between '34540' and '34591' then 1
when icd9_code between '34700' and '34701' then 1
when icd9_code between '34710' and '34711' then 1
when icd9_code = '3483' then 1 -- discontinued icd-9
when icd9_code between '64940' and '64944' then 1
when icd9_code = '7687' then 1
when icd9_code between '76870' and '76873' then 1
when icd9_code = '7803' then 1
when icd9_code = '78031' then 1
when icd9_code = '78032' then 1
when icd9_code = '78033' then 1
when icd9_code = '78039' then 1
when icd9_code = '78097' then 1
when icd9_code = '7843' then 1
end as neuro /* Other neurological */
, CASE
when icd9_code between '490' and '4928' then 1
when icd9_code between '49300' and '49392' then 1
when icd9_code between '494' and '4941' then 1
when icd9_code between '4950' and '505' then 1
when icd9_code = '5064' then 1
end as chrnlung /* Chronic pulmonary disease */
, CASE
when icd9_code between '25000' and '25033' then 1
when icd9_code between '64800' and '64804' then 1
when icd9_code between '24900' and '24931' then 1
end as dm /* Diabetes w/o chronic complications*/
, CASE
when icd9_code between '25040' and '25093' then 1
when icd9_code = '7751' then 1
when icd9_code between '24940' and '24991' then 1
end as dmcx /* Diabetes w/ chronic complications */
, CASE
when icd9_code between '243' and '2442' then 1
when icd9_code = '2448' then 1
when icd9_code = '2449' then 1
end as hypothy /* Hypothyroidism */
, CASE
when icd9_code = '585' then 1 -- discontinued code
when icd9_code = '5853' then 1
when icd9_code = '5854' then 1
when icd9_code = '5855' then 1
when icd9_code = '5856' then 1
when icd9_code = '5859' then 1
when icd9_code = '586' then 1
when icd9_code = 'V420' then 1
when icd9_code = 'V451' then 1
when icd9_code between 'V560' and 'V5632' then 1
when icd9_code = 'V568' then 1
when icd9_code between 'V4511' and 'V4512' then 1
end as renlfail /* Renal failure */
, CASE
when icd9_code = '07022' then 1
when icd9_code = '07023' then 1
when icd9_code = '07032' then 1
when icd9_code = '07033' then 1
when icd9_code = '07044' then 1
when icd9_code = '07054' then 1
when icd9_code = '4560' then 1
when icd9_code = '4561' then 1
when icd9_code = '45620' then 1
when icd9_code = '45621' then 1
when icd9_code = '5710' then 1
when icd9_code = '5712' then 1
when icd9_code = '5713' then 1
when icd9_code between '57140' and '57149' then 1
when icd9_code = '5715' then 1
when icd9_code = '5716' then 1
when icd9_code = '5718' then 1
when icd9_code = '5719' then 1
when icd9_code = '5723' then 1
when icd9_code = '5728' then 1
when icd9_code = '5735' then 1
when icd9_code = 'V427' then 1
end as liver /* Liver disease */
, CASE
when icd9_code = '53141' then 1
when icd9_code = '53151' then 1
when icd9_code = '53161' then 1
when icd9_code = '53170' then 1
when icd9_code = '53171' then 1
when icd9_code = '53191' then 1
when icd9_code = '53241' then 1
when icd9_code = '53251' then 1
when icd9_code = '53261' then 1
when icd9_code = '53270' then 1
when icd9_code = '53271' then 1
when icd9_code = '53291' then 1
when icd9_code = '53341' then 1
when icd9_code = '53351' then 1
when icd9_code = '53361' then 1
when icd9_code = '53370' then 1
when icd9_code = '53371' then 1
when icd9_code = '53391' then 1
when icd9_code = '53441' then 1
when icd9_code = '53451' then 1
when icd9_code = '53461' then 1
when icd9_code = '53470' then 1
when icd9_code = '53471' then 1
when icd9_code = '53491' then 1
end as ulcer /* Chronic Peptic ulcer disease (includes bleeding only if obstruction is also present) */
, CASE
when icd9_code between '042' and '0449' then 1
end as aids /* HIV and AIDS */
, CASE
when icd9_code between '20000' and '20238' then 1
when icd9_code between '20250' and '20301' then 1
when icd9_code = '2386' then 1
when icd9_code = '2733' then 1
when icd9_code between '20302' and '20382' then 1
end as lymph /* Lymphoma */
, CASE
when icd9_code between '1960' and '1991' then 1
when icd9_code between '20970' and '20975' then 1
when icd9_code = '20979' then 1
when icd9_code = '78951' then 1
end as mets /* Metastatic cancer */
, CASE
when icd9_code between '1400' and '1729' then 1
when icd9_code between '1740' and '1759' then 1
when icd9_code between '179' and '1958' then 1
when icd9_code between '20900' and '20924' then 1
when icd9_code between '20925' and '2093' then 1
when icd9_code between '20930' and '20936' then 1
when icd9_code between '25801' and '25803' then 1
end as tumor /* Solid tumor without metastasis */
, CASE
when icd9_code = '7010' then 1
when icd9_code between '7100' and '7109' then 1
when icd9_code between '7140' and '7149' then 1
when icd9_code between '7200' and '7209' then 1
when icd9_code = '725' then 1
end as arth /* Rheumatoid arthritis/collagen vascular diseases */
, CASE
when icd9_code between '2860' and '2869' then 1
when icd9_code = '2871' then 1
when icd9_code between '2873' and '2875' then 1
when icd9_code between '64930' and '64934' then 1
when icd9_code = '28984' then 1
end as coag /* Coagulation deficiency */
, CASE
when icd9_code = '2780' then 1
when icd9_code = '27800' then 1
when icd9_code = '27801' then 1
when icd9_code = '27803' then 1
when icd9_code between '64910' and '64914' then 1
when icd9_code between 'V8530' and 'V8539' then 1
when icd9_code = 'V854' then 1 -- hierarchy used for AHRQ v3.6 and earlier
when icd9_code between 'V8541' and 'V8545' then 1
when icd9_code = 'V8554' then 1
when icd9_code = '79391' then 1
end as obese /* Obesity */
, CASE
when icd9_code between '260' and '2639' then 1
when icd9_code between '78321' and '78322' then 1
end as wghtloss /* Weight loss */
, CASE
when icd9_code between '2760' and '2769' then 1
end as lytes /* Fluid and electrolyte disorders - note:
this comorbidity should be dropped when
used with the AHRQ Patient Safety Indicators*/
, CASE
when icd9_code = '2800' then 1
when icd9_code between '64820' and '64824' then 1
end as bldloss /* Blood loss anemia */
, CASE
when icd9_code between '2801' and '2819' then 1
when icd9_code between '28521' and '28529' then 1
when icd9_code = '2859' then 1
end as anemdef /* Deficiency anemias */
, CASE
when icd9_code between '2910' and '2913' then 1
when icd9_code = '2915' then 1
when icd9_code = '2918' then 1
when icd9_code = '29181' then 1
when icd9_code = '29182' then 1
when icd9_code = '29189' then 1
when icd9_code = '2919' then 1
when icd9_code between '30300' and '30393' then 1
when icd9_code between '30500' and '30503' then 1
end as alcohol /* Alcohol abuse */
, CASE
when icd9_code = '2920' then 1
when icd9_code between '29282' and '29289' then 1
when icd9_code = '2929' then 1
when icd9_code between '30400' and '30493' then 1
when icd9_code between '30520' and '30593' then 1
when icd9_code between '64830' and '64834' then 1
end as drug /* Drug abuse */
, CASE
when icd9_code between '29500' and '2989' then 1
when icd9_code = '29910' then 1
when icd9_code = '29911' then 1
end as psych /* Psychoses */
, CASE
when icd9_code = '3004' then 1
when icd9_code = '30112' then 1
when icd9_code = '3090' then 1
when icd9_code = '3091' then 1
when icd9_code = '311' then 1
end as depress /* Depression */
from diagnoses_icd icd
WHERE seq_num = 1
)
-- collapse the icd9_code specific flags into hadm_id specific flags
-- this groups comorbidities together for a single patient admission
, eligrp as
(
select hadm_id
, max(chf) as chf
, max(arythm) as arythm
, max(valve) as valve
, max(pulmcirc) as pulmcirc
, max(perivasc) as perivasc
, max(htn) as htn
, max(htncx) as htncx
, max(htnpreg) as htnpreg
, max(htnwochf) as htnwochf
, max(htnwchf) as htnwchf
, max(hrenworf) as hrenworf
, max(hrenwrf) as hrenwrf
, max(hhrwohrf) as hhrwohrf
, max(hhrwchf) as hhrwchf
, max(hhrwrf) as hhrwrf
, max(hhrwhrf) as hhrwhrf
, max(ohtnpreg) as ohtnpreg
, max(para) as para
, max(neuro) as neuro
, max(chrnlung) as chrnlung
, max(dm) as dm
, max(dmcx) as dmcx
, max(hypothy) as hypothy
, max(renlfail) as renlfail
, max(liver) as liver
, max(ulcer) as ulcer
, max(aids) as aids
, max(lymph) as lymph
, max(mets) as mets
, max(tumor) as tumor
, max(arth) as arth
, max(coag) as coag
, max(obese) as obese
, max(wghtloss) as wghtloss
, max(lytes) as lytes
, max(bldloss) as bldloss
, max(anemdef) as anemdef
, max(alcohol) as alcohol
, max(drug) as drug
, max(psych) as psych
, max(depress) as depress
from eliflg
group by hadm_id
)
-- now merge these flags together to define elixhauser
-- most are straightforward.. but hypertension flags are a bit more complicated
select adm.subject_id, adm.hadm_id
, case
when chf = 1 then 1
when htnwchf = 1 then 1
when hhrwchf = 1 then 1
when hhrwhrf = 1 then 1
else 0 end as congestive_heart_failure
, case
when arythm = 1 then 1
else 0 end as cardiac_arrhythmias
, case when valve = 1 then 1 else 0 end as valvular_disease
, case when pulmcirc = 1 then 1 else 0 end as pulmonary_circulation
, case when perivasc = 1 then 1 else 0 end as peripheral_vascular
-- we combine "htn" and "htncx" into "HYPERTENSION"
-- note "htn" (hypertension) is only 1 if "htncx" (complicated hypertension) is 0
-- this matters if you filter on DRG but for this query we can just merge them immediately
, case
when htn = 1 then 1
when htncx = 1 then 1
when htnpreg = 1 then 1
when htnwochf = 1 then 1
when htnwchf = 1 then 1
when hrenworf = 1 then 1
when hrenwrf = 1 then 1
when hhrwohrf = 1 then 1
when hhrwchf = 1 then 1
when hhrwrf = 1 then 1
when hhrwhrf = 1 then 1
when ohtnpreg = 1 then 1
else 0 end as hypertension
, case when para = 1 then 1 else 0 end as paralysis
, case when neuro = 1 then 1 else 0 end as other_neurological
, case when chrnlung = 1 then 1 else 0 end as chronic_pulmonary
, case
-- only the more severe comorbidity (complicated diabetes) is kept
when dmcx = 1 then 0
when dm = 1 then 1
else 0 end as diabetes_uncomplicated
, case when dmcx = 1 then 1 else 0 end as diabetes_complicated
, case when hypothy = 1 then 1 else 0 end as hypothyroidism
, case
when renlfail = 1 then 1
when hrenwrf = 1 then 1
when hhrwrf = 1 then 1
when hhrwhrf = 1 then 1
else 0 end as renal_failure
, case when liver = 1 then 1 else 0 end as liver_disease
, case when ulcer = 1 then 1 else 0 end as peptic_ulcer
, case when aids = 1 then 1 else 0 end as aids
, case when lymph = 1 then 1 else 0 end as lymphoma
, case when mets = 1 then 1 else 0 end as metastatic_cancer
, case
-- only the more severe comorbidity (metastatic cancer) is kept
when mets = 1 then 0
when tumor = 1 then 1
else 0 end as solid_tumor
, case when arth = 1 then 1 else 0 end as rheumatoid_arthritis
, case when coag = 1 then 1 else 0 end as coagulopathy
, case when obese = 1 then 1 else 0 end as obesity
, case when wghtloss = 1 then 1 else 0 end as weight_loss
, case when lytes = 1 then 1 else 0 end as fluid_electrolyte
, case when bldloss = 1 then 1 else 0 end as blood_loss_anemia
, case when anemdef = 1 then 1 else 0 end as deficiency_anemias
, case when alcohol = 1 then 1 else 0 end as alcohol_abuse
, case when drug = 1 then 1 else 0 end as drug_abuse
, case when psych = 1 then 1 else 0 end as psychoses
, case when depress = 1 then 1 else 0 end as depression
FROM admissions adm
left join eligrp eli
on adm.hadm_id = eli.hadm_id
order by adm.hadm_id;
As the code was previously:
DROP MATERIALIZED VIEW IF EXISTS comorbidities_old_way CASCADE;
CREATE MATERIALIZED VIEW comorbidities_old_way as
-- This code uses the latest version of Elixhauser provided by AHRQ
-- However, it does *not* filter based on diagnosis related groups (DRGs)
-- As such, "comorbidities" identified are more likely to be associated with the primary reason for their hospital stay
-- The code:
-- removes "primary" ICD9_CODE (seq_num != 1)
-- uses AHRQ published rules to define comorbidities
with
icd as
(
select hadm_id, seq_num
, cast(icd9_code as char(5)) as icd9_code
from diagnoses_icd
)
,
eliflg as
(
select hadm_id, seq_num, icd9_code
-- note that these codes will seem incomplete at first
-- for example, CHF is missing a lot of codes referenced in the literature (402.11, 402.91, etc)
-- these codes are captured by hypertension flags instead
-- later there are some complicated rules which confirm/reject those codes as chf
, CASE
when icd9_code = '39891' then 1
when icd9_code between '4280 ' and '4289 ' then 1
end as chf /* Congestive heart failure */
-- cardiac arrhythmias is removed in up to date versions
, case
when icd9_code = '42610' then 1
when icd9_code = '42611' then 1
when icd9_code = '42613' then 1
when icd9_code between '4262 ' and '42653' then 1
when icd9_code between '4266 ' and '42689' then 1
when icd9_code = '4270 ' then 1
when icd9_code = '4272 ' then 1
when icd9_code = '42731' then 1
when icd9_code = '42760' then 1
when icd9_code = '4279 ' then 1
when icd9_code = '7850 ' then 1
when icd9_code between 'V450 ' and 'V4509' then 1
when icd9_code between 'V533 ' and 'V5339' then 1
end as arythm /* Cardiac arrhythmias */
, CASE
when icd9_code between '09320' and '09324' then 1
when icd9_code between '3940 ' and '3971 ' then 1
when icd9_code = '3979 ' then 1
when icd9_code between '4240 ' and '42499' then 1
when icd9_code between '7463 ' and '7466 ' then 1
when icd9_code = 'V422 ' then 1
when icd9_code = 'V433 ' then 1
end as valve /* Valvular disease */
, CASE
when icd9_code between '41511' and '41519' then 1
when icd9_code between '4160 ' and '4169 ' then 1
when icd9_code = '4179 ' then 1
end as pulmcirc /* Pulmonary circulation disorder */
, CASE
when icd9_code between '4400 ' and '4409 ' then 1
when icd9_code between '44100' and '4419 ' then 1
when icd9_code between '4420 ' and '4429 ' then 1
when icd9_code between '4431 ' and '4439 ' then 1
when icd9_code between '44421' and '44422' then 1
when icd9_code = '4471 ' then 1
when icd9_code = '449 ' then 1
when icd9_code = '5571 ' then 1
when icd9_code = '5579 ' then 1
when icd9_code = 'V434 ' then 1
end as perivasc /* Peripheral vascular disorder */
, CASE
when icd9_code = '4011 ' then 1
when icd9_code = '4019 ' then 1
when icd9_code between '64200' and '64204' then 1
end as htn /* Hypertension, uncomplicated */
, CASE
when icd9_code = '4010 ' then 1
when icd9_code = '4372 ' then 1
end as htncx /* Hypertension, complicated */
/******************************************************************/
/* The following are special, temporary formats used in the */
/* creation of the hypertension complicated comorbidity when */
/* overlapping with congestive heart failure or renal failure */
/* occurs. These temporary formats are referenced in the program */
/* called comoanaly2009.txt. */
/******************************************************************/
, CASE
when icd9_code between '64220' and '64224' then 1
end as htnpreg /* Pre-existing hypertension complicating pregnancy */
, CASE
when icd9_code = '40200' then 1
when icd9_code = '40210' then 1
when icd9_code = '40290' then 1
when icd9_code = '40509' then 1
when icd9_code = '40519' then 1
when icd9_code = '40599' then 1
end as htnwochf /* Hypertensive heart disease without heart failure */
, CASE
when icd9_code = '40201' then 1
when icd9_code = '40211' then 1
when icd9_code = '40291' then 1
end as htnwchf /* Hypertensive heart disease with heart failure */
, CASE
when icd9_code = '40300' then 1
when icd9_code = '40310' then 1
when icd9_code = '40390' then 1
when icd9_code = '40501' then 1
when icd9_code = '40511' then 1
when icd9_code = '40591' then 1
when icd9_code between '64210' and '64214' then 1
end as hrenworf /* Hypertensive renal disease without renal failure */
, CASE
when icd9_code = '40301' then 1
when icd9_code = '40311' then 1
when icd9_code = '40391' then 1
end as hrenwrf /* Hypertensive renal disease with renal failure */
, CASE
when icd9_code = '40400' then 1
when icd9_code = '40410' then 1
when icd9_code = '40490' then 1
end as hhrwohrf /* Hypertensive heart and renal disease without heart or renal failure */
, CASE
when icd9_code = '40401' then 1
when icd9_code = '40411' then 1
when icd9_code = '40491' then 1
end as hhrwchf /* Hypertensive heart and renal disease with heart failure */
, CASE
when icd9_code = '40402' then 1
when icd9_code = '40412' then 1
when icd9_code = '40492' then 1
end as hhrwrf /* Hypertensive heart and renal disease with renal failure */
, CASE
when icd9_code = '40403' then 1
when icd9_code = '40413' then 1
when icd9_code = '40493' then 1
end as hhrwhrf /* Hypertensive heart and renal disease with heart and renal failure */
, CASE
when icd9_code between '64270' and '64274' then 1
when icd9_code between '64290' and '64294' then 1
end as ohtnpreg /* Other hypertension in pregnancy */
/******************** End Temporary Formats ***********************/
, CASE
when icd9_code between '3420 ' and '3449 ' then 1
when icd9_code between '43820' and '43853' then 1
when icd9_code = '78072' then 1
end as para /* Paralysis */
, CASE
when icd9_code between '3300 ' and '3319 ' then 1
when icd9_code = '3320 ' then 1
when icd9_code = '3334 ' then 1
when icd9_code = '3335 ' then 1
when icd9_code = '3337 ' then 1
when icd9_code in ('33371','33372','33379','33385','33394') then 1
when icd9_code between '3340 ' and '3359 ' then 1
when icd9_code = '3380 ' then 1
when icd9_code = '340 ' then 1
when icd9_code between '3411 ' and '3419 ' then 1
when icd9_code between '34500' and '34511' then 1
when icd9_code between '3452 ' and '3453 ' then 1
when icd9_code between '34540' and '34591' then 1
when icd9_code between '34700' and '34701' then 1
when icd9_code between '34710' and '34711' then 1
when icd9_code = '3483' then 1 -- discontinued icd-9
when icd9_code between '64940' and '64944' then 1
when icd9_code = '7687 ' then 1
when icd9_code between '76870' and '76873' then 1
when icd9_code = '7803 ' then 1
when icd9_code = '78031' then 1
when icd9_code = '78032' then 1
when icd9_code = '78033' then 1
when icd9_code = '78039' then 1
when icd9_code = '78097' then 1
when icd9_code = '7843 ' then 1
end as neuro /* Other neurological */
, CASE
when icd9_code between '490 ' and '4928 ' then 1
when icd9_code between '49300' and '49392' then 1
when icd9_code between '494 ' and '4941 ' then 1
when icd9_code between '4950 ' and '505 ' then 1
when icd9_code = '5064 ' then 1
end as chrnlung /* Chronic pulmonary disease */
, CASE
when icd9_code between '25000' and '25033' then 1
when icd9_code between '64800' and '64804' then 1
when icd9_code between '24900' and '24931' then 1
end as dm /* Diabetes w/o chronic complications*/
, CASE
when icd9_code between '25040' and '25093' then 1
when icd9_code = '7751 ' then 1
when icd9_code between '24940' and '24991' then 1
end as dmcx /* Diabetes w/ chronic complications */
, CASE
when icd9_code between '243 ' and '2442 ' then 1
when icd9_code = '2448 ' then 1
when icd9_code = '2449 ' then 1
end as hypothy /* Hypothyroidism */
, CASE
when icd9_code = '585 ' then 1 -- discontinued code
when icd9_code = '5853 ' then 1
when icd9_code = '5854 ' then 1
when icd9_code = '5855 ' then 1
when icd9_code = '5856 ' then 1
when icd9_code = '5859 ' then 1
when icd9_code = '586 ' then 1
when icd9_code = 'V420 ' then 1
when icd9_code = 'V451 ' then 1
when icd9_code between 'V560 ' and 'V5632' then 1
when icd9_code = 'V568 ' then 1
when icd9_code between 'V4511' and 'V4512' then 1
end as renlfail /* Renal failure */
, CASE
when icd9_code = '07022' then 1
when icd9_code = '07023' then 1
when icd9_code = '07032' then 1
when icd9_code = '07033' then 1
when icd9_code = '07044' then 1
when icd9_code = '07054' then 1
when icd9_code = '4560 ' then 1
when icd9_code = '4561 ' then 1
when icd9_code = '45620' then 1
when icd9_code = '45621' then 1
when icd9_code = '5710 ' then 1
when icd9_code = '5712 ' then 1
when icd9_code = '5713 ' then 1
when icd9_code between '57140' and '57149' then 1
when icd9_code = '5715 ' then 1
when icd9_code = '5716 ' then 1
when icd9_code = '5718 ' then 1
when icd9_code = '5719 ' then 1
when icd9_code = '5723 ' then 1
when icd9_code = '5728 ' then 1
when icd9_code = '5735 ' then 1
when icd9_code = 'V427 ' then 1
end as liver /* Liver disease */
, CASE
when icd9_code = '53141' then 1
when icd9_code = '53151' then 1
when icd9_code = '53161' then 1
when icd9_code = '53170' then 1
when icd9_code = '53171' then 1
when icd9_code = '53191' then 1
when icd9_code = '53241' then 1
when icd9_code = '53251' then 1
when icd9_code = '53261' then 1
when icd9_code = '53270' then 1
when icd9_code = '53271' then 1
when icd9_code = '53291' then 1
when icd9_code = '53341' then 1
when icd9_code = '53351' then 1
when icd9_code = '53361' then 1
when icd9_code = '53370' then 1
when icd9_code = '53371' then 1
when icd9_code = '53391' then 1
when icd9_code = '53441' then 1
when icd9_code = '53451' then 1
when icd9_code = '53461' then 1
when icd9_code = '53470' then 1
when icd9_code = '53471' then 1
when icd9_code = '53491' then 1
end as ulcer /* Chronic Peptic ulcer disease (includes bleeding only if obstruction is also present) */
, CASE
when icd9_code between '042 ' and '0449 ' then 1
end as aids /* HIV and AIDS */
, CASE
when icd9_code between '20000' and '20238' then 1
when icd9_code between '20250' and '20301' then 1
when icd9_code = '2386 ' then 1
when icd9_code = '2733 ' then 1
when icd9_code between '20302' and '20382' then 1
end as lymph /* Lymphoma */
, CASE
when icd9_code between '1960 ' and '1991 ' then 1
when icd9_code between '20970' and '20975' then 1
when icd9_code = '20979' then 1
when icd9_code = '78951' then 1
end as mets /* Metastatic cancer */
, CASE
when icd9_code between '1400 ' and '1729 ' then 1
when icd9_code between '1740 ' and '1759 ' then 1
when icd9_code between '179 ' and '1958 ' then 1
when icd9_code between '20900' and '20924' then 1
when icd9_code between '20925' and '2093 ' then 1
when icd9_code between '20930' and '20936' then 1
when icd9_code between '25801' and '25803' then 1
end as tumor /* Solid tumor without metastasis */
, CASE
when icd9_code = '7010 ' then 1
when icd9_code between '7100 ' and '7109 ' then 1
when icd9_code between '7140 ' and '7149 ' then 1
when icd9_code between '7200 ' and '7209 ' then 1
when icd9_code = '725 ' then 1
end as arth /* Rheumatoid arthritis/collagen vascular diseases */
, CASE
when icd9_code between '2860 ' and '2869 ' then 1
when icd9_code = '2871 ' then 1
when icd9_code between '2873 ' and '2875 ' then 1
when icd9_code between '64930' and '64934' then 1
when icd9_code = '28984' then 1
end as coag /* Coagulation deficiency */
, CASE
when icd9_code = '2780 ' then 1
when icd9_code = '27800' then 1
when icd9_code = '27801' then 1
when icd9_code = '27803' then 1
when icd9_code between '64910' and '64914' then 1
when icd9_code between 'V8530' and 'V8539' then 1
when icd9_code = 'V854 ' then 1 -- hierarchy used for AHRQ v3.6 and earlier
when icd9_code between 'V8541' and 'V8545' then 1
when icd9_code = 'V8554' then 1
when icd9_code = '79391' then 1
end as obese /* Obesity */
, CASE
when icd9_code between '260 ' and '2639 ' then 1
when icd9_code between '78321' and '78322' then 1
end as wghtloss /* Weight loss */
, CASE
when icd9_code between '2760 ' and '2769 ' then 1
end as lytes /* Fluid and electrolyte disorders - note:
this comorbidity should be dropped when
used with the AHRQ Patient Safety Indicators*/
, CASE
when icd9_code = '2800 ' then 1
when icd9_code between '64820' and '64824' then 1
end as bldloss /* Blood loss anemia */
, CASE
when icd9_code between '2801 ' and '2819 ' then 1
when icd9_code between '28521' and '28529' then 1
when icd9_code = '2859 ' then 1
end as anemdef /* Deficiency anemias */
, CASE
when icd9_code between '2910 ' and '2913 ' then 1
when icd9_code = '2915 ' then 1
when icd9_code = '2918 ' then 1
when icd9_code = '29181' then 1
when icd9_code = '29182' then 1
when icd9_code = '29189' then 1
when icd9_code = '2919 ' then 1
when icd9_code between '30300' and '30393' then 1
when icd9_code between '30500' and '30503' then 1
end as alcohol /* Alcohol abuse */
, CASE
when icd9_code = '2920 ' then 1
when icd9_code between '29282' and '29289' then 1
when icd9_code = '2929 ' then 1
when icd9_code between '30400' and '30493' then 1
when icd9_code between '30520' and '30593' then 1
when icd9_code between '64830' and '64834' then 1
end as drug /* Drug abuse */
, CASE
when icd9_code between '29500' and '2989 ' then 1
when icd9_code = '29910' then 1
when icd9_code = '29911' then 1
end as psych /* Psychoses */
, CASE
when icd9_code = '3004 ' then 1
when icd9_code = '30112' then 1
when icd9_code = '3090 ' then 1
when icd9_code = '3091 ' then 1
when icd9_code = '311 ' then 1
end as depress /* Depression */
from diagnoses_icd icd
WHERE seq_num = 1
)
-- collapse the icd9_code specific flags into hadm_id specific flags
-- this groups comorbidities together for a single patient admission
, eligrp as
(
select hadm_id
, max(chf) as chf
, max(arythm) as arythm
, max(valve) as valve
, max(pulmcirc) as pulmcirc
, max(perivasc) as perivasc
, max(htn) as htn
, max(htncx) as htncx
, max(htnpreg) as htnpreg
, max(htnwochf) as htnwochf
, max(htnwchf) as htnwchf
, max(hrenworf) as hrenworf
, max(hrenwrf) as hrenwrf
, max(hhrwohrf) as hhrwohrf
, max(hhrwchf) as hhrwchf
, max(hhrwrf) as hhrwrf
, max(hhrwhrf) as hhrwhrf
, max(ohtnpreg) as ohtnpreg
, max(para) as para
, max(neuro) as neuro
, max(chrnlung) as chrnlung
, max(dm) as dm
, max(dmcx) as dmcx
, max(hypothy) as hypothy
, max(renlfail) as renlfail
, max(liver) as liver
, max(ulcer) as ulcer
, max(aids) as aids
, max(lymph) as lymph
, max(mets) as mets
, max(tumor) as tumor
, max(arth) as arth
, max(coag) as coag
, max(obese) as obese
, max(wghtloss) as wghtloss
, max(lytes) as lytes
, max(bldloss) as bldloss
, max(anemdef) as anemdef
, max(alcohol) as alcohol
, max(drug) as drug
, max(psych) as psych
, max(depress) as depress
from eliflg
group by hadm_id
)
-- now merge these flags together to define elixhauser
-- most are straightforward.. but hypertension flags are a bit more complicated
select adm.subject_id, adm.hadm_id
, case
when chf = 1 then 1
when htnwchf = 1 then 1
when hhrwchf = 1 then 1
when hhrwhrf = 1 then 1
else 0 end as congestive_heart_failure
, case
when arythm = 1 then 1
else 0 end as cardiac_arrhythmias
, case when valve = 1 then 1 else 0 end as valvular_disease
, case when pulmcirc = 1 then 1 else 0 end as pulmonary_circulation
, case when perivasc = 1 then 1 else 0 end as peripheral_vascular
-- we combine "htn" and "htncx" into "HYPERTENSION"
-- note "htn" (hypertension) is only 1 if "htncx" (complicated hypertension) is 0
-- this matters if you filter on DRG but for this query we can just merge them immediately
, case
when htn = 1 then 1
when htncx = 1 then 1
when htnpreg = 1 then 1
when htnwochf = 1 then 1
when htnwchf = 1 then 1
when hrenworf = 1 then 1
when hrenwrf = 1 then 1
when hhrwohrf = 1 then 1
when hhrwchf = 1 then 1
when hhrwrf = 1 then 1
when hhrwhrf = 1 then 1
when ohtnpreg = 1 then 1
else 0 end as hypertension
, case when para = 1 then 1 else 0 end as paralysis
, case when neuro = 1 then 1 else 0 end as other_neurological
, case when chrnlung = 1 then 1 else 0 end as chronic_pulmonary
, case
-- only the more severe comorbidity (complicated diabetes) is kept
when dmcx = 1 then 0
when dm = 1 then 1
else 0 end as diabetes_uncomplicated
, case when dmcx = 1 then 1 else 0 end as diabetes_complicated
, case when hypothy = 1 then 1 else 0 end as hypothyroidism
, case
when renlfail = 1 then 1
when hrenwrf = 1 then 1
when hhrwrf = 1 then 1
when hhrwhrf = 1 then 1
else 0 end as renal_failure
, case when liver = 1 then 1 else 0 end as liver_disease
, case when ulcer = 1 then 1 else 0 end as peptic_ulcer
, case when aids = 1 then 1 else 0 end as aids
, case when lymph = 1 then 1 else 0 end as lymphoma
, case when mets = 1 then 1 else 0 end as metastatic_cancer
, case
-- only the more severe comorbidity (metastatic cancer) is kept
when mets = 1 then 0
when tumor = 1 then 1
else 0 end as solid_tumor
, case when arth = 1 then 1 else 0 end as rheumatoid_arthritis
, case when coag = 1 then 1 else 0 end as coagulopathy
, case when obese = 1 then 1 else 0 end as obesity
, case when wghtloss = 1 then 1 else 0 end as weight_loss
, case when lytes = 1 then 1 else 0 end as fluid_electrolyte
, case when bldloss = 1 then 1 else 0 end as blood_loss_anemia
, case when anemdef = 1 then 1 else 0 end as deficiency_anemias
, case when alcohol = 1 then 1 else 0 end as alcohol_abuse
, case when drug = 1 then 1 else 0 end as drug_abuse
, case when psych = 1 then 1 else 0 end as psychoses
, case when depress = 1 then 1 else 0 end as depression
FROM admissions adm
left join eligrp eli
on adm.hadm_id = eli.hadm_id
order by adm.hadm_id;
Then running a the following query on the two views created for comparison:
-- Expect alcohol_abuse to be affected as there are spaces in relevant codes.
SELECT SUM(alcohol_abuse) FROM mimiciii.comorbidities_as_is;
SELECT SUM(alcohol_abuse) FROM mimiciii.comorbidities_old_way;
-- Expect peptic_ulcer to not be affected as there are no spaces in relevant codes.
SELECT SUM(peptic_ulcer) FROM mimiciii.comorbidities_as_is;
SELECT SUM(peptic_ulcer) FROM mimiciii.comorbidities_old_way;
I get the results, correspondingly:
1 row returned
| _ | sum |
|---|---|
| 1 | 408 |
1 row returned
| _ | sum |
|---|---|
| 1 | 329 |
1 row returned
| _ | sum |
|---|---|
| 1 | 10 |
1 row returned
| _ | sum |
|---|---|
| 1 | 10 |
Hence, looks like 79 examples aren't picked up in the former case. Similar discrepancies happen across many other comorbidities, presumably because of the space character issue.
