3

I am new to SQL and am trying to explore the MIMIC-II dataset using the query builder so the logical step seemed to be follow the tutorials within the introduction document [1]. Unfortunately I believe it contains errors. The errors start in example 5 with 'missing right parenthesis' in the second part of code and 'missing by keyword' in the third longer piece.

Can anyone more experienced than me see the errors and how to correct them?

Or alternatively does anyone have some good examples of simple code I can learn from and adapt.

To start with I would ideally just like to do some simple things such as age of patients and length of stay looking to then plot any correlation.

Ultimately I am looking to predict mortality and length of stay but I know there are many baby steps required between where I am now and that. If anybody know of similar work carried out that may be useful would be greatly appreciated.

[1] http://mimic.physionet.org/archive/introduction-mimic-ii.pdf

Thanks in advance, James

4

I am new to SQL and am trying to explore the MIMIC-II dataset using the query builder so the logical step seemed to be follow the tutorials within the introduction document [1].

The current version of the MIMIC database is MIMIC-III. MIMIC-II is an old version that is no longer supported by the MIT Laboratory for Computational Physiology, so I would not recommend using it as a starting point for your studies.

The introduction document that you are using is not current and may well contain errors, particularly as it is a static PDF document that is not well-suited to continuous updates.

The errors start in example 5 with 'missing right parenthesis' in the second part of code and 'missing by keyword' in the third longer piece. Can anyone more experienced than me see the errors and how to correct them?

The second chunk of code in example 5 reads:

SELECT DISTINCT p.subject_id, p.dob, a.hadm_id, a.admit_dt,
    p.hospital_expire_flg, 
    MIN(a.admit_dt) OVER (PARTITION BY p.subject_id) AS first_adm_dt 
FROM admissions a, d_patients p
WHERE p.subject_id = a.subject_id
AND p.dob IS NOT NULL
ORDER BY a.hadm_id, p.subject_id;

The code runs as expected for me when tested against MIMIC-II v2.6 in an Oracle database (it returns 36,095 rows). It is possible that your error is a result of copy and pasting (weird things can happen when you copy and paste from a PDF!).

SQL syntax varies between database systems (Oracle, Postgres, MySQL, ...), so this could be another source of error. Note that while the semicolon (';') is generally required at the end of an SQL query, the (outdated, unsupported) MIMIC-II querybuilder returns an 'invalid character' error unless it is removed.

Either way, I wouldn't recommend treating the code snippet as best practice because it uses old-style comma syntax to join the admissions and d_patients tables. It is generally considered better practice to use the join keyword.

The third code chunk in example 5 reads:

WITH first_admission_date AS (
    SELECT DISTINCT p.subject_id, p.dob, p.sex, a.hadm_id, a.admit_dt, 
    MIN(a.admit_dt) OVER (PARTITION BY a.hadm_id, p.subject_id) AS first_adm_dt
    FROM admissions a, d_patients p
    WHERE p.subject_id = a.subject_id 
    AND p.dob IS NOT NULL
    ORDER BY a.hadm_id, p.subject_id), 
age AS ( 
    SELECT subject_id, hadm_id, dob, sex, first_adm_dt, 
    ROUND(months_between(first_adm_dt, dob) /12, 2) first_adm_age, 
    CASE WHEN (months_between(first_adm_dt, dob) /12) >= 15 THEN 'adult'
         WHEN months_between(first_adm_dt, dob) <= 1 THEN 'neonate' 
         ELSE 'middle' 
         END AS age_group
    FROM first_admission_date
    ORDER BY subject_id, hadm_id)
SELECT * 
FROM age;

This code also runs as expected for me on MIMIC-II, and also returns 36,095 rows. I can't explain your 'missing by keyword' error, but it may be a copy and pasting issue here too.

To start with I would ideally just like to do some simple things such as age of patients and length of stay looking to then plot any correlation.

If you would like to explore the MIMIC database, the first step is familiarising yourself with the current MIMIC website and documentation. Content in the "archive" directory should only be used if you have specific reasons to refer to old content (for example, you are attempting to reproduce a study which used previous versions of the MIMIC database).

After installing the latest version of MIMIC-III by following the instructions on the website, I would recommend referring to the MIMIC Code Repository for sample code. There is a direct link to the repository on the front page of the MIMIC website.

The code repository is being developed continuously by the MIMIC research community. It includes a MIMIC Cookbook directory that contains introductory queries to get you started. Once you become more confident with SQL then we encourage you to make your own contributions to the repository.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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