From the FDA's Orange Book site, I have lots of information about drug patents. I want to be able to link up drug patents with NDCs. Does anyone know of a mapping table that bridges the gap between Orange Book fields like Patent Number and Product Number and NDC?
Matching on Appl_No (APPLICATIONNUMBER in the new NDC format) is the correct strategy.
Note that there are many categories of products in the NDC, and the Orange Book is restricted to small molecule drugs. So, you'll only find matches for NDAs, ANDAs, OTCs and a few others. But, importantly, not BLAs, and unapproved drugs.
I've tried to get the data from the link you provided(*zip file ~1mb) which contain some *.txt files. I looked into the
products.txt, where two lines look like this:
BUDESONIDE~AEROSOL, FOAM;RECTAL~UCERIS~VALEANT PHARMS INTL~2MG/ACTUATION~N~205613~001~~Oct 7, 2014~Yes~Yes~RX~VALEANT PHARMACEUTICALS INTERNATIONAL BETAMETHASONE VALERATE~AEROSOL, FOAM;TOPICAL~BETAMETHASONE VALERATE~PERRIGO~0.12%~A~078337~001~AB~Nov 26, 2012~No~No~RX~PERRIGO CO
Have a look at the attribute
Appl_No, you will find the same number in the data sheet, which I found here at the National Bureau of Economic Research, but with some letters in front of it. The column is called
appnum and contains data like in the form of
What you need to do is try to get rid of the all.In Excel you can try:
Where N2 is cell in which the
appnum is situated.
After this try this tutorial to merge the two lists.
It appears that FDA now shares all of the NDC codes and NDAs through their National Drug Directory. This should solve your problem. https://www.fda.gov/Drugs/InformationOnDrugs/ucm142438.htm