General requirements: SQLape users must prepare a SQLape_input.txt file, which is the unique input file required. In this file, each row corresponds to a new hospital stay and must include all the variables listed in the table below. Fields name should not be included in the file (first line of the file = first record). Each field must be separated by a semicolon (“;”) and each record must be ended by a carriage return compatible with Windows. If it is not the case, open the file with the windows’ application “Bloc-note” and save it.
SQLape® is updated every year with the latest valid international nomenclatures: ICD-10 (WHO version) for diagnosis and ICD-9-CM (US version). Former codes are transcoded in updated codes to allow temporal comparisons (from 1998 to now). (Swiss nomenclatures)
| Fields | Swiss Federal Office of Statistics designation (Swiss nomenclatures) | Format | ||
|---|---|---|---|---|
| Field 1 | Hospital stay identifier | - | text | |
| Field 2 | Hospital identifier | 0.1.V02 | text | |
| Field 3 | Location | 0.1.V03 | text | |
| Field 4 | Patient identifier | 0.2.V01 | text | |
| Field 5 | Class (payer) | 1.3.V02 | number | integer |
| Field 6 | Gender | 1.1.V01 | number | integer |
| Field 7 | Age at admission | 1.1.V03 | number | integer |
| Field 8 | Residence | 1.1.V04 | text | |
| Field 9 | Admission date | 1.2.V01 | YYYYMMDDhh | (text) |
| Field 10 | Stay before admission | 1.2.V02 | Number | integer |
| Field 11 | Admission mode | 1.2.V03 | Number | integer |
| Field 12 | Type of stay | 1.3.V01 | Number | integer |
| Field 13 | Vacations | 1.3.V04 | Number | integer |
| Field 14 | Department | 1.4.V01 | text | |
| Field 15 | Discharge date* | 1.5.V01 | YYYYMMDDhh | (text) |
| Field 16 | Discharge decision | 1.5.V02 | number | integer |
| Field 17 | Stay after discharge | 1.5.V03 | number | integer |
| Field 18 | Length of stay | - | number | integer |
| Field 19 | Hospital cost | - | number | integer (without cents) |
| Field 20 | Weight at birth | 2.2.V04 | number | integer |
| Field 21 | Main diagnosis | 4.2.V010 | text | |
| Field 22 | Complement to main diagnosis | 4.2.V020 | text | |
| Fields 23-71 | Diagnoses 2 to 50 | 4.2.V030-4.2.V510 | text | |
| Field 72 | Procedure 1 | 4.3.V010 | text | |
| Field 73 | Procedure 2 | 4.3.V020 | text | |
| Fields 74-171 | Procedures 3 to 100 | 4.3.V030-4.3.V1000 | text | |
| Field 172 | Procedure 1 date | 4.3.V015 | YYYYMMDD | (text) |
| Field 173 | Procedure 2 date | 4.3.V025 | YYYYMMDD | (text) |
| Fields 174-271 | Procedure 3 to 100 dates | 4.3.V035-4.3.V1005 | YYYYMMDD | (text) |
| Field 272 | 1 st interruption, discharge date | 4.7.V01 | YYYYMMDD | (text) |
| Field 273 | 1 st interruption, readmission date | 4.7.V02 | YYYYMMDD | (text) |
| Field 274 | Reason of 1 st interruption | 4.7.V03 | number | integer |
| Field 275 | 2 nd interruption, discharge date | 4.7.V11 | YYYYMMDD | (text) |
| Field 276 | 2 nd interruption, readmission | 4.7.V12 | YYYYMMDD | (text) |
| Field 277 | Reason of 2 nd interruption | 4.7.V13 | number | integer |
| Field 278 | 3 rd interruption, discharge date | 4.7.V21 | YYYYMMDD | (text) |
| Field 279 | 3 rd interruption, readmission | 4.7.V22 | YYYYMMDD | (text) |
| Field 280 | Reason of 3 rd interruption | 4.7.V23 | number | integer |
| Field 281 | 4 th interruption, discharge date | 4.7.V31 | YYYYMMDD | (text) |
| Field 282 | 4 th interruption, readmission | 4.7.V32 | YYYYMMDD | (text) |
| Field 283 | Reason of 4 th interruption | 4.7.V33 | number | integer |
| Field 284 | Stay after the first interruption | 4.8.V17 | number | integer |
| Field 285 | Stay after the second interruption | 4.8.V18 | number | integer |
| Field 286 | Stay after the third interruption | 4.8.V19 | number | integer |
| Field 287 | Stay after the fourth interruption | 4.8.V20 | number | integer |
Important technical specifications: The stay identifier must be unique in the whole SQLape_input file, not only for each hospital or site. Do not use the underscore “_” character in the identifier.
The patient identifier must be unique for the whole calculation period, independently of the year (important to calculate readmissions’ rate).
Missing dates must be empty (no character).
ICD-10 diagnostic and ICD-9-CM procedure codes can be used with or without point (47.01 or 4701 for instance are both acceptable).
ICD-9-CM codes must be in text format (0109 is different from 109 for instance)
Fields 18 and 19 are only required to compute length of stay and hospital cost. They can be kept empty for other indicators. Hospital cost is not included in hospital medical records; this information corresponds to the total cost computed for SwissDRGs. The length of stay is defined as follows:
Discharge date – Admission date + 1 – Vacation duration (hours/24).
Temporal delimitation: The usual setting includes all discharges from January 1st to December 31th, but shorter periods might also be used. If several years are included in the SQLape_input.txt file, the analysis will take into account the discharges of the more recent year if they represent more than 10% of cases; otherwise, the analysis will be applied to the year including the most cases.
If you are interested in readmissions indicators, please also consider following recommendations:
- To compute unbiased observed rates, the input file must include other available hospitals to identify external readmissions.
- To compute unbiased expected rates, the input file must be extended to June 1stof the previous year. This extension is necessary to adjust for possible six months previous hospital stays.
Data quality assessment: It is recommended to assess the quality of data before to interpret the results (data quality)
Tips: If you want to have results with your own hospital and patient identifiers or medical services you may need to update the fields 1, 4 or 14.
If you are not interested in results per location, you can use the field 3 to use more detailed units (maximum of 50).