Match USP MMG, USP DC, and VA formularies to trials - Automatic link #42

Use RxNorm to automatically link the formularies/drug lists to classes.

Then link back to trial/drugs list.

This will provide a base (autolinked) list to work with.

Use RxNorm to automatically link the formularies/drug lists to classes. Then link back to trial/drugs list. This will provide a base (autolinked) list to work with.

USP DC



/*
 * Match trials to SBD/SDC, then link trials on to USP category and class
 * 
 * The USP DC uses SBD/SDC (branded packs etc to track approved drugs)
 * */

WITH trialncts AS (
         SELECT DISTINCT ts.nct_id
           FROM history.trial_snapshots ts
        )
, nct_to_cui as (
SELECT bi.nct_id,
    bi.downcase_mesh_term,
    rr.tty2,
    rr.rxcui2 AS approved_drug_rxcui,
    count(*) AS count
   FROM ctgov.browse_interventions bi
     LEFT JOIN rxnorm_migrated.rxnorm_props rp ON bi.downcase_mesh_term = rp.propvalue1
     LEFT JOIN rxnorm_migrated.rxnorm_relations rr ON rr.rxcui1 = rp.rxcui
  WHERE 
    (bi.nct_id 
        IN ( 
            SELECT trialncts.nct_id
            FROM trialncts)) 
        AND bi.mesh_type = 'mesh-list' 
        AND rp.propname = 'Active_ingredient_name' 
        AND (rr.tty2 = ANY (ARRAY['BPCK','SCD','SBD','GPCK']))
  GROUP BY bi.nct_id, bi.downcase_mesh_term, rr.tty2, rr.rxcui2
)
select nct_id ,"USP Category", "USP Class"
from 
    nct_to_cui
    join "Formularies".usp_dc ud on ud.rxcui = nct_to_cui.approved_drug_rxcui
group by nct_id, "USP Category", "USP Class"
order by nct_id 
;

Based on initial results, these will need hand matched to validate.

## USP DC ```sql /* * Match trials to SBD/SDC, then link trials on to USP category and class * * The USP DC uses SBD/SDC (branded packs etc to track approved drugs) * */ WITH trialncts AS ( SELECT DISTINCT ts.nct_id FROM history.trial_snapshots ts ) , nct_to_cui as ( SELECT bi.nct_id, bi.downcase_mesh_term, rr.tty2, rr.rxcui2 AS approved_drug_rxcui, count(*) AS count FROM ctgov.browse_interventions bi LEFT JOIN rxnorm_migrated.rxnorm_props rp ON bi.downcase_mesh_term = rp.propvalue1 LEFT JOIN rxnorm_migrated.rxnorm_relations rr ON rr.rxcui1 = rp.rxcui WHERE (bi.nct_id IN ( SELECT trialncts.nct_id FROM trialncts)) AND bi.mesh_type = 'mesh-list' AND rp.propname = 'Active_ingredient_name' AND (rr.tty2 = ANY (ARRAY['BPCK','SCD','SBD','GPCK'])) GROUP BY bi.nct_id, bi.downcase_mesh_term, rr.tty2, rr.rxcui2 ) select nct_id ,"USP Category", "USP Class" from nct_to_cui join "Formularies".usp_dc ud on ud.rxcui = nct_to_cui.approved_drug_rxcui group by nct_id, "USP Category", "USP Class" order by nct_id ; ``` Based on initial results, these will need hand matched to validate.

Labels Milestones

Match USP MMG, USP DC, and VA formularies to trials - Automatic link #42

USP DC