Incorrect codes in BNF maps for normalisation

[richiecroker]

I wrote to the NHSBSA a few months ago:

Hi

We use the BNF code change maps produced by the BSA to normalise our data at OpenPrescribing. We’ve noticed a couple of issue with it and I wanted to check them with you please.

We were originally supplied the maps by Graham Mitchell and Margaret Dockey via email, and more recently have been downloading them from your website (e.g. https://www.nhsbsa.nhs.uk/sites/default/files/2020-05/BNF%20Code%20ChangesV1.docx). However a user has raised an issue where some drugs don’t seem to be correct.

I’ve undertaken an analysis (which you can find [here](https://github.com/ebmdatalab/bnf-map-checker/blob/master/notebooks/BNF change map checker.ipynb)), and have found the following possible anomalies where it appears that the old BNF code is still being used:

old_bnf_code	new_bnf_code	bnf_name	Change year	items on old BNF code since change	Latest month of prescribing
0301040V0AAAAAA	0301020U0AAAAAA	Aclidinium brom 396mcg/dose / Formoterol 11.8m...	2017	94468	Nov 2023
0301040V0BBAAAA	0301020U0BBAAAA	Duaklir 340micrograms/dose / 12micrograms/dose...	2017	1086096	Nov 2023
0302000W0AAAAAA	0304020Z0AAAAAA	Generic Enerzair Breezhaler 114/46/136microg i...	2018	82	Nov 2023
0302000W0BBAAAA	0304020Z0BBAAAA	Enerzair Breezhaler 114microg / 46microg / 136...	2018	20518	Nov 2023
0407010X0CDACBF	NEEDS ENDED	Dental Pain Relief tablets	2013	1	Jan 2014
0408010AHBBAGAG	Withdrawn (no new code)	Vimpat_Syr 10mg/ml	2013	23	Sep 2013
0604011L0AABMBM	0703010S0AAAAAA	Estradiol 1mg / Progesterone 100mg capsules	2014	3037	Nov 2023
0604011L0CCAABM	0703010S0BBAAAA	Bijuve 1mg/100mg capsules	2014	8420	Nov 2023
1306010ACAAAAAA	1306030B0AAAAAA	Trifarotene 50micrograms/g cream	2018	632	Nov 2023
1306010ACBBAAAA	1306030B0BBAAAA	Aklief 50micrograms/g cream	2018	573	Nov 2023
1404000X0AAAHAH	1404000AQAAAAAA	Meningococcal polysacch A, C, W135 & Y conj va...	2017	105	Oct 2023
1404000X0BKAAAH	1404000AQBBAAAA	MenQuadfi vaccine solution for injection 0.5ml...	2017	91	Nov 2023

There are some other codes where it appears the new BNF code in the maps are being used for other drugs:

old_bnf_code	new_bnf_code	mapped BNF name	actual bnf_name	Change year	items
0604011L0AABMBM	0703010S0AAAAAA	Estradiol 1mg / Progesterone 100mg capsules	Estradiol 1.5mg / Nomegestrol 2.5mg tablets	2014	130648
Estradiol/Nomegestrol_Tab 1.5mg/2.5mg	2014	1372
0604011L0CCAABM	0703010S0BBAAAA	Bijuve 1mg/100mg capsules	Zoely 2.5mg/1.5mg tablets	2014	583550
Zoely_Tab 2.5mg/1.5mg	2014	5850
1306010ACAAAAAA	1306030B0AAAAAA	Trifarotene 50micrograms/g cream	Ivermectin 10mg/g cream	2018	3928305
1306010ACBBAAAA	1306030B0BBAAAA	Aklief 50micrograms/g cream	Soolantra 10mg/g cream	2018	3036810
1404000X0AAAHAH	1404000AQAAAAAA	Meningococcal polysacch A, C, W135 & Y conj vacc inj 0.5mlvl	Generic Bexsero vaccine inj 0.5ml pre-filled syringes	2017	6292
1404000X0BKAAAH	1404000AQBBAAAA	MenQuadfi vaccine solution for injection 0.5ml vials	Bexsero vacc inj 0.5ml pre-filled syringes	2017	737561

The maps we are using can be found here.

Can you please let me know if we are using the correct maps, or whether the maps provided to us and downloaded from the BSA are incorrect, so that we can make sure we are using the correct mapping.

Their response was:

Thanks for your enquiry, apologies, as the request is regarding data provided historically, I’ve had difficulty in recounting what has happened due to staff movement since and this has caused the delay.

However, we’ve taken a look through the products you have listed within the email. Please find the current listings of BNF code and drug description that they are aligned to in the BSA drug database and latest BNF version 86.

0301040V0AAAAAA - Aclidinium brom 396mcg/dose / Formoterol 11.8m 0301040V0BBAAAA - Duaklir 340micrograms/dose / 12micrograms/dose

0302000W0AAAAAA - Generic Enerzair Breezhaler 114/46/136microg i... 0302000W0BBAAAA - Enerzair Breezhaler 114microg / 46microg / 136...

0304020Z0AAAAAA Reslizumab 100mg/10ml solution for infusion vials 0304020Z0BBAAAA Cinqaero 100mg/10ml concentrate for solution for infusion vials

0408010AHBBAGAG - Vimpat_Syr 10mg/ml

0604011L0AABMBM - Estradiol 1mg / Progesterone 100mg capsules 0604011L0CCAABM - Bijuve 1mg/100mg capsules

0703010S0AAAAAA - Estradiol 1.5mg / Nomegestrol 2.5mg tablets 0703010S0BBAAAA - Zoely 2.5mg/1.5mg tablets

1306010ACAAAAAA - Trifarotene 50micrograms/g cream 1306010ACBBAAAA - Aklief 50micrograms/g cream

1306030B0AAAAAA - Ivermectin 10mg/g cream 1306030B0BBAAAA - Soolantra 10mg/g cream

1404000X0AAAHAH - Meningococcal polysacch A, C, W135 & Y conj va... 1404000X0BKAAAH - MenQuadfi vaccine solution for injection 0.5ml...

0301020U0AAAAAA, 0301020U0BBAAAA, 0407010X0CDACBF – these codes are not assigned in the latest BNF version held on ISP or our internal drug database.

Unfortunately and unexpectedly after the move to ODD (one drug database), it was identified that ceased BNF codes that were previously used on the legacy database, have been regenerated on the new system. However, mechanisms have been put in place so that the re-use of old BNF codes does not happen.

We would suggest for the most up-to-date reference point, you can download the latest version BNF 86 from the Information Services Portal (ISP), (previous versions are also available), this version updates monthly with new additions throughout the year. An additional annual file of version changes e.g. where a product is moved from one chapter to another can be found at Data Services -Data Services news | NHSBSA

FYI : If you have never used the ISP, if you follow these directions. Once logged in to the portal via the guest login, if you navigate to +data and then select Drug Data, a link for BNF information will be available from which you can select the version(s) you require, and get the data downloaded.

[richiecroker]

Having run this code:

WITH bnf_map_codes AS (SELECT DISTINCT former_bnf_code AS code, 'former' AS status
FROM ebmdatalab.hscic.bnf_map
WHERE former_bnf_code IS NOT NULL
UNION ALL
SELECT DISTINCT current_bnf_code AS code, 'current' AS status
FROM ebmdatalab.hscic.bnf_map
WHERE current_bnf_code IS NOT NULL)

SELECT * from bnf_map_codes
WHERE
code IN (
'0301040V0AAAAAA',
'0301040V0BBAAAA',
'0302000W0AAAAAA',
'0302000W0BBAAAA',
'0304020Z0AAAAAA',
'0304020Z0BBAAAA',
'0408010AHBBAGAG',
'0604011L0AABMBM',
'0604011L0CCAABM',
'0703010S0AAAAAA',
'0703010S0BBAAAA',
'1306010ACAAAAAA',
'1306010ACBBAAAA',
'1306030B0AAAAAA',
'1306030B0BBAAAA',
'1404000X0AAAHAH',
'1404000X0BKAAAH',
'0301020U0AAAAAA',
'0301020U0BBAAAA',
'0407010X0CDACBF'
)

I get the following codes:

code	status
0302000W0AAAAAA	former
0302000W0BBAAAA	former
0407010X0CDACBF	former
0604011L0AABMBM	former
0604011L0CCAABM	former
1306010ACAAAAAA	former
1306010ACBBAAAA	former
1404000X0AAAHAH	former
1404000X0BKAAAH	former
0304020Z0AAAAAA	current
0304020Z0BBAAAA	current
0703010S0AAAAAA	current
0703010S0BBAAAA	current
1306030B0AAAAAA	current
1306030B0BBAAAA	current

I think we need to identify any rows in ebmdatalab.hscic.bnf_map which have a former_bnf_code listed as a "former" in the above table, and remove those rows, as the map to a current bnf_code is incorrect.

For convenience, the codes this relates to are: ('0302000W0AAAAAA','0302000W0BBAAAA','0407010X0CDACBF','0604011L0AABMBM','0604011L0CCAABM','1306010ACAAAAAA','1306010ACBBAAAA','1404000X0AAAHAH','1404000X0BKAAAH')

For

[evansd]

I think this is finally fixed. Below are the steps I took to get this working (eliding all the missteps and various bits of detective work and local testing it took to get there).

After merging these two PRs:

• https://github.com/ebmdatalab/openprescribing/pull/4879
• https://github.com/ebmdatalab/openprescribing/pull/4882

And running:

./manage.py generate_presentation_replacements

I confirmed by running the SQL given above in BiqQuery that all the entries with a status of former were no longer being returned.

I then built and activated a new MatrixStore file:

./manage.py matrixstore_build 2024-04
./manage.py matrixstore_set_live

However the product I expected to now be visible on the Analyse page was still not visible:

Enerzair Breezhaler 114microg / 46microg / 136microg/dose
0302000W0BBAAAA

Searching for that BNF code in the database revealed that (a) it was still flagged as is_current = False and (b) it had completely the wrong name:

[local] postgres@prescribing=# SELECT * FROM frontend_presentation WHERE bnf_code = '0302000W0BBAAAA';
┌─[ RECORD 1 ]────────┬─────────────────────────────────────────────────────────────────┐
│ bnf_code            │ 0302000W0BBAAAA                                                 │
│ name                │ Cinqaero 100mg/10ml concentrate for inf vials                   │
│ is_generic          │ f                                                               │
│ replaced_by_id      │ ¤                                                               │
│ is_current          │ f                                                               │
│ adq_per_quantity    │ ¤                                                               │
│ quantity_means_pack │ f                                                               │
│ dmd_name            │ Cinqaero 100mg/10ml concentrate for solution for infusion vials │
└─────────────────────┴─────────────────────────────────────────────────────────────────┘

I was able to fix the is_current problem by running this command:

./manage.py refresh_bnf_class_currency

I was able to fix the name field issue by running the below (2024_06 is what happened to be the latest downloaded version):

./manage.py import_bnf_codes --filename /mnt/volume-fra1-02/openprescribing-data/bnf_codes/2024_06/bnf_codes.csv

At this point the product was now visible via the Analyse page. However the dmd_name field still contained the old name.

To fix this I needed to reimport the dm+d download, however this refused to run because there was already an ImportLog entry for that download. So I first deleted that entry using:

DELETE FROM frontend_importlog WHERE id=16395;

And I was then able to fix the dmd_name field by running:

./manage.py import_dmd