ATC fits the best indeed; its refactoring is currently in progress, so no decent documentation right now. But the cookbook with the SQL queries will be ready later this spring/summer.
By that time ATC coverage will be broader and more precise, but even now you can look for your drugs of interest.
For instance,
you first find the broad group of ATC codes for statins:
https://www.whocc.no/atc_ddd_index/?code=C10AA
then you query the database to look for all children of this broad group:
select c2.* from concept c
join concept_ancestor ca on ca.ancestor_concept_id = c.concept_id
join concept c2 on c2.concept_id = ca.descendant_concept_id
where c.concept_code like ‘C10AA%’;
For aspirin:
you have multiple aspirins with different indications, so you need to browse WHO ATC and figure out the class you want. Say, you want this one: https://www.whocc.no/atc_ddd_index/?code=B01AC06 (the daily dosage of others is higher than 325 mg).
The same thing:
select c2.* from concept c
join concept_ancestor ca on ca.ancestor_concept_id = c.concept_id
join concept c2 on c2.concept_id = ca.descendant_concept_id
where c.concept_code like ‘B01AC06’;
You don’t really know the indication, you can use drug_strength to limit the results by the dose:
select c2.* from concept c
join concept_ancestor ca on ca.ancestor_concept_id = c.concept_id
join concept c2 on c2.concept_id = ca.descendant_concept_id
join drug_strength ds on ds.drug_concept_id = c2.concept_id
where lower(c.concept_name) = ‘aspirin’
and c.standard_concept = ‘S’
and c.concept_class_id = ‘Ingredient’
and ds.amount_value<325
and not exists
(select 1 from drug_strength ds2
where ds2.drug_concept_id = ds.drug_concept_id
group by ds2.drug_concept_id having count(1)>1);
Here, I’m using only amount_value as I’m assuming that you need only solid forms. Otherwise, add numerator_value/denominator_value for concentration.
I’m also adding a group by condition to get only single-ingredient drugs. For buffers, find their concept_ids and put in the query as well.
