Show me the data! What does the SCMD look like?

In our previous series of blogs, OpenPrescribing Hospitals: Getting Started, we explained what the Secondary Care Medicines Data is and how it is updated. Now that we understand what it is, let’s have a look at some actual data from the SCMD.

What fields does the data contain?

Each monthly data file within the SCMD contains around 300,000 rows of data, with each row representing the quantity of an individual medicine or device issued by a single NHS Trust in that month. Below is some sample data for Apixaban 5mg tablets.

There are 8 fields for each row:

• YEAR_MONTH - The year and month the usage is aggregated over.
• ODS_CODE - Organisation Data Service (ODS) Code, a unique identifier for NHS Trusts included in the dataset. See our previous blog, Organising Organisations: Hospital Trusts in OpenPrescribing Hospitals for more details.
• VMP_SNOMED_CODE - Virtual Medicinal Product SNOMED CT code, a unique identifier to identify medicines and devices within the dm+d. You can read more about the difference between SNOMED CT and dm+d in our blog, Difference between BNF, dm+d and SNOMED CT codes.
• VMP_PRODUCT_NAME - Virtual Medicinal Product name, the description associated with the code above.
• UNIT_OF_MEASURE_IDENTIFIER - A unique identifier for the unit of measure of the recorded quantity.
• UNIT_OF_MEASURE_NAME - The description associated with the identifier above.
• TOTAL_QUANTITY_IN_VMP_UNIT - The total quantity in the specified UNIT_OF_MEASURE of the VMP issued in the specified month and NHS Trust.
• INDICATIVE_COST - Cost derived from current medicines pricing data held in NHSBSA data systems multiplied by the quantity issued within the specified month, in pounds and pence. This does not reflect the actual cost paid by hospitals (see below).

We’ll dive into the details associated with each of these fields in later blogs, but below we have generated some high level statistics about the fields to get a feel for what they include.

ODS codes (NHS Trusts)

As of September 2024, there were 224 unique ODS codes included in the SCMD. The mix of organisations submitting to the SCMD includes acute, specialist, mental health and community trusts, though the type of organisation is not indicated by its ODS code. Acute trusts prescribe a much wider variety of medicines than typical mental health or community trusts. You can read more about organisations within the dataset in our blog series Organising Organisations.

VMP SNOMED code

As of September 2024, 12,537 unique VMP codes had been reported within the SCMD. The number of codes reported each month varies as new medicines or devices can be introduced, discontinued or are used rarely and only recorded in a subset of months. In September 2024, there were 8,431 unique codes recorded.

The chart below shows that the monthly numbers of unique VMPs is broadly stable over time with between 8k and 9k unique VMPs.

The small variations in number of unique VMPs is shown in the chart below, most notably the lower variety of VMPs used during the COVID-19 pandemic (zoomed in: note the axis values no longer start at zero).

We hinted above that different types of NHS trusts issue different numbers of unique products each month. In the chart below, you can see how the number of products issued per NHS Trust in a single month is distributed.

This shows that there is wide variation in the number of products used, representative of the different types of NHS Trusts included in the dataset; large acute trusts are expected to issue a greater number of different products than a specialist mental health trust.

As well as different types of trusts issuing different numbers of unique products, the mix of products individual trust issue also changes over time. So while few trusts use more than 3,000 products in any single month, they may have used nearly twice as many over the complete period the SCMD covers as new formulations arrive, old ones are withdrawn and clinical practice changes. This is shown in the “all time” use chart below.

Unit of measure

The unit of measure field has 52 distinct values. These are shown in the table below, with an indication of the number of products reported in each unit.

Looking at these units, you can see that the unit the quantity is reported in will impact how quantities of different products can be aggregated. Whilst some units, such as tablets and capsules can often be comparable, others will not be (how should “capsules” be compared to “litres”?). This will be one of the major challenges we face when building OpenPrescribing Hospitals.

Note, also, that the units make it obvious that not everything is a medicine. There are devices and unusual treatments in the data (“larvae”, for example, reflects the use of sterile maggots in wound debridement).

Quantity

The quantity field in the SCMD data is the sum of the number of units issued for the specific product according to the units indicated in the unit of measure (UOM) field. This is useful for simple analyses but often requires additional information for a better understanding. For example, if a drug is available as tablets of multiple strengths, the quantity of tablets alone will not tell you the amount of the drug used. Or, if the drug is available in multiple forms (eg a tablet, a patch, an injectable) then analysis will require more consideration.

This table shows all the fentanyl containing products used in 2023 with quantity greater than 5,000 to illustrate the point.

Unit of measure	Product name	Quantity
litre	Bupivacaine 250mg/250ml (0.1%) / Fentanyl 500micrograms/250ml infusion bags	6,693
	Fentanyl 2.5mg/50ml solution for infusion vials	6,994
	Fentanyl 100micrograms/2ml solution for injection ampoules	6,372
	Fentanyl 500micrograms/10ml solution for injection ampoules	5,583
	Levobupivacaine 250mg/250ml / Fentanyl 500micrograms/250ml infusion bags	14,415
lozenge	Fentanyl 200microgram lozenges	11,942
patch	Fentanyl 12micrograms/hour transdermal patches	98,029
	Fentanyl 25micrograms/hour transdermal patches	86,670
	Fentanyl 37.5microgram/hour transdermal patches	8,686
	Fentanyl 50micrograms/hour transdermal patches	39,326
	Fentanyl 75micrograms/hour transdermal patches	13,983
	Fentanyl 100micrograms/hour transdermal patches	15,473
tablet	Fentanyl 100microgram buccal tablets sugar free	12,911
	Fentanyl 100microgram sublingual tablets sugar free	45,546
	Fentanyl 200microgram sublingual tablets sugar free	26,592
	Fentanyl 400microgram buccal tablets sugar free	8,647
	Fentanyl 400microgram sublingual tablets sugar free	15,172
	Fentanyl 800microgram buccal tablets sugar free	7,422
	Fentanyl 800microgram sublingual tablets sugar free	8,047

Comparing different strength tablets, patches and litres of injectable products by directly adding quantities does not make sense so conversion to consistent measures of use is needed.

Another thing to be aware of with quantity is that they can be negative. This is due to backtracking, the process by which unused stock issued in previous months can be subtracted from the recorded quantities in subsequent months. If the quantity being subtracted is more than the quantity issued in that month, it can result in negative values.

Indicative Cost

The SCMD includes the indicative cost for each medicine or device within the dataset. The indicative cost is based on the community pharmacy reimbursement prices for generic and list prices for branded medicines and is calculated by multiplying the quantity issued by this cost. This does not reflect the actual cost paid by hospitals as each hospital can have different purchasing prices based on confidential price agreements. The Rx-Info platform, Define provides access to actual cost information for authorised users.

Indicative cost is the only field in the SCMD data with consistent units across all formulations and variants in the dataset. But, since it does not represent the actual NHS spend, it cannot be used reliably to analyse total spend. Nor, strictly, can it be used to compare spend across multiple drugs as the confidential discounts can be very different. Because of this, the data on spend has limited use, though the data can be helpful for analysts in initially scoping which formulations have the most usage.

This is our first look into the SCMD. We will write more on this data as we further explore interesting features in it. If you have observations or topics of interest, please tell us about them. We hope the ability to explore this dataset rapidly will be widely useful.