PheKnowVec helper queries

generate_code_codes.sql

-- get unique counts of source and standard codes for each phenotype

-- CHCO
SELECT standard_code_set, COUNT(DISTINCT source_code) AS source_code_count, COUNT(DISTINCT standard_code) AS standard_code_count
FROM CHCO_DeID_Oct2018.ADHD_COHORT_VARS
WHERE standard_code_set is NOT NULL
GROUP BY standard_code_set

SELECT standard_code_set, COUNT(DISTINCT source_code) AS source_code_count, COUNT(DISTINCT standard_code) AS standard_code_count
FROM CHCO_DeID_Oct2018.APPENDICITIS_COHORT_VARS
WHERE standard_code_set is NOT NULL
GROUP BY standard_code_set

SELECT standard_code_set, COUNT(DISTINCT source_code) AS source_code_count, COUNT(DISTINCT standard_code) AS standard_code_count
FROM CHCO_DeID_Oct2018.CROHNSDISEASE_COHORT_VARS
WHERE standard_code_set is NOT NULL
GROUP BY standard_code_set

SELECT standard_code_set, COUNT(DISTINCT source_code) AS source_code_count, COUNT(DISTINCT standard_code) AS standard_code_count
FROM CHCO_DeID_Oct2018.HYPOTHYROIDISM_COHORT_VARS
WHERE standard_code_set is NOT NULL
GROUP BY standard_code_set

SELECT standard_code_set, COUNT(DISTINCT source_code) AS source_code_count, COUNT(DISTINCT standard_code) AS standard_code_count
FROM CHCO_DeID_Oct2018.PEANUTALLERGY_COHORT_VARS
WHERE standard_code_set is NOT NULL
GROUP BY standard_code_set

SELECT standard_code_set, COUNT(DISTINCT source_code) AS source_code_count, COUNT(DISTINCT standard_code) AS standard_code_count
FROM CHCO_DeID_Oct2018.SICKLECELLDISEASE_COHORT_VARS
WHERE standard_code_set is NOT NULL
GROUP BY standard_code_set

SELECT standard_code_set, COUNT(DISTINCT source_code) AS source_code_count, COUNT(DISTINCT standard_code) AS standard_code_count
FROM CHCO_DeID_Oct2018.SLEEPAPNEA_COHORT_VARS
WHERE standard_code_set is NOT NULL
GROUP BY standard_code_set

SELECT standard_code_set, COUNT(DISTINCT source_code) AS source_code_count, COUNT(DISTINCT standard_code) AS standard_code_count
FROM CHCO_DeID_Oct2018.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS
WHERE standard_code_set is NOT NULL
GROUP BY standard_code_set

SELECT standard_code_set, COUNT(DISTINCT source_code) AS source_code_count, COUNT(DISTINCT standard_code) AS standard_code_count
FROM CHCO_DeID_Oct2018.SYSTEMICLUPUSERYTHEMATOSUS_COHORT_VARS
WHERE standard_code_set is NOT NULL
GROUP BY standard_code_set


-- MIMIC
SELECT standard_code_set, COUNT(DISTINCT source_code) AS source_code_count, COUNT(DISTINCT standard_code) AS standard_code_count
FROM MIMICIII_OMOP_Mar2019.ADHD_COHORT_VARS
WHERE standard_code_set is NOT NULL
GROUP BY standard_code_set

SELECT standard_code_set, COUNT(DISTINCT source_code) AS source_code_count, COUNT(DISTINCT standard_code) AS standard_code_count
FROM MIMICIII_OMOP_Mar2019.APPENDICITIS_COHORT_VARS
WHERE standard_code_set is NOT NULL
GROUP BY standard_code_set

SELECT standard_code_set, COUNT(DISTINCT source_code) AS source_code_count, COUNT(DISTINCT standard_code) AS standard_code_count
FROM MIMICIII_OMOP_Mar2019.CROHNSDISEASE_COHORT_VARS
WHERE standard_code_set is NOT NULL
GROUP BY standard_code_set

SELECT standard_code_set, COUNT(DISTINCT source_code) AS source_code_count, COUNT(DISTINCT standard_code) AS standard_code_count
FROM MIMICIII_OMOP_Mar2019.HYPOTHYROIDISM_COHORT_VARS
WHERE standard_code_set is NOT NULL
GROUP BY standard_code_set

