search

ebmdatalab / prescribing-queries

Documentation about common queries against the prescribing dataset
GNU General Public License v3.0
3 stars 3 forks source link

readme

Prescribing queries

Documentation about common queries against the prescribing dataset.

Using BigQuery

You will need a Google account with the correct permissions (set up by an administrator) to access our BigQuery account.

Then follow this quickstart (ignoring the first section "Before you begin")

Legacy vs Standard

BigQuery provides a SQL-like interface to massive datasets. It has two dialects, "Legacy" and "standard". When running a query, the default is "Legacy"; you must select "standard" in the options section to use that. Nearly all the examples here are in "standard" format, which is compatible with standard SQL. However, sometimes it is necessary to use "Legacy" format as some functions have not yet been ported by Google to the newer format.

Standard SQL supports temp tables which can make your queries more readable than using lots of subqueries.

Legacy SQL used to have a more extensive range of aggregate functions, and in particular, window functions. However, OpenPrescribing is now able to use standard SQL exclusively.

A comparison between the two formats is here.

Billing

BigQuery is billed by the amount of data queried. Querying the entire prescribing table costs about 20 cents. You should bear these costs in mind if running large numbers of queries. Good practice is to extract, say, one month of data to a new table to design your queries, e.g. running

SELECT *
FROM ebmdatalab.hscic.prescribing
WHERE month = TIMESTAMP('2016-06-01')

...and selecting "save to table". If you then save this to a table ebmdatalb.tmp.<something>, then you can continue to design your query like:

SELECT *
FROM ebmdatalab.tmp.<something>
WHERE bnf_code LIKE '02%'
LIMIT 1000

Overview of tables in BigQuery

Practice settings

The different kinds of practice available in the setting column of the practices table are as follows:

The prescribing table and BNF Codes

The datain the prescribing table covers prescriptions prescribed by GPs and other non-medical prescribers (nurses, pharmacists and others) in England and dispensed in the community in the UK. Prescriptions written in England but dispensed outside England are included.

The format of the data in the prescribing table is documented by the NHS here.

The unique identifier for the item prescribed is the bnf_code.

The BNF (British National Formulary) is the de facto standard list of medicines used UK prescribing. Maintained by the British Medical Association and the Royal Pharmaceutical Society, it lists indications, dosages, side effects and so on for more than 70,000 medicines.

The prescribing data uses a modified version of the BNF which was current in 2014, with custom additions and alterations. The resulting system is called a BNF pseudo-classification and is described here. In particular, appliances are not listed in the BNF at all, so are included in the pseudo-classification with sections named DUMMY SECTION.

The first characters of the code provide a hierarchical classification of the presentation.

The last few characters identify individual presentations, and a way of identifying their generic equivalents.

The image below shows how you might examine Tramadol. Tramadol is an opiod pain medicine, available in the UK as tablets (i.e. pills), capsules (i.e. gelatine things), patches, liquids and more. Just focussing on tablets, these are available as standard tablets, and modified release tablets (which are absorbed by the body over a longer period of time, allowing the patient to take less frequent doses). Modified release tablets are available in 50mg, 100mg, 150mg, 200mg, 300mg and 400mg pills.

image