Mapping from RxNorm to NDC [Brand/Generic RXCUI (SCD/SBD/GPCK/BPCK) and SCDC Crosswalks]

scdc_rxcui_ndc.sql

/*
RxNorm SCDC and Brand/Generic RXCUI (SCD/SBD/GPCK/BPCK) to NDC Crosswalks

We care about semantically defined RxNorm normal forms for clinical/branded drugs and packs ONLY. 
Certain NDCs that are associated with Non-RxNorm Concepts are ignored even though these may occur (infrequently) in real data.
e.g.
44946104501
50967031730
76439025810

VANDF can sometimes disagree with "generic-ness" for a given NDC atom - the NDC may therefore be duplicated across the 2 classes
e.g. pre-processing of 059310057922 adds it into 745679 (SCD) although from other SABs the NDC exists in 745752 (SBD).
*/

-- Find RXCUIs of SCDs and SBDs associated with SCDCs.
CREATE TABLE SCDC_TTY
SELECT con1.STR SCDC_STR, rel.RXCUI2 SCDC_RXCUI, rel.RXCUI1 SUB_RXCUI, con2.TTY, con2.STR RX_STR
FROM RXNCONSO con1
JOIN RXNREL rel on con1.RXCUI=rel.RXCUI2
JOIN RXNCONSO con2 on rel.RXCUI1=con2.RXCUI
WHERE con1.TTY='SCDC' and con1.SUPPRESS='N' and con2.SUPPRESS='N' and rel.RELA='constitutes' and con2.TTY IN ('SBD','SCD');
  
-- Find packs containing drugs in previous step and associate with SCDC CUI
CREATE TABLE SCDC_TTY_PCK
SELECT SCDC_STR, scdc.SCDC_RXCUI, rel.RXCUI1 SUB_RXCUI_PCK, con.TTY, con.STR RX_STR
FROM SCDC_TTY scdc
JOIN RXNREL rel on scdc.SUB_RXCUI=rel.RXCUI2
JOIN RXNCONSO con on con.RXCUI=rel.RXCUI1
WHERE rel.RELA='contained_in' and con.TTY IN ('BPCK','GPCK') and con.SUPPRESS='N';
  
-- Combine drugs and packs
CREATE TABLE SCDC_TTY_ALL
SELECT * FROM SCDC_TTY
UNION
SELECT * FROM SCDC_TTY_PCK;
  
-- Fetch NDCs that haven't been suppressed and attach dose form
-- SUPPRESS='O' terms can represent NDCs no longer marketed, but still relevant historically
CREATE TABLE SCDC_NDC
SELECT scdc.*, con1.STR DF_STR, sat1.ATV NDC, sat1.SUPPRESS
FROM SCDC_TTY_ALL scdc
JOIN RXNSAT sat1 on sat1.RXCUI=SUB_RXCUI
LEFT JOIN RXNREL rel1 on rel1.RXCUI2=SUB_RXCUI
LEFT JOIN RXNCONSO con1 on con1.RXCUI=rel1.RXCUI1
WHERE sat1.ATN='NDC' and sat1.SUPPRESS<>'Y'
and rel1.RELA='has_dose_form'
and con1.TTY='DF' and con1.SAB='RXNORM';

  
-- Trim 1st 0 if 12 digits long (VANDF)
UPDATE SCDC_NDC
    SET NDC = CASE WHEN LENGTH(NDC)=12 AND LOCATE('-', NDC) = 0 THEN SUBSTRING(NDC, 2, 11) ELSE NDC END;
  
ALTER TABLE SCDC_NDC
    ADD NDC_1 VARCHAR(5),
    ADD NDC_2 VARCHAR(4),
    ADD NDC_3 VARCHAR(2),
    ADD NDC_CONCAT VARCHAR(11),
    ADD NDC_NEW VARCHAR(11) COLLATE latin1_general_ci;
      
UPDATE SCDC_NDC
SET NDC_1 = SUBSTRING_INDEX(`NDC`, '-', 1),
    NDC_2 = SUBSTRING_INDEX(SUBSTRING_INDEX(`NDC`, '-', 2), '-', -1),
    NDC_3 = SUBSTRING_INDEX(SUBSTRING_INDEX(`NDC`, '-', 3), '-', -1);
   
