<commit_before>-- This code uses the latest version of Elixhauser provided by AHRQ


-- first, we convert ICD9_CODE from a VARCHAR to a CHAR(5)
-- this is the format used in the SAS code provided by the AHRQ
-- it makes converting their code to SQL much simpler as we can directly use their syntax

-- also note, that we *cannot* skip this step and use a varchar here
-- why? well, for example, VALVE is coded as BETWEEN '4240 ' and '42499'
-- if we used a varchar, then '4240' *is not* between this range
-- but if we use a char(5), then '4240' *is* between this range
-- and we would like the latter behavior
-- it's possible removing the whitespaces would fix this - but I didn't test it.
-- I prefer consistency with AHRQ.
CREATE MATERIALIZED VIEW ELIXHAUSER_AHRQ as
with
icd as
(
  select hadm_id, subject_id, seq_num
    , cast(icd9_code as char(5)) as icd9_code
  from mimiciii.diagnoses_icd
  where seq_num != 1 -- we do not include the primary icd-9 code
)
,
eliflg as
(
select hadm_id, subject_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<commit_msg>drop view. ref #153<commit_after>-- This code uses the latest version of Elixhauser provided by AHRQ


-- first, we convert ICD9_CODE from a VARCHAR to a CHAR(5)
-- this is the format used in the SAS code provided by the AHRQ
-- it makes converting their code to SQL much simpler as we can directly use their syntax

-- also note, that we *cannot* skip this step and use a varchar here
-- why? well, for example, VALVE is coded as BETWEEN '4240 ' and '42499'
-- if we used a varchar, then '4240' *is not* between this range
-- but if we use a char(5), then '4240' *is* between this range
-- and we would like the latter behavior
-- it's possible removing the whitespaces would fix this - but I didn't test it.
-- I prefer consistency with AHRQ.

DROP MATERIALIZED VIEW IF EXISTS ELIXHAUSER_AHRQ CASCADE;
CREATE MATERIALIZED VIEW ELIXHAUSER_AHRQ as
with
icd as
(
  select hadm_id, subject_id, seq_num
    , cast(icd9_code as char(5)) as icd9_code
  from mimiciii.diagnoses_icd
  where seq_num != 1 -- we do not include the primary icd-9 code
)
,
eliflg as
(
select hadm_id, subject_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
{'fraction_non_alphanumeric': 0.07021791767554479, 'fraction_numerical': 0.058918482647296204, 'mean_word_length': 3.410438908659549, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 3, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '5102901', 'n_tokens_mistral': 1356, 'n_tokens_neox': 1184, 'n_words': 567}