search

OHDSI / Vocabulary-v5.0

Build process for the OHDSI Standardized Vocabularies. Currently not available as independent release.
The Unlicense
213 stars 75 forks source link

J-Code HCPCs that Don't End Up at RxNorm #22

Closed ericaVoss closed 6 years ago

ericaVoss commented 9 years ago

Some HCPC J-Codes do not end up at RxNorm.

/*WHICH HCPC J-CODES DON'T END UP AT RxNORM*/
SELECT *
FROM CONCEPT c
    LEFT OUTER JOIN (
        /*HCPCs that go to RxNorm*/
        SELECT c.CONCEPT_ID
        FROM CONCEPT c
            JOIN CONCEPT_RELATIONSHIP cr
                ON cr.CONCEPT_ID_1 = c.CONCEPT_ID 
            JOIN CONCEPT c2
                ON c2.CONCEPT_ID = cr.CONCEPT_ID_2
                AND c2.VOCABULARY_ID = 'RxNorm'
                AND c2.INVALID_REASON IS NULL
        WHERE c.VOCABULARY_ID = 'HCPCS'
        AND c.CONCEPT_CODE LIKE 'J%'
        AND c.INVALID_REASON IS NULL
    ) z
        ON c.CONCEPT_ID = z.CONCEPT_ID
WHERE c.VOCABULARY_ID = 'HCPCS'
AND c.INVALID_REASON IS NULL
AND c.CONCEPT_CODE LIKE 'J%'
AND z.CONCEPT_ID IS NULL
ORDER BY CONCEPT_CODE

For example 

44786557    Injection, aripiprazole, extended release, 1 mg

This could end up at a clinical drug or ingredient.

cgreich commented 9 years ago

Will be covered. And there are a lot of G and C codes of the same kind.

From: ericaVoss [mailto:notifications@github.com] Sent: Tuesday, February 03, 2015 5:57 PM To: OHDSI/Vocabulary-v5.0 Subject: [Vocabulary-v5.0] J-Code HCPCs that Don't End Up at RxNorm (#22)

Some HCPC J-Codes do not end up at RxNorm.

/WHICH HCPC J-CODES DON'T END UP AT RxNORM/ SELECT * FROM CONCEPT c LEFT OUTER JOIN ( /HCPCs that go to RxNorm/ SELECT c.CONCEPT_ID FROM CONCEPT c JOIN CONCEPT_RELATIONSHIP cr ON cr.CONCEPT_ID_1 = c.CONCEPT_ID JOIN CONCEPT c2 ON c2.CONCEPT_ID = cr.CONCEPT_ID_2 AND c2.VOCABULARY_ID = 'RxNorm' AND c2.INVALID_REASON IS NULL WHERE c.VOCABULARY_ID = 'HCPCS' AND c.CONCEPT_CODE LIKE 'J%' AND c.INVALID_REASON IS NULL ) z ON c.CONCEPT_ID = z.CONCEPT_ID WHERE c.VOCABULARY_ID = 'HCPCS' AND c.INVALID_REASON IS NULL AND c.CONCEPT_CODE LIKE 'J%' AND z.CONCEPT_ID IS NULL ORDER BY CONCEPT_CODE

For example

44786557 Injection, aripiprazole, extended release, 1 mg

This could end up at a clinical drug or ingredient.

— Reply to this email directly or view it on GitHub https://github.com/OHDSI/Vocabulary-v5.0/issues/22 . https://github.com/notifications/beacon/AAuxfuRprfkMLmT8cVPQKeFB-lePohWfks5noUnHgaJpZM4DbRSc.gif

ericaVoss commented 9 years ago

VERSION v5.0 21-Mar-2015

I think I'm still seeing this for 75 codes. Some of the ones left might be too generic like:

2718684 Contraceptive supply, hormone containing vaginal ring, each

But there are some specific ones:

44786568    Injection, carfilzomib, 1 mg

Couldn't this go to one of these:

42873407    carfilzomib Injectable Solution
42873638    carfilzomib

Updated this slightly.

SELECT *
FROM CONCEPT c
LEFT OUTER JOIN (
/*HCPCs that go to RxNorm*/
SELECT c.CONCEPT_ID
FROM CONCEPT c
JOIN CONCEPT_RELATIONSHIP cr
ON cr.CONCEPT_ID_1 = c.CONCEPT_ID 
JOIN CONCEPT c2
ON c2.CONCEPT_ID = cr.CONCEPT_ID_2
AND c2.VOCABULARY_ID = 'RxNorm'
AND c2.INVALID_REASON IS NULL
WHERE c.VOCABULARY_ID = 'HCPCS'
AND c.CONCEPT_CODE LIKE 'J%'
AND c.INVALID_REASON IS NULL
) z
ON c.CONCEPT_ID = z.CONCEPT_ID
WHERE c.VOCABULARY_ID = 'HCPCS'
AND c.INVALID_REASON IS NULL
AND c.CONCEPT_CODE LIKE 'J%'
AND z.CONCEPT_ID IS NULL
AND LEN(c.CONCEPT_CODE) > 2
ORDER BY CONCEPT_CODE
ericaVoss commented 9 years ago

