Drug Era‎ > ‎

DER20: Counts of drugs, stratified by drug type and drug exposure count

This query is used to count drugs (drug_concept_id) across all drug exposure records stratified by drug exposure type (drug_type_concept_id, in CDM V2 drug_exposure_type) and drug exposure count (drug_exposure_count) for each era. The input to the query is a value (or a comma-separated list of values) of a drug_concept_id, a drug_type_concept_id and a drug_exposure_count. If the input is omitted, all existing value combinations are summarized.

Input:
 Parameter  Example  Mandatory  Notes
concept_id Yes
drug_exposure_count Yes

Sample query run:
The following is a sample run of the query. The input parameters are highlighted in blue

-- Oracle
SELECT tt.drug_concept_id
     , min(tt.stat_value) AS min_value
     , max(tt.stat_value) AS max_value
     , avg(tt.stat_value) AS avg_value
     , (round(stdDev(tt.stat_value)) ) AS stdDev_value
     , PERCENTILE_DISC(0.25) WITHIN GROUP( ORDER BY tt.stat_value ) 
                                     AS percentile_25
     , PERCENTILE_DISC(0.5)  WITHIN GROUP (ORDER BY tt.stat_value )
                                     AS median_value
     , PERCENTILE_DISC(0.75) WITHIN GROUP (ORDER BY tt.stat_value )
                                     AS percential_75
  FROM
    ( SELECT 
      extract(year from  (min(t.drug_era_start_date) over(partition by t.person_id, t.drug_concept_id) )) - p.year_of_birth as stat_value,
      t.drug_concept_id
      FROM drug_era t,
           person p 
      where t.person_id = p.person_id       
       and t.drug_concept_id in (1300978, 1304643, 1549080)
    ) tt 
  group by tt.drug_concept_id    
  order by tt.drug_concept_id    
 ;
Output:
Output field list:
 Field  Description
drug_concept_id A foreign key that refers to a standard concept identifier in the vocabulary for the drug concept.
drug_exposure_count The number of individual drug exposure occurrences used to construct the drug era.
drug_type_concept_id A foreign key to the predefined concept identifier in the vocabulary reflecting the type of drug exposure recorded. It indicates how the drug exposure was represented in the source data: as medication history, filled prescriptions, etc.
percentile_25
median_value
percentile_75

Sample output record:
 Field  Description
drug_concept_id
drug_exposure_count
drug_type_concept_id
percentile_25
median_value
percentile_75
Comments