[Atlas] customize medication use by ATC

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