Procedures Workflow
Pre requists:
- Load into separate WampServer tables OPS for years 2015 to latest year
- Hospitals dataset from Hostinger
- The full PX hospital dataset
- PX opsall table
- Fetch the procedure data from Bob
Remove any unwanted years:
DELETE FROM proc_germany_all
WHERE `year`='2016';
The raw procedural data most likely will include codes that start with come with the full range 0 to 9 inclusive:
-- List distinct first characters from the code column
SELECT DISTINCT LEFT(code, 1) AS first_character
FROM proc_germany_all
ORDER BY first_character;
Codes starting with 0, 2, 4 and 7 must be removed as these are not recognised in the offical OPS coding system, which leaves the following chapters:
|
Chapter |
Code-Range |
Class Title |
|
1 |
1-00... 1-99 |
Diagnostische Maßnahmen (Procedures for medical diagnosis) |
|
3 |
3-03... 3-99 |
Bildgebende Diagnostik (Medical imaging) |
|
5 |
5-01... 5-99 |
Operationen (Surgical procedures) |
|
6 |
6-00... 6-00 |
Medikamente (Drugs, medicaments and biological agents) |
|
8 |
8-01... 8-99 |
Nichtoperative therapeutische Maßnahmen (Other therapeutic procedures) |
|
9 |
9-20... 9-99 |
Ergänzende Maßnahmen (Ancillary procedures) |
Running the following SQL code will achieve this:
DELETE FROM proc_germany_all
WHERE code LIKE '0%'
OR code LIKE '2%'
OR code LIKE '4%'
OR code LIKE '7%';
Check codes exist in the table and if not find and add them to the PX opsall table. Run Assign missing OPS codes to PX OPSALL v0.1.php.
This PHP script will attempt to find / add descriptions to the column called description. If none are found after searching the offical OPS tables then a entry Description not found is entered. Note! remember to translate the description. Some codes can't be added due to their exclusivity (one reason is that they might be entered incorrectly) and these are usually that appear with <5 rows. These codes can be safely removed from the the procedural data. Check for any blank cells in all the opsall dataset.
Use the following SQL code to build / populate a custom table (built by yourself i.e. proc_germany_all_distinct_code_opsall_check) the following table:

-- Check if code exits in table t2 and if so set flag to y
UPDATE proc_germany_all_distinct_code_opsall_check t1
JOIN procedure_codes_opsall_2026 t2
ON t1.code = t2.code
SET t1.in_opsall_2026 = 'y';
-- Populate the appearance column with how many times the code appears (rows) in the data
UPDATE proc_germany_all_distinct_code_opsall_check t
JOIN (
SELECT
code,
COUNT(*) AS code_count
FROM proc_germany_all
GROUP BY code
) c ON t.code = c.code
SET t.appearance_in_proc_germany_all = c.code_count;
-- Add the total cases for the code
UPDATE proc_germany_all_distinct_code_opsall_check d
JOIN (
SELECT
code,
SUM(cases) AS total_cases
FROM proc_germany_all
GROUP BY code
) p ON d.code = p.code
SET d.total_cases = p.total_cases;
Check for any blank cells in all the dataset.
Process any duplicates by running Duplicate table entry processing v0.1.php
Sync the procedural PX serials with the hospital dataset by running STEP 1 - Serial bidirectional matching v0.1.php. Then manually check for any blank cells in all the dataset. Serials that can be removed and may include:
4849 (this is not s typo)
494034
494058
494086
494123
494138
494145
494159
494167
494175
494181
494188
494193
494194
494198
494200
494202
494206
494207
494210
494211
494212
494213
494214
494217
494229
494232
494238
494258
494261
494271
494273
494301
494307
494447
Topic last reviewed: 14/03/26