Exploratory Data Analysis - 4

SQL Query

My queries will pull from NSQIP 2005-2019.

In order to generate the main dataframe, I have constructed a query to pull only those patients that have undergone one of the above four procedures without any concomitant procedures and only with a single CPT code (a few records had multiple of these four CPT codes):

select *
from (
    select caseid, trim(col, 'cpt_') cpt 
    from
    (
        select caseid, 
        cpt_44140, cpt_44141, cpt_44143, cpt_44144, cpt_44145, cpt_44146, cpt_44204, cpt_44206, cpt_44207, cpt_44208
    from
    (
        select caseid,
            countif(cpt = "44140") cpt_44140,
            countif(cpt = "44141") cpt_44141,
            countif(cpt = "44143") cpt_44143,
            countif(cpt = "44144") cpt_44144,
            countif(cpt = "44145") cpt_44145,
            countif(cpt = "44146") cpt_44146,
            countif(cpt = "44204") cpt_44204,
            countif(cpt = "44206") cpt_44206,
            countif(cpt = "44207") cpt_44207,
            countif(cpt = "44208") cpt_44208,
        from nsqip.puf_cpt
        group by caseid
        having not logical_or(cpt not in ("44140", "44141","44143", "44144","44145", "44146", "44204", "44206", "44207", "44208"))
    )
    where (cpt_44140 + cpt_44141 + cpt_44143 + cpt_44144 + cpt_44145 + cpt_44146 + cpt_44204 + cpt_44206 + cpt_44207 + cpt_44208) = 1
    )
    unpivot (cpt for col in (cpt_44140, cpt_44141, cpt_44143, cpt_44144, cpt_44145, cpt_44146, cpt_44204, cpt_44206, cpt_44207, cpt_44208))
    where cpt !=0    
) cpt

join (
    select *
    from nsqip.puf
) puf
using(caseid)

join (
    select *
    from nsqip.col
) col
using(caseid)

left join (
    select distinct(caseid), TRUE as readmission from nsqip.puf_readmission
    where readmission is true
) readm
using(caseid)

left join (
    select distinct(caseid), TRUE as reoperation from nsqip.puf_reoperation
    where reoperation is true
) reop
using(caseid)

I will build a companion dataframe for readmissions:

select *
from (
    select caseid, trim(col, 'cpt_') cpt 
    from
    (
        select caseid, 
        cpt_44140, cpt_44141, cpt_44143, cpt_44144, cpt_44145, cpt_44146, cpt_44204, cpt_44206, cpt_44207, cpt_44208
    from
    (
        select caseid,
            countif(cpt = "44140") cpt_44140,
            countif(cpt = "44141") cpt_44141,
            countif(cpt = "44143") cpt_44143,
            countif(cpt = "44144") cpt_44144,
            countif(cpt = "44145") cpt_44145,
            countif(cpt = "44146") cpt_44146,
            countif(cpt = "44204") cpt_44204,
            countif(cpt = "44206") cpt_44206,
            countif(cpt = "44207") cpt_44207,
            countif(cpt = "44208") cpt_44208,
        from nsqip.puf_cpt
        group by caseid
        having not logical_or(cpt not in ("44140", "44141","44143", "44144","44145", "44146", "44204", "44206", "44207", "44208"))
    )
    where (cpt_44140 + cpt_44141 + cpt_44143 + cpt_44144 + cpt_44145 + cpt_44146 + cpt_44204 + cpt_44206 + cpt_44207 + cpt_44208) = 1
    )
    unpivot (cpt for col in (cpt_44140, cpt_44141, cpt_44143, cpt_44144, cpt_44145, cpt_44146, cpt_44204, cpt_44206, cpt_44207, cpt_44208))
    where cpt !=0    
) cpt

join nsqip.puf_readmission
using(caseid)
where readmission is true

and for reoperations:

select *
from (
    select caseid, trim(col, 'cpt_') cpt 
    from
    (
        select caseid, 
        cpt_44140, cpt_44141, cpt_44143, cpt_44144, cpt_44145, cpt_44146, cpt_44204, cpt_44206, cpt_44207, cpt_44208
    from
    (
        select caseid,
            countif(cpt = "44140") cpt_44140,
            countif(cpt = "44141") cpt_44141,
            countif(cpt = "44143") cpt_44143,
            countif(cpt = "44144") cpt_44144,
            countif(cpt = "44145") cpt_44145,
            countif(cpt = "44146") cpt_44146,
            countif(cpt = "44204") cpt_44204,
            countif(cpt = "44206") cpt_44206,
            countif(cpt = "44207") cpt_44207,
            countif(cpt = "44208") cpt_44208,
        from nsqip.puf_cpt
        group by caseid
        having not logical_or(cpt not in ("44140", "44141","44143", "44144","44145", "44146", "44204", "44206", "44207", "44208"))
    )
    where (cpt_44140 + cpt_44141 + cpt_44143 + cpt_44144 + cpt_44145 + cpt_44146 + cpt_44204 + cpt_44206 + cpt_44207 + cpt_44208) = 1
    )
    unpivot (cpt for col in (cpt_44140, cpt_44141, cpt_44143, cpt_44144, cpt_44145, cpt_44146, cpt_44204, cpt_44206, cpt_44207, cpt_44208))
    where cpt !=0    
) cpt

