OHDSI Home | Forums | Wiki | Github

Multiple events per one cost

CDM Builders
#21

Real ones.

#22

Should we initiate MIN case adding to OMOP?

#23

Even adding both RxNorm MIN class and RxNorm Extension existing ingredient combinations will not resolve the NDC issue completely. It looks like we need to add new ingredient combinations anyway.

Please find queries inside:

Among 82 NDC codes with 1-to-many mapping: 
SELECT DISTINCT c1.concept_id
FROM concept c1

JOIN concept_relationship cr1
ON c1.concept_id = cr1.concept_id_1 AND cr1.relationship_id = 'Maps to' AND cr1.invalid_reason IS NULL

JOIN concept c2
ON cr1.concept_id_2 = c2.concept_id AND c2.standard_concept = 'S' AND c2.invalid_reason IS NULL

WHERE c1.vocabulary_id = 'NDC'
GROUP BY c1.concept_id
HAVING count(DISTINCT c2.concept_id) > 1
ORDER BY c1.concept_id
There are just 16 with appropriate drug forms and likely MINs 
    with all_ndc as (
select c1.concept_id as ndc_id,
       c1.concept_name as ndc_name,
       array_agg(distinct ds.ingredient_concept_id order by ds.ingredient_concept_id) ndc_arr
from concept c1
join concept_relationship r on r.concept_id_1 = c1.concept_id and r.invalid_reason is null and r.relationship_id = 'Maps to'
join drug_strength ds on ds.drug_concept_id = r.concept_id_2
where c1.vocabulary_id = 'NDC'
and c1.concept_id in (SELECT DISTINCT c1.concept_id
                        FROM concept c1

                                 JOIN concept_relationship cr1
                                      ON c1.concept_id = cr1.concept_id_1 AND cr1.relationship_id = 'Maps to' AND cr1.invalid_reason IS NULL

                                 JOIN concept c2
                                      ON cr1.concept_id_2 = c2.concept_id AND c2.standard_concept = 'S' AND c2.invalid_reason IS NULL

                        WHERE c1.vocabulary_id = 'NDC'

                        GROUP BY c1.concept_id
                        HAVING count(DISTINCT c2.concept_id) > 1
                        ORDER BY c1.concept_id
    )
group by c1.concept_id, c1.concept_name
)

select distinct n.ndc_id, n.ndc_name, c3.concept_id as rx_id, c3.concept_name as rx_name, cl_drugs.form_id, cl_drugs.form_name
from all_ndc n
join (
	select c1.concept_id as form_id,
	       c1.concept_name as form_name,
	       array_agg(ds.ingredient_concept_id order by ds.ingredient_concept_id) drugs_arr
    from concept c1
    join drug_strength ds on ds.drug_concept_id = c1.concept_id
    where c1.vocabulary_id like 'Rx%'
    and c1.concept_class_id = 'Clinical Drug Form'
    group by c1.concept_id, c1.concept_name

) cl_drugs on cl_drugs.drugs_arr=n.ndc_arr
join concept_relationship r3 on n.ndc_id = r3.concept_id_1 and r3.invalid_reason is null and r3.relationship_id = 'Maps to'
join concept c3 on r3.concept_id_2 = c3.concept_id
order by 1,2,3,4,5,6
The rest 66 of them have no such. Most of them are multivitamins, but still 
    with all_ndc as (
select c1.concept_id as ndc_id,
       c1.concept_name as ndc_name,
       array_agg(distinct ds.ingredient_concept_id order by ds.ingredient_concept_id) ndc_arr
from concept c1
join concept_relationship r on r.concept_id_1 = c1.concept_id and r.invalid_reason is null and r.relationship_id = 'Maps to'
join drug_strength ds on ds.drug_concept_id = r.concept_id_2
where c1.vocabulary_id = 'NDC'
and c1.concept_id in (SELECT DISTINCT c1.concept_id
                        FROM concept c1

                                 JOIN concept_relationship cr1
                                      ON c1.concept_id = cr1.concept_id_1 AND cr1.relationship_id = 'Maps to' AND cr1.invalid_reason IS NULL

                                 JOIN concept c2
                                      ON cr1.concept_id_2 = c2.concept_id AND c2.standard_concept = 'S' AND c2.invalid_reason IS NULL

                        WHERE c1.vocabulary_id = 'NDC'

                        GROUP BY c1.concept_id
                        HAVING count(DISTINCT c2.concept_id) > 1
                        ORDER BY c1.concept_id
    )
group by c1.concept_id, c1.concept_name
)

select distinct n.ndc_id, n.ndc_name, c3.concept_id as rx_id, c3.concept_name as rx_name, cl_drugs.form_id, cl_drugs.form_name
from all_ndc n
left join (
	select c1.concept_id as form_id,
	       c1.concept_name as form_name,
	       array_agg(ds.ingredient_concept_id order by ds.ingredient_concept_id) drugs_arr
    from concept c1
    join drug_strength ds on ds.drug_concept_id = c1.concept_id
    where c1.vocabulary_id like 'Rx%'
    and c1.concept_class_id = 'Clinical Drug Form'
    group by c1.concept_id, c1.concept_name

) cl_drugs on cl_drugs.drugs_arr=n.ndc_arr
join concept_relationship r3 on n.ndc_id = r3.concept_id_1 and r3.invalid_reason is null and r3.relationship_id = 'Maps to'
join concept c3 on r3.concept_id_2 = c3.concept_id
where form_id is null
order by 1,2,3,4,5,6

;

#24

Hi all,

So it sounds like thereā€™s a few workarounds as @nzvyagina alluded to, and then thereā€™s just creating new RxNorm extensions so that these drugs donā€™t map to multiple ingredient concepts.

Weā€™re still seeing hundreds of source codes from our claims data that have this 2 concept mapping issue. Until there are new concepts, what are folks doing to handle this as of now? Is it to pick 1 event_id for the cost, is it to divide the cost up evenly between those event lines?

Thanks,
Ajit

t