FYI, here are some of the relationships we are interested in:

CONCEPT_ID  CONCEPT_NAME    MAPPED_CONCEPT_ID   MAPPED_CONCEPT_NAME
44786557            Injection, aripiprazole, extended release, 1 mg 40137292    aripiprazole Injectable Solution
44786558            Injection, certolizumab pegol, 1 mg (code may be used for medicare when drug administered under the direct supervision of a physician, not for use when drug is self administered)  40150010    certolizumab pegol Injectable Solution
44786562            Injection, golimumab, 1 mg, for intravenous use 40160779    golimumab Prefilled Syringe
44786564            Injection, zoledronic acid, 1 mg    40101550    zoledronic acid Injectable Solution
45890806            Injection, paclitaxel, 1 mg 45775594    Paclitaxel 1 MG/ML Injectable Solution
2718699                 Hyaluronan or derivative, euflexxa, for intra-articular injection, per dose 40243208    2 ML Sodium Hyaluronate 10 MG/ML Prefilled Syringe [Euflexxa]
2718700                 Hyaluronan or derivative, orthovisc, for intra-articular injection, per dose    42708471    2 ML Sodium Hyaluronate 15 MG/ML Prefilled Syringe [Orthovisc]
40663578            Hyaluronan or derivative, synvisc or synvisc-one, for intra-articular injection, 1 mg   42706202    HYLAN G-F 20 Prefilled Syringe [Synvisc]
40664773            Hyaluronan or derivative, gel-one, for intra-articular injection, per dose  43014076    hyaluronate Prefilled Syringe [Gel-One]
45890789            Hyaluronan or derivative, monovisc, for intra-articular injection, per dose 44818008    hyaluronate Prefilled Syringe [Monovisc]
45890807            Injection, obinutuzumab, 10 mg  44507681    obinutuzumab Injectable Solution
44786570            Injection, pertuzumab, 1 mg 42801292    pertuzumab Injectable Solution
cgreich commented 9 years ago

Adding these now.

ericaVoss commented 8 years ago

v5.0 21-JAN-16

Double checked this, there are 57 now but here are 35 that people might care about:

concept_id  concept_name
915783  Injection, alemtuzumab, 1 mg
45890792    Buprenorphine/naloxone, oral, less than or equal to 3 mg buprenorphine
45890793    Buprenorphine/naloxone, oral, less than or equal to 3 mg buprenorphine
45890795    Buprenorphine/naloxone, oral, greater than 6 mg, but less than or equal to 10 mg buprenorphine
45890796    Buprenorphine/naloxone, oral, greater than 10 mg buprenorphine
915784  Injection, c1 esterase inhibitor (recombinant), ruconest, 10 units
915785  Injection, ceftolozane 50 mg and tazobactam 25 mg
915786  Injection, ceftazidime and avibactam, 0.5 g/0.125 g
915787  Injection, dalbavancin, 5mg
45890798    Injection, epoetin beta, 1 microgram, (for non esrd use)
915789  Injection, tbo-filgrastim, 1 microgram
915790  Injection, immune globulin/hyaluronidase, (hyqvia), 100 mg immuneglobulin
915791  Injection, isavuconazonium, 1 mg
915792  Injection, oritavancin, 10 mg
915793  Injection, pasireotide long acting, 1 mg
915794  Injection, peramivir, 1 mg
915795  Injection, siltuximab, 10 mg
44786563    Injection, taliglucerace alfa, 10 units
915796  Injection, tedizolid phosphate, 1 mg
45890805    Injection, testosterone undecanoate, 1 mg
915797  Injection, vedolizumab, 1 mg
915800  Levonorgestrel-releasing intrauterine contraceptive system, 52mg, 3 year duration
915801  Levonorgestrel-releasing intrauterine contraceptive system, 52 mg, 5 year duration
915802  Injection, fluocinolone acetonide, intravitreal implant, 0.01 mg
915803  Hyaluronan or derivative, gel-syn, for intra-articular injection, 0.1 mg
2718702 Carbidopa 5 mg/levodopa 20 mg enteral suspension
915804  Tacrolimus, extended release, (envarsus xr), oral, 0.25 mg
915805  Prednisone, immediate release or delayed release, oral, 1 mg
915807  Netupitant 300 mg and palonosetron 0.5 mg
915808  Injection, belinostat, 10 mg
915809  Injection, blinatumomab, 1 microgram
915810  Injection, pembrolizumab, 1 mg
915811  Injection, nivolumab, 1 mg
915812  Injection, ramucirumab, 5 mg
44786573    Injection, ziv-aflibercept, 1 mg
2718920 Injection, porfimer sodium, 75 mg
ericaVoss commented 8 years ago

