search

ebmdatalab / open-nhs-hospital-use-data

For analysis of https://opendata.nhsbsa.net/dataset/secondary-care-medicines-data
0 stars 0 forks source link

How do we aggregate and compare volumes? #2

Open brianmackenna opened 4 years ago

brianmackenna commented 4 years ago

In primary care data we use items as a comparator of volume in primary care but no value exists in SCMD. The fields included in SCMD related to volume include

UNIT_OF_MEASURE_IDENTIFIER UNIT_OF_MEASURE_NAME TOTAL_QUANITY_IN_VMP_UNIT

We need to be able to

  1. aggregate at chemical level or other groupings (e.g. for paracetamol there will be mix of tabs, caps, IV, liquids in each hosp)
  2. compare between chemicals

For 1 - we can calculate the amount of mg for each preparation For 2 - we could then convert amount to Daily Defined Doses

Q. Is there another method we could use?

Other useful things / questions

inglesp commented 4 years ago

is there an automated feed / database of DDDs we can use?

Not that I've come across.

You can search for DDDs here: https://www.whocc.no/atc_ddd_index/. It might be possible to get a dump of the full list from the site, although I can't see where.

https://www.whocc.no/atc_ddd_index_and_guidelines/atc_ddd_index/ says:

Lists of the annual ATC/DDD alterations are distributed in November/December each year free of charge to the users of the ATC/DDD system according to a mailing list. Please contact the Centre if you want to receive this information (whocc@fhi.no).

If not, we can scrape it.

inglesp commented 4 years ago

And we have dm+d to ATC is in TRUD: https://isd.digital.nhs.uk/trud3/user/guest/group/0/pack/6/subpack/25/releases.

inglesp commented 4 years ago

Or you can order it as XML for €200. (Paper copy €60...)

https://www.whocc.no/atc_ddd_index_and_guidelines/order/

brianmackenna commented 4 years ago

@sebbacon has previously done related sleuthing on this related to ADQs. The BSA also indicated in their ODR publications that ADQs / DDDs are being reviewed for publication. We also know from users that they can access ADQ / DDD information via ePACT2 (though it is not 100% complete)

From a cursory look at the TRUD files it appears that DDDs are contained in the supplementary (aka dmdbonus) zipped files. There is xml file and taking Carbocisteine 250mg/5ml oral solution sugar free (dmd browser link) it reports the following

VMP>

<VPID>38658711000001109</VPID>

<ATC>R05CB03</ATC>

<DDD>1.5</DDD>

<DDD_UOMCD>258682000</DDD_UOMCD>

For our first few analysis where it requires meds to be compared across chemicals / strengths /preparations I think we should attempt to use the TRUD file?

inglesp commented 4 years ago

So there is. (I looked at this a couple of weeks ago with Rich...)

There's a CSV file with a recent dump of the XML at https://ebmdatalab.slack.com/archives/C31D62X5X/p1598257887042600?thread_ts=1598221229.041100&cid=C31D62X5X.

richiecroker commented 4 years ago

I've taken a quick look at this tonight.

There are VMP level DDD values here which may be useful - although one caveat is that combination products don't seem to be well served (e.g. co-codamol does not have a DDD attached, although paracetamol and codeine do as separate products. It may be possible to extract a full list of ATC DDD values from here.

I have also used the VPI data in DM+D before to get total mg of a drug. I can't find the original notebook, but @HelenCEBM used the concept in the hospital stock notebook:

SELECT
  DISTINCT bnf_code,
  nm, --vmp name
  ing, --ingredient
  strnt_nmrtr_val/COALESCE(strnt_dnmtr_val,
    1) AS strnt, -- ingredient concentration (e.g. 40mg/5ml = 8)
  vmp.udfs, -- denominator of concentration (e.g. 5) 
  vmp.udfs_uom -- unit of measure (e.g. ml)
FROM
  ebmdatalab.dmd.vmp_full AS vmp
INNER JOIN
  ebmdatalab.dmd.vpi AS vpi
ON
  vmp.id=vpi.vmp

You still need to be careful when thinking about combination products. You can separate these out by joining to hscic.ing which has specific chemical names.

For example running the above code for co-codamol 500/30mg tablets (0407010F0AAAHAH) will return Row bnf_code nm ing strnt udfs udfs_uom
1 0407010F0AAAHAH Co-codamol 30mg/500mg tablets 387517004 500 1 tablet
2 0407010F0AAAHAH Co-codamol 30mg/500mg tablets 261000 30 1 tablet 
SELECT
  DISTINCT bnf_code,
  vmp.nm as vmp_nm, --vmp name
  vpi.ing, --ingredient
  strnt_nmrtr_val/COALESCE(strnt_dnmtr_val,
    1) AS strnt, -- ingredient concentration (e.g. 40mg/5ml = 8)
  vmp.udfs, -- denominator of concentration (e.g. 5) 
  vmp.udfs_uom, -- unit of measure (e.g. ml)
  ing.nm as ing_nm --ingredient name
FROM
  ebmdatalab.dmd.vmp_full AS vmp
INNER JOIN
  ebmdatalab.dmd.vpi AS vpi
ON
  vmp.id=vpi.vmp
INNER JOIN
  dmd.ing as ing
ON
  ing.id = vpi.ing
  where bnf_code = '0407010F0AAAHAH'

will return

Row bnf_code vmp_nm ing strnt udfs udfs_uom ing_nm
1 0407010F0AAAHAH Co-codamol 30mg/500mg tablets 387517004 500 1 tablet Paracetamol
2 0407010F0AAAHAH Co-codamol 30mg/500mg tablets 261000 30 1 tablet Codeine phosphate

BUT, i don't think there's a foolproof way to 100% map DDDs (for combo products) to the ATC/DDD table. It probably wouldn't take too long to create a manual table.

richiecroker commented 4 years ago

Ideally we would want to create a DDD table mapped to ing as we could then calculate mg and DDD for each component of combination products as well as single chemical items.

Jongmassey commented 2 years ago

Here's what's on the WHO DDD website for combination products present in the SCMD dataset (as of Jan 2021) https://docs.google.com/spreadsheets/d/1_hnX24ETg0QDD5RslB2XOTGURl3yTK4O-vMDtnC_NwI

The ingredient mentioned in the "notes" field would need manually mapping to ing SNOMED codes

The route of administration forms would also need mapping to SNOMED codes

Route of administration (Adm.R)

Implant = Implant
Inhal = Inhalation
Instill = Instillation
N = nasal
O = oral
P = parenteral
R = rectal
SL = sublingual/buccal/oromucosal
TD = transdermal
V = vaginal

Jongmassey commented 2 years ago

gist for conversion of NHSD xml DDD to csv