Update PPU tool to remove manually curated list and reflect dm+d rather than BNF codes

[richiecroker]

We released the "price-per-unit" tool in 2017. It works by identifying possible switches in two different ways:

1. All brands prescribed under the same linked BNF code, ie. having the first same 9 digits and the last two digits. e.g. Omeprazole 20mg dispersible gastro-resistant tablets [0103050P0AAANAN] and Losec MUPS 20mg gastro-resistant tablets [0103050P0BBAEAN] have the same "9+2" code (013050P0____AN), and therefore we can easily find them
2. Suitable preparation switches (e.g. tablets can often be switched to capsules and vice versa), e.g. Ramipril 10mg tablets and Ramipril 10mg capsules have the same active ingredient, and don't have any specific reason why they are not interchangeable. These swaps were created using a manually-created list of suitable formulation swaps.

Although this was fine when the tool was released in 2017, it is in urgent need of an overhaul, for a number of reasons:

1. The spreadsheet hasn't been reviewed since 2017, and consequently we are at risk of a) missing items suitable for swaps, and b) suggesting swaps that may be less suitable than first appeared. The review for this would be time-consuming, and still leave us at risk of us missing things.
2. A number of items which appear to be suitable for cheaper brand prescribing are no longer in the "drugs" section of the BNF code hierarchy. Instead brands are appearing as "appliances", which a) do not have a hierarchical code in the same way, or b) aren't linked to the generic code, and therefore can't be mapped. For example, generic Hypromellose 0.3% eye drops have the BNF code 1108010F0AAAAAA (in the Eye chapter), whereas common brands, e.g. Xailin Hydrate 0.3% eye drops have an Appliance chapter BNF code 21300000169, which can't be mapped to the generic.
3. The BNF code mapping for generics to brands doesn't take into account where branded preparations should be prescribed, or where items have been discontinued. For example, the October 2024 PPU dashboard for NHS Devon for sulphasalazine 500mg GR tablets contains both Sulazine EC 500mg tablets (which have been discontinued for some time) and Salazopyrin EN-Tabs 500mg (which have been recently discontinued).

However, these issues should be able to be (hopefully) relatively easy to resolve.

From my relatively-limited understanding, the tool uses the following to decide on what's in the "substitution set":

• In the get_formulation_swaps function, a csv version of the current formulation swaps lists from Google Sheets is used to map e.g. tablet to capsule formulations.
• In the generic_equivalent_for_bnf_code function, the generic BNF code is created from the existing branded BNF code, in order to map to all brands.

1. Change formulation swaps table to SQL-based view in BigQuery, using logic from dm+d to define suitable swaps

By defining logic, we can create a formulation swap in SQL from the dm+d Virtual Medicinal Product dataset. I have created a first attempt, using the following logic, which should mirror what the current list attempts manually.

Logic

• Must be the same chemical substance (or substances), and not include other substances
• Must be the same strength (for all chemical substances)
• Must be the same route (e.g. oral, rectal, injection)
• Must be suitable for prescribing in primary care without brand (e.g. exclude phenytoin, where tablet and capsule have different properties)
• If not oral, must be same "size", e.g. Morphine 10mg/2ml and 5mg/1ml aren't suitable, even though they're the same strength
• Swaps between "normal release" and "modified-release" aren't included

This list is created by creating a string containing ingredient codes, strength numerator and denominator value and units, route, prescribing status flag, unit dose values for non-oral preps, and a "modified-release" flag.

For example the string for Ramipril 10mg capsules (0205051R0AAADAD) is 386872004|1|10|258684004|NULL|NULL|26643006|1|NULL|NULL, comprising:

Field name	Field Description	Value	Description
ing	Chemical ingredient	386872004	Rampril
basis_strnt	How is ingredient strength calculated?	1	Ingredient Substance
strnt_nmrtr_val	Strength value numerator	10
strnt_nmrtr_uom	trength value numerator unit	258684004	mg
strnt_dnmtr_val	Strength value denominator	NULL
strnt_dnmtr_uom	trength value denominator unit	NULL
route	Drug route	26643006	Oral
pres_stat	Prescribing status	1	Valid as a prescribable product
udfs	Unit dose form size	NULL	(used in non-oral preps, e.g. ampoule size)
formroute.descr LIKE '%modified-release%'	Is product modified-release	NULL

NOTE: this doesn't contain the drug form, i.e. capsule, so it will match to any other generic which matches this string. In the case above, we get this:

nm_1	bnf_code_1	route_1	formroute_1	nm_2	bnf_code_2	route_2	formroute_2
Ramipril 10mg capsules	0205051R0AAADAD	26643006	capsule.oral	Ramipril 10mg tablets	0205051R0AAANAN	26643006	tablet.oral

i.e. it finds that ramipril 10mg tablets are a suitable alternative formulation.

Full SQL is here:

WITH drugs AS (
    SELECT 
        vmp.id, 
        nm, 
        bnf_code, 
        pres_stat, 
        route, 
        CASE WHEN formroute.descr LIKE 'solutioninfusion%' then 'solutioninfusion' -- simplify multiple infusion routes as 'infusion'
        WHEN formroute.descr LIKE 'solutioninjection%' then 'solutioninjection' -- simplify multiple injection routes as 'injection'
        ELSE formroute.descr END AS formroute,
        udfs, 
        STRING_AGG(
            CONCAT(
                COALESCE(CAST(ing AS STRING), 'NULL'), '|', -- ingredient code
                COALESCE(CAST(basis_strnt AS STRING), 'NULL'), '|',  -- strength basis
                COALESCE(CAST(strnt_nmrtr_val AS STRING), 'NULL'), '|', -- strength numerator value
                COALESCE(CAST(strnt_nmrtr_uom AS STRING), 'NULL'), '|',  -- strength numerator unit
                COALESCE(CAST(strnt_dnmtr_val AS STRING), 'NULL'), '|',  -- strength denominator value
                COALESCE(CAST(strnt_dnmtr_uom AS STRING), 'NULL'), '|', -- strength denominator unit
                COALESCE(CAST(route AS STRING), 'NULL'), '|',-- route
                COALESCE(CAST(pres_stat AS STRING), 'NULL'), '|',-- prescribing suitable for primary care value
                COALESCE(CASE WHEN formroute.descr NOT LIKE '%.oral%' THEN CAST(udfs AS STRING) ELSE 'NULL' END, 'NULL'), '|', -- if not oral meds, then ensure unit doses are the same (e.g. injections)
                CASE WHEN formroute.descr LIKE '%modified-release%' THEN 'MR' ELSE 'NULL' END -- add 'modified release' flag on match string, so that non modified-release preps aren't matched with standard release
            ), 
            ',' 
            ORDER BY ing, basis_strnt
        ) AS vpi_string 
    FROM 
        dmd.vpi AS vpi
    INNER JOIN 
        dmd.vmp AS vmp ON vpi.vmp = vmp.id
    INNER JOIN
        dmd.droute AS droute ON vmp.id = droute.vmp
    INNER JOIN
        dmd.ont AS ont ON vmp.id = ONT.vmp
    INNER JOIN
        dmd.ontformroute AS formroute ON ont.form = formroute.cd
    WHERE bnf_code IS NOT NULL --make sure all drugs have BNF code
    AND (non_avail != 1 OR non_avail is NULL) -- make sure all drugs are available
    AND strnt_nmrtr_val IS NOT NULL -- make sure all drugs have a strength
    AND pres_stat = 1 -- must be "suitable for prescribing in primary care", e.g. no cautions on switching preparations
    GROUP BY 
        vmp.id, nm, bnf_code, pres_stat, route, formroute, udfs
)
SELECT 
        drugs_1.nm AS nm_1, 
        drugs_1.bnf_code AS bnf_code_1, 
        drugs_1.route AS route_1,        
        drugs_1.formroute AS formroute_1,       
        drugs_2.nm AS nm_2, 
        drugs_2.bnf_code AS bnf_code_2, 
        drugs_2.route AS route_2,   
        drugs_2.formroute AS formroute_2,  
    FROM 
        drugs AS drugs_1
    INNER JOIN 
        drugs AS drugs_2 
        ON drugs_1.vpi_string = drugs_2.vpi_string -- only where strings are the same, i.e. same ingredients, strength, and route
    WHERE 
        drugs_1.id != drugs_2.id -- so don't get duplication