Still exists in v5.0 12-FEB-16

cgreich commented 8 years ago

Yikes. They popped up while we were building the automatic processor and didn't get in. Will fix now.

ericaVoss commented 8 years ago

@cgreich - I know you already told me the HCPC fixes aren't in, but this is still an open issue in v5.0 11-MAR-16

cgreich commented 8 years ago

All fixed except: 2718920 Injection, porfimer sodium, 75 mg 2718702 Carbidopa 5 mg/levodopa 20 mg enteral suspension 915807 Netupitant 300 mg and palonosetron 0.5 mg

ericaVoss commented 6 years ago

I think I still see this in 20170920. I'm pulling back 191 rows with my query.

Example:

2718831 Injection, clofarabine, 1 mg
915808  Injection, belinostat, 10 mg
2718836 Injection, bortezomib, 0.1 mg
ericaVoss commented 6 years ago

My favorite issue. :) This is still happening in 20171201.

SELECT DRUG_CONCEPT_ID, DRUG_SOURCE_VALUE, COUNT(DISTINCT PERSON_ID) AS PERSON_COUNT
FROM DRUG_EXPOSURE
WHERE DRUG_SOURCE_VALUE IN (
'J0120','J0131','J0153','J0178','J0180','J0190','J0210','J0220','J0221','J0256','J0257','J0280','J0300','J0348','J0360','J0461','J0480','J0485','J0571','J0575','J0585','J0586','J0587','J0588','J0594','J0596','J0597','J0598','J0600','J0636','J0637','J0641','J0690','J0692','J0694','J0697','J0698','J0712','J0713','J0714','J0720','J0740','J0744','J0795','J0833','J0834','J0840','J0875','J0878','J0894','J0895','J1020','J1160','J1162','J1245','J1250','J1320','J1322','J1324','J1327','J1335','J1430','J1438','J1439','J1443','J1453','J1455','J1458','J1459','J1460','J1556','J1557','J1559','J1561','J1566','J1568','J1569','J1572','J1580','J1595','J1599','J1600','J1620','J1670','J1710','J1742','J1743','J1756','J1786','J1790','J1800','J1815','J1817','J1830','J1835','J1890','J1931','J1945','J1950','J1953','J2185','J2248','J2260','J2278','J2315','J2320','J2325','J2360','J2407','J2425','J2460','J2469','J2504','J2507','J2545','J2547','J2562','J2590','J2680','J2700','J2725','J2765','J2778','J2783','J2793','J2796','J2800','J2810','J2916','J2941','J2993','J2995','J2997','J3060','J3090','J3095','J3230','J3240','J3243','J3246','J3260','J3301','J3302','J3303','J3305','J3320','J3364','J3365','J3370','J3385','J3396','J3420','J3465','J3470','J3475','J3485','J3486','J3489','J3490','J3530','J3535','J3570','J3590','J7042','J7183','J7187','J7199','J7205','J7300','J7301','J7303','J7304','J7306','J7307','J7308','J7311','J7312','J7316','J7320','J7330','J7340','J7342','J7511','J7599','J7626','J7627','J7633','J7634','J7635','J7636','J7640','J7665','J7676','J7699','J7799','J7999','J8498','J8499','J8597','J8655','J8999','J9000','J9015','J9019','J9020','J9027','J9032','J9041','J9043','J9045','J9047','J9060','J9065','J9070','J9098','J9100','J9150','J9151','J9155','J9160','J9179','J9206','J9207','J9208','J9209','J9211','J9213','J9214','J9217','J9225','J9226','J9260','J9262','J9263','J9266','J9270','J9300','J9305','J9307','J9310','J9315','J9328','J9330','J9340','J9370','J9371','J9390','J9400','J9600','J9999'
)
GROUP BY DRUG_CONCEPT_ID, DRUG_SOURCE_VALUE
HAVING COUNT(DISTINCT PERSON_ID) > 10
ORDER BY COUNT(DISTINCT PERSON_ID) DESC
DRUG_CONCEPT_ID DRUG_SOURCE_VALUE PERSON_COUNT
903963 J3301 5447808
46287340 J0690 2794829
906939 J2765 1184301
1309010 J3420 1010104
1506270 J1020 609560
40087247 J3370 321929
19093848 J3475 205359
46287363 J0694 171777
40071173 J2360 152626
1797513 J0744 141625
46275706 J0698 135204
903963 J3302 132814
914335 J0461 126333
19095173 J0280 114449
40022178 J9045 108645
40032338 J9000 105942
1326115 J2590 95387
903963 J3303 94562
1310317 J9070 84022
40055972 J7308 79791
40035704 J1250 77554
46275076 J9310 62358
40029100 J9060 54515
46287419 J0697 50541
1351541 J1950 43969
1351541 J9217 43858
939259 J7626 42709
46287358 J0692 34236
40088725 J3260 29392
1308290 J9370 28107
1305058 J9260 26492
1309204 J0153 23330
40044168 J3470 20111
46275511 J0713 18212
40053913 J1561 13006
40030421 J3230 9806
40062879 J9209 8839
40049686 J1710 8625
40053913 J1566 7515
1311078 J9100 7481
1380068 J9214 6092
939259 J7633 5920
40067397 J2460 5285
1151789 J1438 4835
40220618 J1568 4371
40048954 J9208 4289
19008009 J0833 4286
40071653 J2700 3969
1518254 J7312 3316
40021360 J1890 3208
1584910 J2941 3016
40220553 J1572 2941
40067154 J2545 2751
939259 J7627 2279
40012384 J7636 2037
40023477 J0720 1998
40087784 J0120 1656
40053913 J1460 1600
751889 J1595 1520
40012679 J1600 1367
46275937 J7187 1233
1196677 J7640 1187
42799761 J9226 1165
19109997 J2680 1020
40056952 J1830 1011
40085629 J3364 804
19136207 J7511 725
40090202 J2810 691
40012384 J7635 673
40003373 J9020 643
1379969 J9213 601
42799762 J9225 599
46275912 J9340 552
46275842 J0480 417
40047592 J1620 404
40053913 J1599 399
40020206 J0190 394
1514412 J2320 370
939259 J7634 287
40052913 J1835 224
40008924 J1320 211
40079393 J2995 210
40084555 J3320 200
40067154 J7676 190
40087956 J3305 166
40085629 J3365 97
40078626 J9270 82
46287541 J0571 79
1114220 J0571 79
46275937 J7183 69
1308290 J9371 47
40058579 J1945 42
aostropolets commented 6 years ago

