Exploratory Data Analysis - 2

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_44143, cpt_44145, cpt_44146, cpt_44204, cpt_44206, cpt_44207, cpt_44208
    from
    (
        select caseid,
            countif(cpt = "44140") cpt_44140,
            countif(cpt = "44143") cpt_44143,
            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","44143","44145", "44146", "44204", "44206", "44207", "44208"))
    )
    where (cpt_44140 + cpt_44143 + cpt_44145 + cpt_44146 + cpt_44204 + cpt_44206 + cpt_44207 + cpt_44208) = 1
    )
    unpivot (cpt for col in (cpt_44140, cpt_44143, 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_44143, cpt_44145, cpt_44146, cpt_44204, cpt_44206, cpt_44207, cpt_44208
    from
    (
        select caseid,
            countif(cpt = "44140") cpt_44140,
            countif(cpt = "44143") cpt_44143,
            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","44143","44145", "44146", "44204", "44206", "44207", "44208"))
    )
    where (cpt_44140 + cpt_44143 + cpt_44145 + cpt_44146 + cpt_44204 + cpt_44206 + cpt_44207 + cpt_44208) = 1
    )
    unpivot (cpt for col in (cpt_44140, cpt_44143, 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_44143, cpt_44145, cpt_44146, cpt_44204, cpt_44206, cpt_44207, cpt_44208
    from
    (
        select caseid,
            countif(cpt = "44140") cpt_44140,
            countif(cpt = "44143") cpt_44143,
            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","44143","44145", "44146", "44204", "44206", "44207", "44208"))
    )
    where (cpt_44140 + cpt_44143 + cpt_44145 + cpt_44146 + cpt_44204 + cpt_44206 + cpt_44207 + cpt_44208) = 1
    )
    unpivot (cpt for col in (cpt_44140, cpt_44143, 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. 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 9719 patients have chronic diverticular disease. This exclusion criteria eliminates 56839 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 7886 patients undergo laparoscopic or robotic surgery. This exclusion criteria eliminates 1833 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 4906 patients do not have an open assist documented. This exclusion criteria eliminates 2980 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 452 patients have an open CPT with a laparoscopic or robotic approach and a conversion to open documented. A discordant documented approach eliminates 220 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 4407 patients undergo elective surgery. This exclusion criteria eliminates 279 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 28 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 4387 patients are admitted from home. This exclusion criteria eliminates 20 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 4365 patients are operated on by a general surgeon. This exclusion criteria eliminates 22 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:3905      FALSE:4365      FALSE:4365      FALSE:4357     
#>  TRUE :460                                       TRUE :8        
#>     hxmi          hypermed        discancr       type_prsepis 
#>  Mode :logical   Mode :logical   Mode :logical   Sepsis:   7  
#>  FALSE:2         FALSE:2547      FALSE:4352      SIRS  :  24  
#>  NA's :4363      TRUE :1818      TRUE :13        NA's  :4334

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

#>   diabetes        ventilat        ascites          hxchf           hxmi        
#>  Mode :logical   Mode :logical   Mode :logical   Mode :logical   Mode:logical  
#>  FALSE:2417      FALSE:2417      FALSE:2417      FALSE:2417      NA's:2417     
#>   hypermed        discancr       type_prsepis
#>  Mode :logical   Mode :logical   SIRS:  10   
#>  FALSE:2417      FALSE:2417      NA's:2407

Incomplete data

We need to exclude patients that are missing required data:

• Height (height)
• Weight (weight)

A total of 2408 patients do not have incomplete data. This exclusion criteria eliminates 9 patients.

BMI

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

A total of 1071 patients are within the desired BMI range. This exclusion criteria eliminates 1337 patients.