Improving the Measurement of Surgical Site Infection Risk Stratification/Outcome Detection

Appendix C. Data Dictionary

Table Field Datatype Description
PATIENT     Include patients who had surgery that meet inclusion CPT,SNOMED, or ICD-9 criteria between 1/1/2007 and 1/30/2009
PATIENT DOB Date The birthdate for the patient
PATIENT PATIENT_ID Integer A unique ID for the patient
PATIENT DATA_SOURCE_ID Varchar(10) An identifier for the source of the patient record data (UU, IHC, DH for example)
DIAGNOSIS     Include ICD-9 CM discharge codes within one month of surgery. A list of included codes is in table 2 of Stevenson et al. AJIC vol 36(3) 155-164.
DIAGNOSIS DIAGNOSIS_ID Integer A unique ID for the diagnosis
DIAGNOSIS DIAGNOSIS_CODE Varchar(64) The code for the patient's diagnosis
DIAGNOSIS DIAGNOSIS_CODE_SOURCE Varchar(64) The nomenclature that the diagnosis code is taken from (ICD9, etc.)
DIAGNOSIS CLINICAL_DTM Date The date and time of the diagnosis's onset or exacerbation
MICROBIOLOGY     Include all Microbiology specimens taken within one month before or after a surgery. (For risk, this might be expanded to one-year or more.)
MICROBIOLOGY MICRO_ID Integer A unique ID for the procedure
MICROBIOLOGY SPECIMEN_CODE Varchar(64) The site that the specimen was collected from
MICROBIOLOGY SPECIMEN_CODE_SOURCE Varchar(64) The nomenclature that the specimen code is taken from (SNOMED, LOINC, etc.)
MICROBIOLOGY PATHOGEN_CODE Varchar(64) The code of the pathogen cultured from the collected specimen
MICROBIOLOGY PATHOGEN_CODE_SOURCE Varchar(64) The nomenclature that the pathogen code is taken from (SNOMED, LOINC, etc.)
MICROBIOLOGY COLLECT_DTM Date The date and time the specimen was collected
ENCOUNTER     Include all Encounters within one month before or after surgery.
ENCOUNTER ENCOUNTER_ID Integer A unique ID for the visit.  This will server to tie all of the different data tables together via foreign key relationship.
ENCOUNTER ADMIT_DTM Date The admission date and time for a patient's visit
ENCOUNTER DISCH_DTM Date The discharge date and time for a patient's visit
ENCOUNTER ENCOUNTER_TYPE Varchar(64) The type of patient encounter such as inpatient, outpatient, observation, etc.
PROCEDURE     Include all procedures that meet inclusion CPT, SNOMED, or ICD-9 criteria between 1/1/2007 and 1/30/2009
PROCEDURE PROCEDURE_ID Integer A unique ID for the procedure
PROCEDURE CLINICAL_DTM Date The date and time that the procedure was performed
PROCEDURE PROC_CODE Varchar(64) The code (ICD9, CPT4, SNOMED etc.) or description for the procedure that was performed
PROCEDURE PROC_CODE_SOURCE Varchar(64) The nomenclature that the procedure code is taken from (ICD9, CPT4, SNOMED etc.)
PROCEDURE SITE_CODE Varchar(64) The code (ICD9, CPT4, SNOMED etc.) or description for the site where the procedure was performed
PROCEDURE SITE_CODE_SOURCE Varchar(64) The nomenclature that the site code is taken from (ICD9, CPT4, SNOMED etc.)
ANTIBIOTIC     Include all antibiotics that were given one month before or after surgery
ANTIBIOTIC ABX_ID Integer A unique ID for the antibiotic
ANTIBIOTIC NAME Varchar(64) The name of the antibiotic given
ANTIBIOTIC DOSE Varchar(64) The dose of the antibiotic
ANTIBIOTIC FREQUENCY Varchar(64) The frequency of the antibiotic
ANTIBIOTIC START_DTM Date The date of when the antibiotic treatment began
ANTIBIOTIC DC_DTM Date The date of when the antibiotic treatment ended
VITAL_SIGN     Include the lowest systolic and diastolic blood pressures, and highest pulse and temperature 2 days before and after each culture
VITAL_SIGN VS_ID Integer A unique ID for the vital sign
VITAL_SIGN TEMPERATURE Double Patient's temperature for determination of present infection
VITAL_SIGN TEMP_SCALE Varchar(64) The scale that the temperature was taken in (Celsius, Fahrenheit)
VITAL_SIGN TEMP_DTM Date The date and time the temperature was taken
LAB     Include the most extreme laboratory values from tests of interest within 2 days before and after each culture. Potential tests of interest: blood leukocyte count, erythrocyte sedimentation rate, c-reactive protein, and procalcitonin.
LAB LAB_ID Integer A unique ID for the lab
LAB TEST_NAME Varchar(64) The name or type of lab test
LAB RESULT Varchar(64) The results from the lab test
LAB COLLECT_DTM Date The date and time the specimen was collected
LAB RESULT_DTM Date The date and time the lab test was resulted
LAB SPECIMEN_CODE Varchar(64) The site that the specimen was collected from
LAB SPECIMEN_CODE_SOURCE Varchar(64) The nomenclature that the specimen code is taken from (SNOMED, LOINC, etc.)
OUTCOMES - FROM NSQIP or OTHER Include patients who had surgery that meet inclusion CPT,SNOMED, or ICD-9 criteria between 1/1/2007 and 1/30/2009  
OUTCOMES PATIENT_ID Integer A unique ID for the patient
OUTCOMES DATA_SOURCE_ID Varchar(10) An identifier for the source of the patient record data (VA, IHC, DH, VA-NSQIP for example)
OUTCOMES SURG_ID Varchar(10) A unique identifier for all surgeries performed at an institution
OUTCOMES SURG_DATE Date Date and time of surgery start
OUTCOMES SURG_CODE Varchar(10) Surgery Codes associated with SURG_ID
OUTCOMES SURG_CODE_TYPE Varchar(10) ICD-9, CPT or other
OUTCOMES WOUND_CLASS Varchar(64) Clean, Clean/Contaminated, Contaminated, Dirty/Infected
OUTCOMES S_INCIS_SSI Date Enter date when superficial incisional SSI found
OUTCOMES D_INCIS_SSI Date Enter date when deep incisional SSI found
OUTCOMES ORG_SSI Date Enter date when an organ space SSI found
OUTCOMES SYS_SEPSIS Date Enter date when patient suffered systemic sepsis
OUTCOMES SEPSIS Date Enter date when patient suffered sepsis
OUTCOMES SEPTIC_SHOCK Date Enter date when patient suffered septic shock
RISK PATIENT_ID Integer A unique ID for the patient
RISK DATA_SOURCE_ID Varchar(10) An identifier for the source of the patient record data (VA, IHC, DH, VA-NSQIP for example)
RISK SURG_ID Varchar(10) A unique identifier for all surgeries performed at an institution
RISK SURG_DATE Date Date and time of surgery start
Page last reviewed December 2012
Internet Citation: Appendix C. Data Dictionary. December 2012. Agency for Healthcare Research and Quality, Rockville, MD. http://www.ahrq.gov/research/findings/final-reports/ssi/ssiapc.html