I wonder how to map SUBJECT_ID and HADM_ID of MIMIC-II with SUBJECT_ID and HADM_ID of MIMIC-III.

3 Answers 3


The SUBJECT_IDs in MIMIC-II are the same as SUBJECT_IDs in MIMIC-III.

HADM_IDs are different because MIMIC-II used an ICU database to define hospital admission and discharge (and only had dates), whereas MIMIC-III uses a hospital database (and has dates and times). You could create an approximate mapping, but since there will be erroneous entries in both databases which won't line up, it's probably more effort than it's worth.


Linking data between MIMIC-II v2.6 and MIMIC-III v1.3 on subject_id and first hospital admission confirms that the subject_ids have been retained:

first_adm_mimic2 as (
    select p2.subject_id subject_id_m2,
           p2.sex gender_m2,
           p2.dob dob_m2, 
           a2.admit_dt admittime_m2,
           row_number() over (partition by a2.subject_id order by a2.admit_dt) 
                as hadm_order_m2
    from mimic2v26.d_patients p2
    inner join mimic2v26.admissions a2
    on p2.subject_id = a2.subject_id
    where p2.subject_id < 500),
first_adm_mimic3 as (
    select p3.subject_id subject_id_m3, 
           p3.gender gender_m3,
           p3.dob dob_m3, 
           a3.admittime admittime_m3,
           row_number() over (partition by a3.subject_id order by a3.admittime) 
                  as hadm_order_m3
    from mimiciii.patients p3
    inner join mimiciii.admissions a3
    on p3.subject_id = a3.subject_id
    where p3.subject_id < 500)
select m2.subject_id_m2, m3.subject_id_m3,
       m2.gender_m2, m3.gender_m3,
       m2.dob_m2, m2.admittime_m2,
       m3.dob_m3, m3.admittime_m3,
       round((m2.admittime_m2 - m2.dob_m2)/365,1) as age_on_first_adm_m2,
       round((m3.admittime_m3 - m3.dob_m3)/365,1) as age_on_first_adm_m3,
       (m2.admittime_m2 - m2.dob_m2) - floor((m3.admittime_m3 - m3.dob_m3)) as age_diff
from first_adm_mimic2 m2
inner join first_adm_mimic3 m3
on m2.subject_id_m2 = m3.subject_id_m3
where hadm_order_m2 = 1
and hadm_order_m3 = 1;

Returns a table showing age on first hospital admission:

------------- | ------------- | ------ | ------ | --------- 
    2         |      2        | 0      | 0      |    0  
    3         |      3        | 76     | 76     |    0  
    4         |      4        | 47     | 47     |    0  

The last column shows the difference in age on first admission between the two versions of MIMIC. In general, the age on first admission is the same, so the difference (“AGE_DIFF”) is zero.

There are a small proportion of patients where the age on first admission differs. Reasons include:

  • hospital admissions that are not associated with ICU data were removed in MIMIC-III. The result is that some patients may appear older on the first admission.
  • dates occasionally differ between sources (e.g. ICU system vs hospital system).

We had to map physician notes between MIMIC 2 and MIMIC 3. Someone in our team did so using some text similarity technique: https://ektar.github.io/2016/mimic-iii-note-matching.html (mirror).


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.