Working on it. Hope to release the vocabulary next week.

aostropolets commented 6 years ago

@ericaVoss We are left with 20 concepts that are too general to be mapped:

concept_name concept_code
Injection, insulin, per 5 units J1815
Immunosuppressive drug, not otherwise classified J7599
Contraceptive supply, hormone containing patch, each J7304
Contraceptive supply, hormone containing vaginal ring, each J7303
Intrauterine copper contraceptive J7300
Hemophilia clotting factor, not otherwise classified J7199
Not otherwise classified, antineoplastic drugs J9999
Unclassified biologics J3590
Laetrile, amygdalin, vitamin b17 J3570
Drug administered through a metered dose inhaler J3535
Nasal vaccine inhalation J3530
Unclassified drugs J3490
Prescription drug, oral, chemotherapeutic, nos J8999
Antiemetic drug, oral, not otherwise specified J8597
Prescription drug, oral, non chemotherapeutic, nos J8499
Antiemetic drug, rectal/suppository, not otherwise specified J8498
Noc drugs, other than inhalation drugs, administered through dme J7799
Noc drugs, inhalation solution administered through dme J7699
Insulin for administration through dme (i.e., insulin pump) per 50 units J1817
Compounded drug, not otherwise classified J7999
schuemie commented 6 years ago

Not sure if this is solved in the next version, but seeing the problem in Sep 2017 version for zoledronic acid:

HCPCS Name
Q4095 Injection, zoledronic acid (reclast), 1 mg
J3488 Injection, zoledronic acid (reclast), 1 mg
J3487 Injection, zoledronic acid (zometa), 1 mg
J3489 Injection, zoledronic acid, 1 mg
Q2051 Injection, zoledronic acid, not otherwise specified, 1mg
aostropolets commented 6 years ago

It is. In fact, we also released mappings from CPT4 drugs to RxNorm at the beginning of this year. Personally I find it easy to look at the concepts in Athena as it has the up-to-date version.

cgreich commented 6 years ago

Do you want to close it, @ericaVoss?

ericaVoss commented 6 years ago

