This query is used to provide summary statistics for the drug era length (drug_era_length) across all drug era records stratified by drug (drug_concept_id) and drug type (drug_type_concept_id, in CDM V2 drug_exposure_type): the mean, the standard deviation, the minimum, the 25th percentile, the median, the 75th percentile, the maximum and the number of missing values. The length of an era is defined as the difference between the start date and the end date. The input to the query is a value (or a comma-separated list of values) of a drug_concept_id and a drug_type_concept_id. If the input is omitted, all existing value combinations are summarized.
Input:
| Parameter |
Example |
Mandatory |
Notes |
| list of concept_id |
1300978, 1304643, 1549080 |
Yes |
|
| drug_type_concept_id |
38000182 |
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
, tt.drug_type_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 percentile_75
FROM
( SELECT
(t.drug_era_end_date - t.drug_era_start_date) as stat_value,
t.drug_concept_id, t.drug_type_concept_id
FROM drug_era t
where t.drug_concept_id in (1300978, 1304643, 1549080)
and t.drug_type_concept_id = 38000182
) tt
group by tt.drug_concept_id, tt.drug_type_concept_id
order by tt.drug_concept_id, tt.drug_type_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_type_concept_id |
|
| min_value |
|
| max_value |
|
| avg_value |
The start date for the drug era constructed from the individual instances of drug exposures. It is the start date of the very first chronologically recorded instance of utilization of a drug. |
| percentile_25 |
|
| median_value |
|
| percentile_75 |
|
| drug_era_end_date |
The end date for the drug era constructed from the individual instance of drug exposures. It is the end date of the final continuously recorded instance of utilization of a drug. |
| drug_era_start_start |
The start date for the drug era constructed from the individual instances of drug exposures. It is the start date of the very first chronologically recorded instance of utilization of a drug. |
| stat_value |
|
Sample output record:
| Field |
Description |
| drug_concept_id |
|
| drug_type_concept_id |
|
| min_value |
|
| max_value |
|
| avg_value |
|
| percentile_25 |
|
| median_value |
|
| percentile_75 |
|
| drug_era_end_date |
|
| drug_era_start_start |
|
| stat_value |
|
|
|
