Under the Freedom of Information Act 2000, I was wondering if you would be able to develop on top of the FOI Request FOI 24442 and FOI 27689.
The data in this request relates to April 2020 to March 2022 and April 2022 to June 2022 from the data source ‘NHSBSA Information Services Data Warehouse’ with the Columns YEAR_MONTH, PRACTICE_CODE, DISPENSER_CODE, BNF_CODE, PRODUCT_ORDER_NUMBER, PACK_ORDER_NUMBER and NIC_GBP.
Would it be possible to have the data in the same format from July 2022 to December 2022 or from July 2022 to the latest possible month please?
Please note that prescription data for December prescriptions is not available as those prescriptions have not been processed. The latest month available is November 2022.
July to November 2022
A copy of the information is attached.
NHS Prescription Services process prescriptions for Pharmacy Contractors, Appliance Contractors, Dispensing Doctors and Personal Administration with information then used to make payments to pharmacists and appliance contractors in England for prescriptions dispensed in primary care settings (other arrangements are in place for making payments to Dispensing Doctors and Personal Administration). This involves processing over 1 billion prescription items and payments totalling over £9 billion each year. The information gathered from this process is then used to provide information on costs and trends in prescribing in England and Wales to over 25,000 registered NHS and Department of Health and Social Care users.
Data Source - The data source was the NHSBSA Information Services Data Warehouse.
The Data excludes:
• Items not dispensed, disallowed and those returned to the contractor for further clarification.
• Prescriptions prescribed and dispensed in Prisons, Hospitals and Private prescriptions.
• Items prescribed but not presented for dispensing or not submitted to NHS Prescription Services by the dispenser.
Please note drugs flagged in our database as Schedule 2 and 3 controlled drugs have been excluded from this dataset for information security reasons to prevent the identification of organisations with high levels of controlled drug dispensing.
Whilst Schedule 2 and 3 controlled drugs are excluded from the data search, note that as appliance contractors are unable to dispense controlled drugs, these drugs would never have appeared in the data provided.
Time Period - July 2022 to November 2022
PRACTICE CODE - No limits are placed on prescribing practice. The symbol (-) is used for dummy practices (for example unidentified doctors or Scottish doctors)
Dispenser codes are limited to the following;
FA133,FA696,FAA96,FAH84,FAV47,FC714,FC850,FC879,FCE87,FCH11,FCY62,FD135,FDA28,FDC49,FDR75,FE401,FE807,FEC67,FEW49,FF468,FFF14,FFY66,FFY68,FG020,FGD92,FGR26,FGW36,FH261,FH272,FH421,FH767,FH804,FHC99,FHE90,FHT00,FHY81,FJ469,FJ708,FJE85,FJF97,FJH75,FJM24,FJM43,FJM85,FJN80,FJT66,FJV34,FK593,FKM09,FKV20,FL755,FLD15,FLD64,FLE21,FLE95,FLJ41,FLV51,FM982,FMD74,FMG28,FMK11,FMV40,FMV84,FMY99,FND22,FNE35,FNE89,FNJ81,FPC27,FPE43,FPM97,FPP28,FPP56,FPW87,FQ588,FQ608,FQT79,FR040,FR158,FR739,FRF82,FRQ51,FRX15,FT468,FT553,FTD28,FTL51,FTM68,FTQ92,FTR18,FV104,FV224,FVQ58,FVT91,FVW18,FW285,FWC51,FWE39,FWL22,FWN04,FWN89,FWP06,FWP87,FXF12,FXF13,FXG29,FXN29,FXW55,FXW61,FYL81 and FYR20
BNF CODE - The 11 characters at the beginning of a BNF code which specifies a Product (generic or branded) but not its strength or formulation.
PRODUCT_ORDER_NUMBER and PACK_ORDER_NUMBER: If this is not held in our database then it will be blank.
NIC_GBP - The cost of the supplied item(s) calculated from the reimbursement price held on the NHSBSA master database. Given in Pounds Sterling.
Accessing CSVs with more than 1 million rows
CSVs with more than 1 million rows can be viewed using add-ons to existing software, such as the Microsoft PowerPivot add-on for Excel, to handle larger data sets. The Microsoft PowerPivot add-on for Excel is available using the link in the 'Related Links' section below.
Once PowerPivot has been installed, to load the large files, please follow the instructions below. Note that it may take at least 20 to 30 minutes to load one monthly file.
Start Excel as normal
Click on the PowerPivot tab
Click on the PowerPivot Window icon (top left)
In the PowerPivot Window, click on the "From Other Sources" icon
In the Table Import Wizard e.g. scroll to the bottom and select Text File
Browse to the file you want to open and choose the file extension you require e.g. CSV
Once the data has been imported you can view it in a spreadsheet.
Within the data, E is being interpreted in Text as Scientific notation, for example 01E00 shown as 1.00E+00
You can fix this as follows:
If you open a new Excel then
- go to Data – From Text
- select the csv file you want
– leave format as delimited
– highlight the CCG column
- change to text
- highlight column with the odd formatting
– change to text
ok when the little import box pops up.
This should let you open it without the formatting issues.
Please note that this request and our response is published on our Freedom of Information disclosure log at:
If you have any queries regarding the data provided, or if you plan on publishing the data, please contact email@example.com ensuring you quote the above reference. This is important to ensure that the figures are not misunderstood or misrepresented.
If you plan on producing a press or broadcast story based upon the data please contact firstname.lastname@example.org. This is important to ensure that the figures are not misunderstood or misrepresented.
The information supplied to you continues to be protected by the Copyright, Designs and Patents Act 1988 and is subject to NHSBSA copyright. This information is licenced under the terms of the Open Government Licence detailed at:
Should you wish to re-use the information you must include the following statement:
“NHSBSA Information Services Data Warehouse, NHSBSA Copyright 2023” This information is licenced under the terms of the Open Government Licence:
Failure to do so is a breach of the terms of the licence.
Information you receive which is not subject to NHSBSA Copyright continues to be protected by the copyright of the person, or organisation, from which the information originated.
Please obtain their permission before reproducing any third party (non NHSBSA Copyright) information.