We still are on 20171201, will close when I can test it in an updated version.

ericaVoss commented 6 years ago

@cgreich - I still think I'm seeing the issue in 20180609. I pulled back 219 rows.

Example:

2718218         Injection abciximab, 10 mg
40664807    Injection, acetaminophen, 10 mg
2718221         Injection, adalimumab, 20 mg

What is weird is 2718218-Injection abciximab, 10 mg had a mapping in 20171201 but now doesn't.

aostropolets commented 6 years ago

@ericaVoss Hmmm. We have mappings to RxE for those three.

ericaVoss commented 6 years ago

Yeah, my query above is terrible. I find no Jcodes lacking a map (if I wrote this one right).

WITH CTE_HCPC AS (
    SELECT c.CONCEPT_ID, c.CONCEPT_NAME, c.CONCEPT_CODE
    FROM CONCEPT c
        JOIN CONCEPT_RELATIONSHIP cr
            ON cr.CONCEPT_ID_1 = c.CONCEPT_ID 
        JOIN CONCEPT c2
            ON c2.CONCEPT_ID = cr.CONCEPT_ID_2
            AND c2.VOCABULARY_ID = 'RxNorm'
            AND c2.INVALID_REASON IS NULL
    WHERE c.VOCABULARY_ID = 'HCPCS'
    AND c.CONCEPT_CODE LIKE 'J%'
    AND c.INVALID_REASON IS NULL
),
CTE_VOCAB_MAP AS (
       SELECT c.concept_code AS SOURCE_CODE, c.concept_id AS SOURCE_CONCEPT_ID, c.concept_name AS SOURCE_CODE_DESCRIPTION, c.vocabulary_id AS SOURCE_VOCABULARY_ID, 
                           c.domain_id AS SOURCE_DOMAIN_ID, c.CONCEPT_CLASS_ID AS SOURCE_CONCEPT_CLASS_ID, 
                                                   c.VALID_START_DATE AS SOURCE_VALID_START_DATE, c.VALID_END_DATE AS SOURCE_VALID_END_DATE, c.INVALID_REASON AS SOURCE_INVALID_REASON, 
                           c1.concept_id AS TARGET_CONCEPT_ID, c1.concept_name AS TARGET_CONCEPT_NAME, c1.VOCABULARY_ID AS TARGET_VOCABUALRY_ID, c1.domain_id AS TARGET_DOMAIN_ID, c1.concept_class_id AS TARGET_CONCEPT_CLASS_ID, 
                           c1.INVALID_REASON AS TARGET_INVALID_REASON, c1.standard_concept AS TARGET_STANDARD_CONCEPT
       FROM CONCEPT C
             JOIN CONCEPT_RELATIONSHIP CR
                        ON C.CONCEPT_ID = CR.CONCEPT_ID_1
                        AND CR.invalid_reason IS NULL
                        AND cr.relationship_id = 'Maps To'
              JOIN CONCEPT C1
                        ON CR.CONCEPT_ID_2 = C1.CONCEPT_ID
                        AND C1.INVALID_REASON IS NULL
       UNION
       SELECT source_code, SOURCE_CONCEPT_ID, SOURCE_CODE_DESCRIPTION, source_vocabulary_id, c1.domain_id AS SOURCE_DOMAIN_ID, c2.CONCEPT_CLASS_ID AS SOURCE_CONCEPT_CLASS_ID,
                                        c1.VALID_START_DATE AS SOURCE_VALID_START_DATE, c1.VALID_END_DATE AS SOURCE_VALID_END_DATE, 
                     stcm.INVALID_REASON AS SOURCE_INVALID_REASON,target_concept_id, c2.CONCEPT_NAME AS TARGET_CONCEPT_NAME, target_vocabulary_id, c2.domain_id AS TARGET_DOMAIN_ID, c2.concept_class_id AS TARGET_CONCEPT_CLASS_ID, 
                     c2.INVALID_REASON AS TARGET_INVALID_REASON, c2.standard_concept AS TARGET_STANDARD_CONCEPT
       FROM source_to_concept_map stcm
              LEFT OUTER JOIN CONCEPT c1
                     ON c1.concept_id = stcm.source_concept_id
              LEFT OUTER JOIN CONCEPT c2
                     ON c2.CONCEPT_ID = stcm.target_concept_id
       WHERE stcm.INVALID_REASON IS NULL
)
SELECT *
FROM CTE_HCPC h
    LEFT OUTER JOIN CTE_VOCAB_MAP v
        ON v.SOURCE_CODE = h.CONCEPT_CODE 
WHERE v.SOURCE_CODE IS NULL

I find 0 Jcodes without a map.