Although it's not yet perfectly matching the fields in the old formulation_swaps.csv file, this could be resolved easily, when we get nearer deployment.

2. Mapping generic to brands

As described above, due to differences in BNF code hierarchy, not everything is currently being mapped. However, by using VMP to AMP mapping, this can be resolved.

Logic

• VMP will match to all AMP codes
• AMPs must have different BNF code (as some ghost branded generics are listed, but with generic BNF code)
• AMPs must be available
• VMP prescribing status must be suitable for prescribing in primary care without brand
• VMP must be available

Using this logic, for Hypromellose 0.3% eye drops (1108010F0AAAAAA) returns this:

nm	bnf_code	nm_1	bnf_code_1
Hypromellose 0.3% eye drops	1108010F0AAAAAA	Mandanol eye drops	21300000114
Hypromellose 0.3% eye drops	1108010F0AAAAAA	Lumecare Hypromellose 0.3% eye drops	21300000117
Hypromellose 0.3% eye drops	1108010F0AAAAAA	Xailin Hydrate 0.3% eye drops	21300000169
Hypromellose 0.3% eye drops	1108010F0AAAAAA	Ocufresh Hypromellose 0.3% eye drops	21300000188
Hypromellose 0.3% eye drops	1108010F0AAAAAA	Teardew 0.3% eye drops	21300000308
Hypromellose 0.3% eye drops	1108010F0AAAAAA	AaproMel 0.3% eye drops	21300000937
Hypromellose 0.3% eye drops	1108010F0AAAAAA	AacuLose Hypromellose 0.3% eye drops	21300000950
Hypromellose 0.3% eye drops	1108010F0AAAAAA	Ocufresh-C 0.3% eye drops	21300000979
Hypromellose 0.3% eye drops	1108010F0AAAAAA	Puroptics Hypromellose 0.3% eye drops	21300000985

As you can see, none of these brands would have appeared under the old bnf_code "9+2" mapping.

Full SQL is here:

SELECT
    vmp.nm AS generic_bnf_name,
    vmp.bnf_code AS generic_bnf_code, 
    amp.nm AS brand_bnf_name, 
    amp.bnf_code AS brand_bnf_code
FROM
    dmd.vmp AS vmp
INNER JOIN
    dmd.amp AS amp
ON
    vmp.id = amp.vmp
WHERE
    vmp.bnf_code <> amp.bnf_code -- doesn't pick up AMPs with generic BNF codes, as these aren't in the prescribing data
AND amp.avail_restrict =1 -- make sure the brand is available for dispensing
AND vmp.pres_stat = 1
AND (vmp.non_avail != 1 OR vmp.non_avail is NULL) -- make sure all drugs are available

Next steps

Creating the SQL-based formulation swaps map results in about 10x the number of lines in the old Google Sheets version. They look reasonable, but further testing is required. @inglesp @evansd it would be great to understand from your perspective what else is needed, and your views on the best way to test.

[richiecroker]

