Hi Erica, Richard, Sara, and others:
Yesterday I had shown the following results on the amount of data in
LAERTES, for 10 HOI concepts, that could be validated from the OMOP
2011-2012 reference set (aforementioned above). Based on these data,
approximately 2-4% of the data for each HOI could be verified.
HOI concept ID hoi_concept_name LAERTES: Total # unique drug concept IDs
from test set (Evidence of association) OMOP reference: Total # unique drug
concept IDs from reference set # Matches Recall 500000301 OMOP Acute Liver
Failure 1 4843 117 117 0.024158579 500000302 OMOP Acute Liver Failure 2
4228 2 2 0.000473037 500000307 OMOP Acute Liver Failure 7 984 117 82
0.083333333 500000308 OMOP Acute Liver Failure 8 4228 117 117 0.027672658
500000401 OMOP Acute Renal Failure 1 2897 88 84 0.028995513 500000402 OMOP
Acute Renal Failure 2 2897 88 84 0.028995513 500000404 OMOP Acute Renal
Failure 4 2897 88 84 0.028995513 500000405 OMOP Acute Renal Failure 5 2897
88 84 0.028995513 500000801 OMOP Acute Myocardial Infarction 1 2732 102 99
0.036237189 500001001 OMOP Upper Gastrointestinal Ulcer 1 1863 91 81
0.043478261
However, we had discussed whether the # unique drug concept IDs from the
LAERTES included ones not limited to the ingredient level. Accordingly,
I’ve edited the query 'AND c1.CONCEPT_NAME=‘Ingredient’.
WITH CTE_HOI AS (
SELECT DISTINCT c.CONCEPT_ID AS HOI_CONCEPT_ID, c.CONCEPT_NAME AS
HOI_CONCEPT_NAME,
c1.CONCEPT_ID AS CONDITION_CONCEPT_ID, c1.CONCEPT_NAME AS
CONDITION_CONCEPT_NAME
FROM CONCEPT c
JOIN CONCEPT_ANCESTOR ca
ON ca.ANCESTOR_CONCEPT_ID = c.CONCEPT_ID
JOIN CONCEPT c1
ON c1.CONCEPT_ID = ca.DESCENDANT_CONCEPT_ID
AND c1.CONCEPT_NAME=‘Ingredient’
AND c1.INVALID_REASON IS NULL
WHERE c.CONCEPT_ID IN
(
/YOU NEED TO DOUBLE CHECK THAT THESE ARE ALL THE OMOP HOI
CONCEPT IDS/
500000402,500000404,500000801,500001003,500000308,500000303,500000802,500000805,500000301,500000403,500000405,500000401,500000302,500000305,500001001,500000307,500000803,500000306,500000304
)
)
SELECT z.DRUG_HOI_RELATIONSHIP, h.HOI_CONCEPT_ID, h.HOI_CONCEPT_NAME,
h.CONDITION_CONCEPT_ID, h.CONDITION_CONCEPT_NAME, z.DRUG AS
DRUG_CONCEPT_ID, z.RXNORM_DRUG AS DRUG_CONCEPT_NAME,
z.EVIDENCE_TYPE, z.MODALITY, z.STATISTIC_TYPE,
z.STATISTIC_VALUE, z.EVIDENCE_LINKOUT
FROM CTE_HOI h
JOIN (
SELECT *
FROM DRUG_HOI_EVIDENCE e
JOIN DRUG_HOI_RELATIONSHIP r
ON r.ID = e.DRUG_HOI_RELATIONSHIP
WHERE MODALITY = ‘t’
) z
ON z.HOI = h.CONDITION_CONCEPT_ID
ORDER BY h.HOI_CONCEPT_NAME, h.CONDITION_CONCEPT_NAME, z.RXNORM_DRUG,
z.EVIDENCE_TYPE
However, I did not get any results. Could you help me diagnose?