SELECT standard_code_set, COUNT(DISTINCT source_code) AS source_code_count, COUNT(DISTINCT standard_code) AS standard_code_count
FROM MIMICIII_OMOP_Mar2019.PEANUTALLERGY_COHORT_VARS
WHERE standard_code_set is NOT NULL
GROUP BY standard_code_set

SELECT standard_code_set, COUNT(DISTINCT source_code) AS source_code_count, COUNT(DISTINCT standard_code) AS standard_code_count
FROM MIMICIII_OMOP_Mar2019.SICKLECELLDISEASE_COHORT_VARS
WHERE standard_code_set is NOT NULL
GROUP BY standard_code_set

SELECT standard_code_set, COUNT(DISTINCT source_code) AS source_code_count, COUNT(DISTINCT standard_code) AS standard_code_count
FROM MIMICIII_OMOP_Mar2019.SLEEPAPNEA_COHORT_VARS
WHERE standard_code_set is NOT NULL
GROUP BY standard_code_set

SELECT standard_code_set, COUNT(DISTINCT source_code) AS source_code_count, COUNT(DISTINCT standard_code) AS standard_code_count
FROM MIMICIII_OMOP_Mar2019.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS
WHERE standard_code_set is NOT NULL
GROUP BY standard_code_set

SELECT standard_code_set, COUNT(DISTINCT source_code) AS source_code_count, COUNT(DISTINCT standard_code) AS standard_code_count
FROM MIMICIII_OMOP_Mar2019.SYSTEMICLUPUSERYTHEMATOSUS_COHORT_VARS
WHERE standard_code_set is NOT NULL
GROUP BY standard_code_set

truncate_tables.SQL

-- truncate existing tables in the CHCO and MIMIC databases

-- CHCO
DELETE from CHCO_DeID_Oct2018.ADHD_COHORT_VARS WHERE 1=1
DELETE from CHCO_DeID_Oct2018.APPENDICITIS_COHORT_VARS WHERE 1=1
DELETE from CHCO_DeID_Oct2018.CROHNSDISEASE_COHORT_VARS WHERE 1=1
DELETE from CHCO_DeID_Oct2018.PEANUTALLERGY_COHORT_VARS WHERE 1=1
DELETE from CHCO_DeID_Oct2018.HYPOTHYROIDISM_COHORT_VARS WHERE 1=1
DELETE from CHCO_DeID_Oct2018.SICKLECELLDISEASE_COHORT_VARS WHERE 1=1
DELETE from CHCO_DeID_Oct2018.SLEEPAPNEA_COHORT_VARS WHERE 1=1
DELETE from CHCO_DeID_Oct2018.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS WHERE 1=1
DELETE from CHCO_DeID_Oct2018.SYSTEMICLUPUSERYTHEMATOSUS_COHORT_VARS WHERE 1=1

-- MIMIC
DELETE from MIMICIII_OMOP_Mar2019.ADHD_COHORT_VARS WHERE 1=1
DELETE from MIMICIII_OMOP_Mar2019.APPENDICITIS_COHORT_VARS WHERE 1=1
DELETE from MIMICIII_OMOP_Mar2019.CROHNSDISEASE_COHORT_VARS WHERE 1=1
DELETE from MIMICIII_OMOP_Mar2019.PEANUTALLERGY_COHORT_VARS WHERE 1=1
DELETE from MIMICIII_OMOP_Mar2019.HYPOTHYROIDISM_COHORT_VARS WHERE 1=1
DELETE from MIMICIII_OMOP_Mar2019.SICKLECELLDISEASE_COHORT_VARS WHERE 1=1
DELETE from MIMICIII_OMOP_Mar2019.SLEEPAPNEA_COHORT_VARS WHERE 1=1
DELETE from MIMICIII_OMOP_Mar2019.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS WHERE 1=1
DELETE from MIMICIII_OMOP_Mar2019.SYSTEMICLUPUSERYTHEMATOSUS_COHORT_VARS WHERE 1=1

uniqueify_tables.SQL

-- replace table contents to ensure only unique rowa remain

