A list of all unique NDCs across 5 sources (RxNorm, RxNorm Historical, FDA NDC, FDA Excluded, and FDA Unfinished), matching product-level RXCUI if found, and description (RxNorm description preferred over FDA description)
A list of ATC 1-4 mapped to product-level RXCUI. The ATC 4 -> RXCUI mapping is done by FDA, but putting a flat file together with this information and combining it with ATC1-3 was the part we added
NDC preference:
RxNorm first, then RxNorm Historical
FDA NDC first, then Excluded, then Unfinished
RxNorm Historical strategy:
Take only the NDC with the most recent end_date
We may want to revisit that or create a separate mart for people looking to work with historical data
Other stuff:
Converted RxNorm historical JSON column format to JSONB for faster transformation times (I think)
Normalized ATC column formats with a staging table
NDC has to be unique and not null in the NDC description mart
RXCUI has to be unique and not null in the ATC mart
Created custom FDA display name out of FDA fields: nonprprietaryname active_numerator_strength active_ingred_unit dosageformname [proprietaryname proprietarynamesuffix]
Limited RxNorm NDCs to only those from SABs with SRL = 0 per RxNorm Technical Docs
Added a potentially useful staging table that just contains RXNCONSO rows from SAB = "RXNORM" and TTYs considered product TTYs
Rationale
Makes it easy to find NDC descriptions and classify NDCs.
Tests
What testing did you do?
Ran DAG to completion and did tests and reviewed documentation.
Explanation
Created two new marts
NDC preference:
RxNorm Historical strategy:
Other stuff:
nonprprietaryname active_numerator_strength active_ingred_unit dosageformname [proprietaryname proprietarynamesuffix]Rationale
Makes it easy to find NDC descriptions and classify NDCs.
Tests