Hi! Good afternoon -
I have customized an SQL code to look at medication use on a specific date at the drug exposure ID level. I would appreciate any suggestion on how to modify the code to analyze drug use at the ATC level, based on the attached script.
For context, I am interested in four drug classes - ARB, MRA, SGLT2i, and beta-blockers - for which I have created concept sets.
Thank you in advance for your help!
Best,
Shanshan
select cast(drug_concept_id as bigint)*1000 + @analysis_id as covariate_id,
c.concept_name as covariate_name,
drug_concept_id as concept_id,
count(*) as sum_value,
count(*)*1.0 / stat.total_cnt*1.0 as average_value
from (select distinct drug_concept_id,
cohort.subject_id,
cohort.cohort_start_date
from @cohort_table cohort
inner join @cdm_database_schema.drug_exposure on cohort.subject_id = drug_exposure.person_id
where drug_exposure_start_date <= '2021-03-01'
and (drug_exposure_end_date >= '2021-03-01' or drug_exposure_end_date is null)
and drug_concept_id != 0
and cohort.cohort_definition_id = @cohort_id) drug_entries
join @cdm_database_schema.concept c on drug_entries.drug_concept_id = c.concept_id
cross join (select count(*) total_cnt
from @cohort_table
where cohort_definition_id = @cohort_id) stat
group by drug_concept_id,
c.concept_name,
stat.total_cnt