inst/shinyApps/QueryLibrary/queries/drug/D05.md

In OHDSI/QueryLibrary: A Query Library for the OMOP-CDM

D05: Find generic drugs by ingredient

Description

This query is designed to extract all generic drugs that have a specified ingredient. The query accepts an ingredient concept ID as the input and returns all generic (not branded) drugs that have the ingredient. It should be noted that the query returns both generics that have a single ingredient (i.e. the specified ingredient) and those that are combinations which include the specified ingredient. The query requires the ingredient concept ID as the input. A list of these ingredient concepts can be extracted by querying the CONCEPT table for concept class of 'Ingredient'

Query

SELECT        A.concept_id Ingredient_concept_id,
                A.concept_Name Ingredient_name,
                A.concept_Code Ingredient_concept_code,
                A.concept_Class_id Ingredient_concept_class,
                D.concept_id Generic_concept_id,
                D.concept_Name Generic_name,
                D.concept_Code Generic_concept_code,
                D.concept_class_id Generic_concept_class
FROM        @vocab.concept_ancestor CA,
                @vocab.concept A,
                @vocab.concept D
WHERE
        CA.ancestor_concept_id = 966991
AND        CA.ancestor_concept_id = A.concept_id
AND CA.descendant_concept_id = D.concept_id
AND        D.concept_class_id = 'Clinical Drug'
AND        (getdate() >= A.valid_start_date)
AND        (getdate() <= A.valid_end_date)
AND          (getdate() >= D.valid_start_date)
AND        (getdate() <= D.valid_end_date)

Input

| Parameter | Example | Mandatory | Notes | | --- | --- | --- | --- | | Ingredient Concept ID | 966991 | Yes | Concept ID for 'Simethicone'. Ingredient concepts can be extracted from CONCEPT table as records of concept class of 'Ingredient' | | As of date | Sysdate | No | Valid record as of specific date. Current date – sysdate is a default |

Output

| Field | Description | | --- | --- | | Ingredient_Concept_ID | Concept ID of the ingredient entered as input | | Ingredient_name | Name of the Ingredient | | Ingredient_Concept_code | Concept code of the ingredient | | Ingredient_Concept_class | Concept Class of the ingredient | | Generic_Concept_ID | Concept ID of drug with the ingredient | | Generic_Name | Name of drug concept with the ingredient | | Generic_Concept_Code | Concept code of the drug with the ingredient | | Generic_Concept_Class | Concept class of drug with the ingredient |

Example output record

| Field | Value | | --- | --- | | Ingredient_Concept_ID | 966991 | | Ingredient_name | Simethicone | | Ingredient_Concept_code | 9796 | | Ingredient_Concept_class | Ingredient | | Generic_Concept_ID | 967306 | | Generic_Name | Simethicone 10 MG/ML Oral Solution | | Generic_Concept_Code | 251293 | | Generic_Concept_Class | Clinical Drug |

Documentation

https://github.com/OHDSI/CommonDataModel/wiki/

OHDSI/QueryLibrary documentation built on Aug. 1, 2023, 5:42 a.m.