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