-- CHCO
CREATE OR REPLACE TABLE CHCO_DeID_Oct2018.ADHD_COHORT_VARS AS SELECT DISTINCT * FROM CHCO_DeID_Oct2018.ADHD_COHORT_VARS
CREATE OR REPLACE TABLE CHCO_DeID_Oct2018.APPENDICITIS_COHORT_VARS AS SELECT DISTINCT * FROM CHCO_DeID_Oct2018.APPENDICITIS_COHORT_VARS
CREATE OR REPLACE TABLE CHCO_DeID_Oct2018.CROHNSDISEASE_COHORT_VARS AS SELECT DISTINCT * FROM CHCO_DeID_Oct2018.CROHNSDISEASE_COHORT_VARS
CREATE OR REPLACE TABLE CHCO_DeID_Oct2018.PEANUTALLERGY_COHORT_VARS AS SELECT DISTINCT * FROM CHCO_DeID_Oct2018.PEANUTALLERGY_COHORT_VARS
CREATE OR REPLACE TABLE CHCO_DeID_Oct2018.HYPOTHYROIDISM_COHORT_VARS AS SELECT DISTINCT * FROM CHCO_DeID_Oct2018.HYPOTHYROIDISM_COHORT_VARS
CREATE OR REPLACE TABLE CHCO_DeID_Oct2018.SICKLECELLDISEASE_COHORT_VARS AS SELECT DISTINCT * FROM CHCO_DeID_Oct2018.SICKLECELLDISEASE_COHORT_VARS
CREATE OR REPLACE TABLE CHCO_DeID_Oct2018.SLEEPAPNEA_COHORT_VARS AS SELECT DISTINCT * FROM CHCO_DeID_Oct2018.SLEEPAPNEA_COHORT_VARS
CREATE OR REPLACE TABLE CHCO_DeID_Oct2018.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS AS SELECT DISTINCT * FROM CHCO_DeID_Oct2018.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS
CREATE OR REPLACE TABLE CHCO_DeID_Oct2018.SYSTEMICLUPUSERYTHEMATOSUS_COHORT_VARS AS SELECT DISTINCT * FROM CHCO_DeID_Oct2018.SYSTEMICLUPUSERYTHEMATOSUS_COHORT_VARS

-- MIMIC
CREATE OR REPLACE TABLE MIMICIII_OMOP_Mar2019.ADHD_COHORT_VARS AS SELECT DISTINCT * FROM MIMICIII_OMOP_Mar2019.ADHD_COHORT_VARS
CREATE OR REPLACE TABLE MIMICIII_OMOP_Mar2019.APPENDICITIS_COHORT_VARS AS SELECT DISTINCT * FROM MIMICIII_OMOP_Mar2019.APPENDICITIS_COHORT_VARS
CREATE OR REPLACE TABLE MIMICIII_OMOP_Mar2019.CROHNSDISEASE_COHORT_VARS AS SELECT DISTINCT * FROM MIMICIII_OMOP_Mar2019.CROHNSDISEASE_COHORT_VARS
CREATE OR REPLACE TABLE MIMICIII_OMOP_Mar2019.PEANUTALLERGY_COHORT_VARS AS SELECT DISTINCT * FROM MIMICIII_OMOP_Mar2019.PEANUTALLERGY_COHORT_VARS
CREATE OR REPLACE TABLE MIMICIII_OMOP_Mar2019.HYPOTHYROIDISM_COHORT_VARS AS SELECT DISTINCT * FROM MIMICIII_OMOP_Mar2019.HYPOTHYROIDISM_COHORT_VARS
CREATE OR REPLACE TABLE MIMICIII_OMOP_Mar2019.SICKLECELLDISEASE_COHORT_VARS AS SELECT DISTINCT * FROM MIMICIII_OMOP_Mar2019.SICKLECELLDISEASE_COHORT_VARS
CREATE OR REPLACE TABLE MIMICIII_OMOP_Mar2019.SLEEPAPNEA_COHORT_VARS AS SELECT DISTINCT * FROM MIMICIII_OMOP_Mar2019.SLEEPAPNEA_COHORT_VARS
CREATE OR REPLACE TABLE MIMICIII_OMOP_Mar2019.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS AS SELECT DISTINCT * FROM MIMICIII_OMOP_Mar2019.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS
CREATE OR REPLACE TABLE MIMICIII_OMOP_Mar2019.SYSTEMICLUPUSERYTHEMATOSUS_COHORT_VARS AS SELECT DISTINCT * FROM MIMICIII_OMOP_Mar2019.SYSTEMICLUPUSERYTHEMATOSUS_COHORT_VARS