join nsqip.puf_reoperation
using(caseid)
where reoperation is true

Indication

Now we can start culling away patients via exlusion criteria. The first will be indication - we only want patients with an indication of chronic diverticular disease or acute diverticulitis. I will rely strictly on the field col_indication. Additional fields that I may consider using would be col_indication_icd, podiag, or podiag10. But these are ICD codes; I think using the indication field from the colorectal subset is cleaner.

A total of 17286 patients have chronic diverticular disease or acute diverticulitis. This exclusion criteria eliminates 52970 patients.

Approach

The next exclusion criteria will be non-laparoscopic or robotic approach. This will rely on the variable col_approach. We can easily eliminate all records where col_approach is not equal to “Laparoscopic” or “Robotic”.

A total of 10788 patients undergo laparoscopic or robotic surgery. This exclusion criteria eliminates 6498 patients.

Open assist

The next exclusion criteria will remove all patients with a documented open assist technique because there is no ability to document a conversion to open in NSQIP when open-assist is selected. This will use the col_open_assist variable.

A total of 6905 patients do not have an open assist documented. This exclusion criteria eliminates 3883 patients.

Discordance of approach

The next exclusion criteria will remove patients with some kind of discordance in their recorded approach.

We are including CPT codes for open approaches with the assumption that if the col_approach is laparoscopic, this should indicate a conversion to open. This assumption will be easy to validate with the col_unplanned_conversion variable`.

A total of 800 patients have an open CPT with a laparoscopic or robotic approach and a conversion to open documented. A discordant documented approach eliminates 414 patients.

Elective surgery

The next exclusion criteria will be non-elective surgery. There are multiple fields that contain relevant data - electsurg, emergncy, and col_emergent. I will utilize all of these fields to exclude patients. I will require that electsurg is TRUE while emergncy is FALSE. col_emergent is a factor - I will require that it is equal to NA. Lets take a look at a breakdown of all three variables:

It helps to look at all combinations of these three variables to see why we need to consider all three in our determination of what is non-elective surgery.

A total of NA patients undergo elective surgery. This exclusion criteria eliminates NA patients.

Outpatient

The next exclusion criteria will be inpatient surgery. Only a single variable is relevant to this criteria - inout.

As the table makes clear, only 41 patients undergo outpatient surgery. Therefore, we can not use this exclusion critiera.

Admitted from home

The next exclusion criteria will be not admitted from home. Only a single variable is relevant to this criteria - transt.

A total of 5529 patients are admitted from home. This exclusion criteria eliminates 26 patients.

General Surgery

The next exclusion criteria will be surgical specialty other than general surgery. Only a single variable is relevant - surgspec.

A total of 5506 patients are operated on by a general surgeon. This exclusion criteria eliminates 23 patients.

Co-morbidities

There are various co-morbidities and pre-operative conditions that we will also use to exclude patients. These are:

• Diabetes (diabetes)
• Pre-op ventilator use (ventilat)
• Pre-op ascites (ascites)
• History of CHF (hxchf)
• History of MI (hxmi)
• Hypertension requiring medication (hypermed)
• Pre-op disseminated cancer (discancr)
• Pre-op sepsis/septic shock (type_prsepis)

#>   diabetes        ventilat        ascites          hxchf        
#>  Mode :logical   Mode :logical   Mode :logical   Mode :logical  
#>  FALSE:4951      FALSE:5506      FALSE:5506      FALSE:5495     
#>  TRUE :555                                       TRUE :11       
#>     hxmi          hypermed        discancr       type_prsepis 
#>  Mode :logical   Mode :logical   Mode :logical   Sepsis:  17  
#>  FALSE:2         FALSE:3207      FALSE:5488      SIRS  :  33  
#>  NA's :5504      TRUE :2299      TRUE :18        NA's  :5456

A total of 3049 patients do not have any of these excluding co-morbid conditions. This exclusion criteria eliminates 2457 patients.

#>   diabetes        ventilat        ascites          hxchf           hxmi        
#>  Mode :logical   Mode :logical   Mode :logical   Mode :logical   Mode:logical  
#>  FALSE:3049      FALSE:3049      FALSE:3049      FALSE:3049      NA's:3049     
#>   hypermed        discancr       type_prsepis
#>  Mode :logical   Mode :logical   SIRS:  14   
#>  FALSE:3049      FALSE:3049      NA's:3035

Incomplete data

We need to exclude patients that are missing required data:

• Height (height)
• Weight (weight)

A total of 3039 patients do not have incomplete data. This exclusion criteria eliminates 10 patients.

BMI

We must now eliminate patients with BMIs outside of the desired ranges (<30, 35-40). This will use the variable bmi.

A total of 2217 patients are within the desired BMI range. This exclusion criteria eliminates 822 patients.