I have now modified the formulation swap SQL to mirror the Google Sheets version (minus spaces in the column names and the "really equivalent?" column, as it's not necessary):

WITH drugs AS (
    SELECT 
        vmp.id, 
        nm, 
        bnf_code, 
        pres_stat, 
        route, 
        CASE WHEN formroute.descr LIKE 'solutioninfusion%' then 'solutioninfusion' -- simplify multiple infusion routes as 'infusion'
        WHEN formroute.descr LIKE 'solutioninjection%' then 'solutioninjection' -- simplify multiple injection routes as 'injection'
        ELSE formroute.descr END AS formroute,
        udfs, 
        form.descr AS form,
        STRING_AGG(
            CONCAT(
                COALESCE(CAST(ing AS STRING), 'NULL'), '|', -- ingredient code
                COALESCE(CAST(basis_strnt AS STRING), 'NULL'), '|',  -- strength basis
                COALESCE(CAST(strnt_nmrtr_val AS STRING), 'NULL'), '|', -- strength numerator value
                COALESCE(CAST(strnt_nmrtr_uom AS STRING), 'NULL'), '|',  -- strength numerator unit
                COALESCE(CAST(strnt_dnmtr_val AS STRING), 'NULL'), '|',  -- strength denominator value
                COALESCE(CAST(strnt_dnmtr_uom AS STRING), 'NULL'), '|', -- strength denominator unit
                COALESCE(CAST(route AS STRING), 'NULL'), '|',-- route
                COALESCE(CAST(pres_stat AS STRING), 'NULL'), '|',-- prescribing suitable for primary care value
                COALESCE(CASE WHEN formroute.descr NOT LIKE '%.oral%' THEN CAST(udfs AS STRING) ELSE 'NULL' END, 'NULL'), '|', -- if not oral meds, then ensure unit doses are the same (e.g. injections)
                CASE WHEN formroute.descr LIKE '%modified-release%' THEN 'MR' ELSE 'NULL' END -- add 'modified release' flag on match string, so that non modified-release preps aren't matched with standard release
            ), 
            ',' 
            ORDER BY ing, basis_strnt
        ) AS vpi_string 
    FROM 
        dmd.vpi AS vpi
    INNER JOIN 
        dmd.vmp AS vmp ON vpi.vmp = vmp.id
    INNER JOIN
        dmd.droute AS droute ON vmp.id = droute.vmp
    INNER JOIN
        dmd.ont AS ont ON vmp.id = ONT.vmp
    INNER JOIN
        dmd.ontformroute AS formroute ON ont.form = formroute.cd
    INNER JOIN
        dmd.dform AS dform ON vmp.id = dform.vmp
    INNER JOIN
        dmd.form AS form ON dform.form = form.cd
    WHERE bnf_code IS NOT NULL --make sure all drugs have BNF code
    AND (non_avail != 1 OR non_avail is NULL) -- make sure all drugs are available
    AND strnt_nmrtr_val IS NOT NULL -- make sure all drugs have a strength
    AND pres_stat = 1 -- must be "suitable for prescribing in primary care", e.g. no cautions on switching preparations
    GROUP BY 
        vmp.id, nm, bnf_code, pres_stat, route, formroute, udfs, form
)
SELECT 
        
        drugs_1.bnf_code AS Code, 
        drugs_1.nm AS Name, 
        drugs_2.bnf_code AS Alternative_code, 
        drugs_1.form AS Formulation,     
        drugs_2.nm AS Alternative_name,    
        drugs_2.form AS Alternative_formulation,         
    FROM 
        drugs AS drugs_1
    INNER JOIN 
        drugs AS drugs_2 
        ON drugs_1.vpi_string = drugs_2.vpi_string -- only where strings are the same, i.e. same ingredients, strength, and route
    WHERE 
        drugs_1.id != drugs_2.id -- so don't get duplication

[inglesp]

I should think the best way to test this would be to create a new spreadsheet with the same structure as the original. We can then update formulation_swaps.csv with the curl command here, commit the change, and deploy.

If there's a problem with the new data (perhaps there are too many rows and performance suffers), we can easily revert the change and redeploy.

[richiecroker]

Thanks @inglesp that works for the substitution sets, but what about the VMP to AMP mapping?

[inglesp]

I'd assumed, without proper consideration, that the VMP to AMP mapping could be handled in the same way, but it doesn't have the concept of an alternative formulation (obviously). So that's not going to work.

[richiecroker]

Issues raised by @chrisjwood16 on first test iteration:

• Sertraline 100mg/5ml oral suspension Concentrate for oral solution / Oral suspension
  Never come across the concentrate before. It's for per dose home dilution - draw up the dose and mix with 120ml water. So might be suitable for careful switches. It's the only concentrate in the list but I think we'd need to be careful with these.

• Urea 5% cream Cutaneous cream / Cutaneous liquid / Shampoo
  This seems to link shampoo with cream with scalp application (cutaneous liquid).
  Might need to have a little think about these. I wouldn't compare cream with shampoo. Cutaneous liquid is a bit of a grey area - these seem to be mostly scalp applications except betnovate lotion which is a bit more vague. I can do some more digging on topical routes but perhaps we match creams/ointments only for topicals?

• Alprostadil 10microgram powder and solvent for solution for injection vials
  This links to Alprostadil 10microgram powder and solvent for solution for injection pre-filled disposable devices. Switching pre-filled pen to vial generally not something
  be done. Both are listed as "Powder and solvent for solution for injection" as the pre-filled pen contains both bits to combine within the pen.

• Dorzolamide 20mg/ml eye drops and other eye drops
  I think we might be able to code in the preservative free status of drops. So in formulation swaps it would show as Eye drops / Eye drops preservative free

• Colestyramine 4g oral powder sachets
  Possibly same as above but Powder for oral suspension / Powder for oral suspension sugar free

• Phosphates enema (Formula B) 128ml long tube / Phosphates enema (Formula B) 128ml standard tube
  Probably a good reason for choosing between the 2?

• Melatonin 1mg/ml oral drops Oral drops / Oral solution / Oral suspension
  This looks to be ok. But are we happy with oral drops -> oral solution options for all? I think it's probably fine.

• Acetylcysteine 600mg effervescent tablets Effervescent tablet / Oral capsule / Oral tablet
  Is this suggesting to swap away from effervescent tablets? I can only see effervescent tablets in the BNF - are the others unlicensed?

[richiecroker]

latest version of code to fix some of the issues

WITH drugs AS (
    SELECT 
        vmp.id, 
        nm, 
        bnf_code, 
        pres_stat, 
        droute.route, 
        pres_f,
        sug_f, 
        CASE WHEN formroute.descr LIKE 'solutioninfusion%' then 'solutioninfusion' -- simplify multiple infusion routes as 'infusion'
        WHEN formroute.descr LIKE 'solutioninjection%' then 'solutioninjection' -- simplify multiple injection routes as 'injection'
        ELSE formroute.descr END AS formroute,
        udfs, 
        form.descr AS form,
        STRING_AGG(
            CONCAT(
                COALESCE(COALESCE(CAST (bs_subid AS STRING), (CAST(ing AS STRING))), 'NULL'), '|', -- ingredient code, maps to base if it exists
                COALESCE(CAST(strnt_nmrtr_val AS STRING), 'NULL'), '|', -- strength numerator value
                COALESCE(CAST(strnt_nmrtr_uom AS STRING), 'NULL'), '|',  -- strength numerator unit
                COALESCE(CAST(strnt_dnmtr_val AS STRING), 'NULL'), '|',  -- strength denominator value
                COALESCE(CAST(strnt_dnmtr_uom AS STRING), 'NULL'), '|', -- strength denominator unit
                COALESCE(CAST(droute.route AS STRING), 'NULL'), '|',-- route
                COALESCE(CAST(pres_stat AS STRING), 'NULL'), '|',-- prescribing suitable for primary care value
                COALESCE(CASE WHEN formroute.descr NOT LIKE '%.oral%' THEN CAST(udfs AS STRING) ELSE 'NULL' END, 'NULL'), '|', -- if not oral meds, then ensure unit doses are the same (e.g. injections)
                CASE WHEN LOWER(formroute.descr) LIKE '%modified-release%' THEN 'MR' ELSE 'NULL' END, '|', -- add 'modified release' flag on match string, so that non modified-release preps aren't matched with standard release
                CASE WHEN LOWER(form.descr) LIKE '%concentrate%' THEN 'conc' ELSE 'NULL' END, '|', -- add 'concentrate' flag on match string, so that non concentrate preps aren't matched with standard preps
                CASE WHEN LOWER(route.descr) LIKE '%cutaneous%' THEN LEFT(formroute.descr, STRPOS(formroute.descr, '.') - 1)  ELSE 'NULL' END -- add type of formulation to cutaneous preps
            ), 
            ',' 
            ORDER BY ing, basis_strnt
        ) AS vpi_string 
    FROM 
        dmd.vpi AS vpi
    INNER JOIN 
        dmd.vmp AS vmp ON vpi.vmp = vmp.id
    INNER JOIN
        dmd.droute AS droute ON vmp.id = droute.vmp
    INNER JOIN
        dmd.route AS route ON route.cd = droute.route
    INNER JOIN
        dmd.ont AS ont ON vmp.id = ONT.vmp
    INNER JOIN
        dmd.ontformroute AS formroute ON ont.form = formroute.cd
    INNER JOIN
        dmd.dform AS dform ON vmp.id = dform.vmp
    INNER JOIN
        dmd.form AS form ON dform.form = form.cd
    WHERE bnf_code IS NOT NULL --make sure all drugs have BNF code
    AND SUBSTR(bnf_code,10,2) = 'AA' --make sure all generic codes
    AND (non_avail != 1 OR non_avail is NULL) -- make sure all drugs are available
    AND strnt_nmrtr_val IS NOT NULL -- make sure all drugs have a strength
    AND pres_stat = 1 -- must be "suitable for prescribing in primary care", e.g. no cautions on switching preparations
    AND nm LIKE 'Ondansetr%'
    GROUP BY 
        vmp.id, nm, bnf_code, pres_stat, pres_f, sug_f, route, formroute, udfs, form
)
SELECT DISTINCT
        
        drugs_1.bnf_code AS Code, 
        drugs_1.nm AS Name, 
        drugs_2.bnf_code AS Alternative_code, 
        CASE WHEN drugs_1.pres_f IS TRUE THEN concat(drugs_1.form, ' preservative free')
             WHEN drugs_1.sug_f IS TRUE THEN concat(drugs_1.form, ' sugar free')
             ELSE drugs_1.form END AS Formulation,     
        CASE WHEN drugs_2.pres_f IS TRUE THEN concat(drugs_2.form, ' preservative free')
             WHEN drugs_2.sug_f IS TRUE THEN concat(drugs_2.form, ' sugar free') ELSE drugs_2.form END AS Alternative_formulation,
        "Y" AS Really_equivalent         
    FROM 
        drugs AS drugs_1
    INNER JOIN 
        drugs AS drugs_2 
        ON drugs_1.vpi_string = drugs_2.vpi_string -- only where strings are the same, i.e. same ingredients, strength, and route
    WHERE 
        drugs_1.id != drugs_2.id -- so don't get duplication
        and drugs_1.nm LIKE 'Ondansetr%'
    AND
        drugs_1.bnf_code != drugs_2.bnf_code -- remove duplication where two different VMP types have same BNF code (e.g. solution/suspension)
    ORDER BY drugs_1.bnf_code

[evansd]

Thanks for the detailed and very clear write-up, Rich! Just to note for other people's benefit the things we've already discussed elsewhere:

• Replacing the formulation swaps spreadsheet and the BNF-based generic-to-brand mapping with SQL queries using dm+d should be fairly straightforward, once you're happy the SQL's correct.
• There'll be a little bit of work in translating the SQL from BQ-flavour to Postgres-flavour, but that shouldn't be too much trouble.
• It should also be possible to update the spending calculation to account for the tariff category discount and the existence of concessions based on the work we've already done for the price concessions tool.
• In the meantime, sharing a copy of the site running off my laptop as a poor man's staging server seems to be working OK as a way of checking that the SQL's working as expected.