Drug Era‎ > ‎

DER05: For a given indication, what proportion of patients take each indicated treatment?

 Parameter  Example  Mandatory  Notes
concept_id 21001738 Yes Cold Symptoms
list of relationship_id 21,23,155,156,126,127,240,241 Yes

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

select tt.concept_id, tt.concept_name, 100*(tt.cntPersons*1.0/tt.total*1.0) as proportion from (
select c.concept_id, c.concept_name, t.cntPersons, sum(cntPersons) over() as total 
from vocabulary.concept c, 
(select er.drug_concept_id, count(distinct er.person_id) as cntPersons 
from  vocabulary.concept_relationship cr, 
      vocabulary.ERA_VOCAB_MAP4DRUGS m,
      drug_era er
where cr.concept_id_1 = m.gen_voc_descendant_concept_id
  and er.drug_concept_id = m.gen_voc_ancestor_concept_id
  and cr.concept_id_2 = 21001738--&era_id -- &Indication_id                  
  and cr.relationship_id IN (21,23,155,156,126,127,240,241) -- allow only indication relationships
group by er.drug_concept_id, cr.concept_id_2
) t
where t.drug_concept_id = c.concept_id
) tt
Output field list:
 Field  Description
drug_era_id A system-generated unique identifier for each drug era.
person_id A foreign key identifier to the person who is subjected to the drug during the drug era. The demographic details of that person are stored in the person table.
drug_concept_id A foreign key that refers to a standard concept identifier in the vocabulary for the drug concept.
count The number of individual drug exposure occurrences used to construct the drug era.
descendant_concept_id A foreign key to the concept code in the concept table for the lower-level concept that forms the descendant in the relationship.
ancestor_concept_id A foreign key to the concept code in the concept table for the higher-level concept that forms the ancestor in the relationship.
relationship_id The type of relationship captured by the relationship record.

Sample output record:
 Field  Description