-- Pad with 0s
UPDATE SCDC_NDC
SET
    NDC_1 = CASE WHEN LENGTH(NDC_1)=4 THEN CONCAT('0',NDC_1) ELSE NDC_1 END,
    NDC_2 = CASE WHEN LENGTH(NDC_2)=3 THEN CONCAT('0',NDC_2) ELSE NDC_2 END,
    NDC_3 = CASE WHEN LENGTH(NDC_3)=1 THEN CONCAT('0',NDC_3) ELSE NDC_3 END;
           
UPDATE SCDC_NDC
SET
    NDC_CONCAT = CONCAT(NDC_1,NDC_2,NDC_3);
          
UPDATE SCDC_NDC
SET
    NDC_NEW = CASE WHEN LOCATE('-', NDC) > 0 THEN NDC_CONCAT ELSE NDC END;
 

-- Relate NDCs to SBD/SCD/GPCK/BPCK
-- DF_STR redundant if using RX_STR since RX_STR = SCDC_STR + DF_STR [+ Brand]
CREATE TABLE SUB_RXCUI_NDC
    SELECT DISTINCT SUB_RXCUI, RX_STR, TTY, NDC_NEW
    FROM SCDC_NDC;

-- Relate NDCs to SCDC.  Requires R code below for post-processing to compound SCDCs for mixed ingredients: packs and mixed capsules
CREATE TABLE SCDC_NDC_NEW
    SELECT DISTINCT SCDC_STR, SCDC_RXCUI, SUB_RXCUI, TTY, RX_STR, DF_STR, NDC_NEW
    FROM SCDC_NDC;



/*
`SCDC_NDC_NEW` Post-Processing

R code to form compound SCDCs for mixed ingredients: packs and mixed capsules

library(dplyr)
library(RMySQL)

mysql_db_rx <- src_mysql(user='xxx', 
                      password='yyy', 
                      host='127.0.0.1', 
                      port=3306, dbname='rxnorm')
  
scdc_ndc <- mysql_db_rx %>%
  tbl('SCDC_NDC_NEW') %>%
  collect(n=Inf) %>%
  distinct(SCDC_STR, SCDC_RXCUI, SUB_RXCUI, TTY, NDC_NEW)
  
scdc_ndc_tf <- scdc_ndc %>%
  arrange(SUB_RXCUI, SCDC_RXCUI) %>%
  group_by(SUB_RXCUI) %>%
  mutate(SCDC_RXCUI_NEW = paste(unique(SCDC_RXCUI), collapse = '_'),
         STR_NEW = paste(unique(SCDC_STR), collapse = ';')) %>%
  ungroup()

scdc_ndc_final <- scdc_ndc_tf %>%
  distinct(STR_NEW, SCDC_RXCUI_NEW, TTY, NDC_NEW) %>%
  select(SCDC_STR=STR_NEW, SCDC_RXCUI=SCDC_RXCUI_NEW, TTY, NDC=NDC_NEW)

mysql_db_rxn_con <- dbConnect(MySQL(), user = 'mysql_local', password = 'drowssap', host = 'localhost', dbname = 'rxnorm2')
dbWriteTable(mysql_db_rxn_con, 'SCDC_NDC_NEW_COMPOUND', scdc_ndc_final, overwrite=TRUE, row.names = FALSE, 
             field.types=list(SCDC_STR='VARCHAR(256) COLLATE latin1_general_ci', 
                              SCDC_RXCUI='VARCHAR(256) COLLATE latin1_general_ci', 
                              TTY='VARCHAR(4) COLLATE latin1_general_ci', 
                              NDC='VARCHAR(11) COLLATE latin1_general_ci'))
dbSendQuery(mysql_db_rxn_con, "ALTER TABLE rxnorm2.SCDC_NDC_NEW_COMPOUND ADD INDEX ndc_ai USING BTREE (`NDC` ASC)")
dbDisconnect(mysql_db_rxn_con)
*/