In UCCC/McLeod: Package for generating tables from CHORDS data for Surgery to Recovery project
options(scipen=999, digits = 5, width = 88)
options(qwraps2_markup = "markdown")
options(stringAsFactors = F)
options(dplyr.print_max = 10)
options(dplyr.pring_min = 5)
knitr::opts_chunk$set(cache = TRUE,
echo = FALSE,
results = "hide",
message = FALSE,
warning = FALSE)
startTime <- Sys.time()
library(knitr)
library(tidyverse)
library(qwraps2)
con <- DBI::dbConnect(odbc::odbc(),
Driver = "SQL Server",
Server = "cc-s-d05.ucdenver.pvt",
Database = "VDW_3_1_DH_0418")
start_dt <- "2011-01-01"
end_dt <- "2016-12-31"
## helper functions
# returns a vector with the column numbers of character variables in the data frame
charVars <- function(df) grep('^ch',sapply(df,class))
# trims all character variables in a dataframe, sets blank to NA
trimChrVars <- function(df){
for(i in charVars(df)){
df[,i] <- gsub('\\s+$','',df[,i])
df[,i] <- ifelse(nchar(df[,i])==0,NA,df[,i])
}
df
}
# age category calculator
ageCatCalc <- function(age){
ageCat <- factor(
ifelse(is.na(age)==TRUE, 0,
ifelse(age<0 , 1,
ifelse(age>=0 & age<2, 2,
ifelse(age>=2 & age<5, 3,
ifelse(age>=5 & age<10, 4,
ifelse (age>=10 & age<15, 5,
ifelse(age>=15 & age<19, 6,
ifelse(age>=19 & age<22, 7,
ifelse(age>=22 & age<45, 8,
ifelse(age>=45 & age<65, 9,
ifelse(age>=65 & age<75, 10,
ifelse(age>=75 & age<90, 11,
ifelse(age>=90 , 12, 13)))))) ))))))),
levels=0:13,
labels=c('Missing','Negative','0-1','2-4','5-9','10-14','15-18','19-21','22-44','45-64','65-74','75-89','90+','Other')
)
return(ageCat)
}
# Provider specialties
specialties <- chordsTables::specialties
# Medication Names
med_names <- read_csv("Q:/MattCM/CHORDS/surg2rec/med-names.csv")
devtools::use_data(med_names)
# Provider type
prov_type <- chordsTables::prov_type
names(prov_type) <- c("PROVIDER_TYPE", "prov_desc")
#Create long version of codes file
codes <- readr::read_csv("Q:/MattCM/CHORDS/surg2rec/inclusion codes.csv")
codes2 <- codes %>%
tidyr::gather(.) %>%
dplyr::filter(complete.cases(.)) %>%
dplyr::rename(code_type = key, code = value) %>%
dplyr::mutate(
spine = if_else(stringr::str_detect(code_type, "spine"), 1, 0),
pectus = if_else(stringr::str_detect(code_type, "pectus"), 1, 0),
ProcedureType = if_else(spine == 1, "Spine", "Pectus")
)
Table Set 1: Facility Demographics
Table 1A. Surgeries by Year and Surgery Type
This table contains the number of procedures performed by year and type of procedure
tab1a <- odbc::dbGetQuery(con,
"SELECT
DISTINCT z.ENC_ID,
z.PERSON_ID,
MAX(CASE
WHEN z.PX IN('0RG4070', '0RG4071', '0RG407J', '0RG40A0', '0RG40A1', '0RG40AJ', '0RG40J0', '0RG40J1', '0RG40JJ', '0RG40K0', '0RG40K1', '0RG40KJ', '0RG40Z0', '0RG40Z1', '0RG40ZJ', '0RG6070', '0RG6071', '0RG607J', '0RG60A0', '0RG60A1', '0RG60AJ', '0RG60J0', '0RG60J1', '0RG60JJ', '0RG60K0', '0RG60K1', '0RG60KJ', '0RG60Z0', '0RG60Z1', '0RG60ZJ', '0RG7070', '0RG7071', '0RG707J', '0RG70A0', '0RG70A1', '0RG70AJ', '0RG70J0', '0RG70J1', '0RG70JJ', '0RG70K0', '0RG70K1', '0RG70KJ', '0RG70Z0', '0RG70Z1', '0RG70ZJ', '0RG8070', '0RG8071', '0RG807J', '0RG80A0', '0RG80A1', '0RG80AJ', '0RG80J0', '0RG80J1', '0RG80JJ', '0RG80K0', '0RG80K1', '0RG80KJ', '0RG80Z0', '0RG80Z1', '0RG80ZJ', '0RGA070', '0RGA071', '0RGA07J', '0RGA0A0', '0RGA0A1', '0RGA0AJ', '0RGA0J0', '0RGA0J1', '0RGA0JJ', '0RGA0K0', '0RGA0K1', '0RGA0KJ', '0RGA0Z0', '0RGA0Z1', '0RGA0ZJ', '0SG0070', '0SG0071', '0SG007J', '0SG00A0', '0SG00A1', '0SG00AJ', '0SG00J0', '0SG00J1', '0SG00JJ', '0SG00K0', '0SG00K1', '0SG00KJ', '0SG00Z0', '0SG00Z1', '0SG00ZJ', '0SG1070', '0SG1071', '0SG107J', '0SG10A0', '0SG10A1', '0SG10AJ', '0SG10J0', '0SG10J1', '0SG10JJ', '0SG10K0', '0SG10K1', '0SG10KJ', '0SG10Z0', '0SG10Z1', '0SG10ZJ', '0SG3070', '0SG3071', '0SG307J', '0SG30A0', '0SG30A1', '0SG30AJ', '0SG30J0', '0SG30J1', '0SG30JJ', '0SG30K0', '0SG30K1', '0SG30KJ', '0SG30Z0', '0SG30Z1', '0SG30ZJ', '81', '81.01', '81.02', '81.03', '81.04', '81.05', '81.06', '81.07', '81.08', '81.09', '81.3', '81.35', '81.36', '81.37', '81.38', '81.39', '81.61', '81.62', '81.63', '81.64', '84.51', '22532', '22533', '22548', '22551', '22554', '22556', '22558', '22586', '22590', '22595', '22600', '22610', '22612', '22614', '22630', '22633', '22634', '22800', '22802', '22804', '22808', '22810', '22812', '22818', '22819', '22830', '22840', '22841', '22842', '22843', '22844', '22845', '22846', '22847', '22848', '22849', '22850', '22852', '22853', '22854', '22855', '22856', '22857', '22858', '22859', '22861', '22862', '22864', '22865', '22867', '22868', '22869', '22870')
THEN 1 ELSE 0 END) AS spine,
MAX(CASE
WHEN z.PX IN('0WU80JZ', '0WU84JZ', '34.74', '21740', '21742', '21743')
THEN 1 ELSE 0 END) AS pectus,
z.PROCDATE,
YEAR(z.ADATE) as px_date
FROM
DEMOGRAPHICS a
INNER JOIN [PROCEDURES] z
ON z.PERSON_ID = a.PERSON_ID
WHERE z.ADATE >= '01/01/2011'
AND DATEDIFF(YY, a.BIRTH_DATE, z.ADATE) - CASE
WHEN DATEADD(YY, DATEDIFF(YY, a.BIRTH_DATE, z.ADATE), a.BIRTH_DATE) > z.ADATE
THEN 1
ELSE 0
END >= 10
AND DATEDIFF(YY, a.BIRTH_DATE, z.ADATE) - CASE
WHEN DATEADD(YY, DATEDIFF(YY, a.BIRTH_DATE, z.ADATE), a.BIRTH_DATE) > z.ADATE
THEN 1
ELSE 0
END <= 26
AND
z.PX IN('0RG4070', '0RG4071', '0RG407J', '0RG40A0', '0RG40A1', '0RG40AJ', '0RG40J0', '0RG40J1', '0RG40JJ', '0RG40K0', '0RG40K1', '0RG40KJ', '0RG40Z0', '0RG40Z1', '0RG40ZJ', '0RG6070', '0RG6071', '0RG607J', '0RG60A0', '0RG60A1', '0RG60AJ', '0RG60J0', '0RG60J1', '0RG60JJ', '0RG60K0', '0RG60K1', '0RG60KJ', '0RG60Z0', '0RG60Z1', '0RG60ZJ', '0RG7070', '0RG7071', '0RG707J', '0RG70A0', '0RG70A1', '0RG70AJ', '0RG70J0', '0RG70J1', '0RG70JJ', '0RG70K0', '0RG70K1', '0RG70KJ', '0RG70Z0', '0RG70Z1', '0RG70ZJ', '0RG8070', '0RG8071', '0RG807J', '0RG80A0', '0RG80A1', '0RG80AJ', '0RG80J0', '0RG80J1', '0RG80JJ', '0RG80K0', '0RG80K1', '0RG80KJ', '0RG80Z0', '0RG80Z1', '0RG80ZJ', '0RGA070', '0RGA071', '0RGA07J', '0RGA0A0', '0RGA0A1', '0RGA0AJ', '0RGA0J0', '0RGA0J1', '0RGA0JJ', '0RGA0K0', '0RGA0K1', '0RGA0KJ', '0RGA0Z0', '0RGA0Z1', '0RGA0ZJ', '0SG0070', '0SG0071', '0SG007J', '0SG00A0', '0SG00A1', '0SG00AJ', '0SG00J0', '0SG00J1', '0SG00JJ', '0SG00K0', '0SG00K1', '0SG00KJ', '0SG00Z0', '0SG00Z1', '0SG00ZJ', '0SG1070', '0SG1071', '0SG107J', '0SG10A0', '0SG10A1', '0SG10AJ', '0SG10J0', '0SG10J1', '0SG10JJ', '0SG10K0', '0SG10K1', '0SG10KJ', '0SG10Z0', '0SG10Z1', '0SG10ZJ', '0SG3070', '0SG3071', '0SG307J', '0SG30A0', '0SG30A1', '0SG30AJ', '0SG30J0', '0SG30J1', '0SG30JJ', '0SG30K0', '0SG30K1', '0SG30KJ', '0SG30Z0', '0SG30Z1', '0SG30ZJ', '81', '81.01', '81.02', '81.03', '81.04', '81.05', '81.06', '81.07', '81.08', '81.09', '81.3', '81.35', '81.36', '81.37', '81.38', '81.39', '81.61', '81.62', '81.63', '81.64', '84.51', '0WU80JZ', '0WU84JZ', '34.74', '22532', '22533', '22548', '22551', '22554', '22556', '22558', '22586', '22590', '22595', '22600', '22610', '22612', '22614', '22630', '22633', '22634', '22800', '22802', '22804', '22808', '22810', '22812', '22818', '22819', '22830', '22840', '22841', '22842', '22843', '22844', '22845', '22846', '22847', '22848', '22849', '22850', '22852', '22853', '22854', '22855', '22856', '22857', '22858', '22859', '22861', '22862', '22864', '22865', '22867', '22868', '22869', '22870', '21740', '21742', '21743')
GROUP BY z.PERSON_ID, z.ENC_ID, z.PROCDATE, YEAR(z.ADATE)"
)
tab1a2 <- tab1a %>%
group_by(PERSON_ID) %>%
mutate(
min_dt = min(PROCDATE),
use_dt = if_else(min_dt == PROCDATE, 1, 0)
) %>%
ungroup(.) %>%
dplyr::filter(use_dt == 1) %>%
group_by(px_date) %>%
summarise(spine = sum(spine), pectus = sum(pectus)) %>%
ungroup(.)
tab1a_tot <- odbc::dbGetQuery(con,
"SELECT
count(*) as n_pxs,
YEAR(z.ADATE) as px_date
FROM
DEMOGRAPHICS a
JOIN [PROCEDURES] z
ON z.PERSON_ID = a.PERSON_ID
WHERE z.ADATE >= '01/01/2011'
AND DATEDIFF(YY, a.BIRTH_DATE, z.ADATE) - CASE
WHEN DATEADD(YY, DATEDIFF(YY, a.BIRTH_DATE, z.ADATE), a.BIRTH_DATE) > z.ADATE
THEN 1
ELSE 0
END >= 10
AND DATEDIFF(YY, a.BIRTH_DATE, z.ADATE) - CASE
WHEN DATEADD(YY, DATEDIFF(YY, a.BIRTH_DATE, z.ADATE), a.BIRTH_DATE) > z.ADATE
THEN 1
ELSE 0
END <= 26
GROUP BY YEAR(z.ADATE)
ORDER BY YEAR(z.ADATE)"
)
tab1a_all <- tab1a2 %>%
left_join(tab1a_tot, by = "px_date") %>%
mutate(
spine2 = paste0(spine, " (", round((spine/n_pxs) * 100, 2), "%)"),
pectus2 = paste0(pectus, " (", round((pectus/n_pxs) * 100, 2), "%)"),
total = paste0(spine + pectus, " (", round(((spine + pectus)/n_pxs) * 100, 2), "%)")
) %>%
select(px_date, spine2, pectus2, total)
knitr::kable(tab1a_all, col.names = c("Year", "Spine", "Pectus", "Total"))
Table 1B: Cumulative Demographic Demographic Differences by Facility
Heights and weights in this table represent an average of the heights and weights recorded during the procedure encounter. This was done because 1) height and weight weren't measured at the same time for some individuals and 2) height and weight weren't measured during consistent times for each participant (i.e,g measures could be at any point in the encounter).
tab1b <- odbc::dbGetQuery(con,
"SELECT
DISTINCT z.ENC_ID,
z.PERSON_ID,
MAX(CASE
WHEN z.PX IN('0RG4070', '0RG4071', '0RG407J', '0RG40A0', '0RG40A1', '0RG40AJ', '0RG40J0', '0RG40J1', '0RG40JJ', '0RG40K0', '0RG40K1', '0RG40KJ', '0RG40Z0', '0RG40Z1', '0RG40ZJ', '0RG6070', '0RG6071', '0RG607J', '0RG60A0', '0RG60A1', '0RG60AJ', '0RG60J0', '0RG60J1', '0RG60JJ', '0RG60K0', '0RG60K1', '0RG60KJ', '0RG60Z0', '0RG60Z1', '0RG60ZJ', '0RG7070', '0RG7071', '0RG707J', '0RG70A0', '0RG70A1', '0RG70AJ', '0RG70J0', '0RG70J1', '0RG70JJ', '0RG70K0', '0RG70K1', '0RG70KJ', '0RG70Z0', '0RG70Z1', '0RG70ZJ', '0RG8070', '0RG8071', '0RG807J', '0RG80A0', '0RG80A1', '0RG80AJ', '0RG80J0', '0RG80J1', '0RG80JJ', '0RG80K0', '0RG80K1', '0RG80KJ', '0RG80Z0', '0RG80Z1', '0RG80ZJ', '0RGA070', '0RGA071', '0RGA07J', '0RGA0A0', '0RGA0A1', '0RGA0AJ', '0RGA0J0', '0RGA0J1', '0RGA0JJ', '0RGA0K0', '0RGA0K1', '0RGA0KJ', '0RGA0Z0', '0RGA0Z1', '0RGA0ZJ', '0SG0070', '0SG0071', '0SG007J', '0SG00A0', '0SG00A1', '0SG00AJ', '0SG00J0', '0SG00J1', '0SG00JJ', '0SG00K0', '0SG00K1', '0SG00KJ', '0SG00Z0', '0SG00Z1', '0SG00ZJ', '0SG1070', '0SG1071', '0SG107J', '0SG10A0', '0SG10A1', '0SG10AJ', '0SG10J0', '0SG10J1', '0SG10JJ', '0SG10K0', '0SG10K1', '0SG10KJ', '0SG10Z0', '0SG10Z1', '0SG10ZJ', '0SG3070', '0SG3071', '0SG307J', '0SG30A0', '0SG30A1', '0SG30AJ', '0SG30J0', '0SG30J1', '0SG30JJ', '0SG30K0', '0SG30K1', '0SG30KJ', '0SG30Z0', '0SG30Z1', '0SG30ZJ', '81', '81.01', '81.02', '81.03', '81.04', '81.05', '81.06', '81.07', '81.08', '81.09', '81.3', '81.35', '81.36', '81.37', '81.38', '81.39', '81.61', '81.62', '81.63', '81.64', '84.51', '22532', '22533', '22548', '22551', '22554', '22556', '22558', '22586', '22590', '22595', '22600', '22610', '22612', '22614', '22630', '22633', '22634', '22800', '22802', '22804', '22808', '22810', '22812', '22818', '22819', '22830', '22840', '22841', '22842', '22843', '22844', '22845', '22846', '22847', '22848', '22849', '22850', '22852', '22853', '22854', '22855', '22856', '22857', '22858', '22859', '22861', '22862', '22864', '22865', '22867', '22868', '22869', '22870')
THEN 1 ELSE 0 END) AS spine,
MAX(CASE
WHEN z.PX IN('0WU80JZ', '0WU84JZ', '34.74', '21740', '21742', '21743')
THEN 1 ELSE 0 END) AS pectus,
DATEDIFF(YY, a.BIRTH_DATE, z.ADATE) - CASE
WHEN DATEADD(YY, DATEDIFF(YY, a.BIRTH_DATE, z.ADATE), a.BIRTH_DATE) > z.ADATE
THEN 1
ELSE 0
END AS [AGE],
z.ADATE,
z.PROCDATE,
a.GENDER,
a.PRIMARY_LANGUAGE,
a.RACE1,
b.HT,
b.WT,
b.MEASURE_DATE,
c.GEOCODE,
c.LOC_START,
c.LOC_END,
d.DDATE
FROM
DEMOGRAPHICS a
INNER JOIN [PROCEDURES] z
ON z.PERSON_ID = a.PERSON_ID
LEFT JOIN [ENCOUNTERS] d
ON z.PERSON_ID = d.PERSON_ID AND z.ENC_ID = d.ENC_ID
LEFT JOIN [VITAL_SIGNS] b
ON z.PERSON_ID = b.PERSON_ID AND z.ENC_ID = b.ENC_ID
LEFT JOIN [CENSUS_LOCATION] c
ON z.PERSON_ID = c.PERSON_ID AND
CASE WHEN c.LOC_END IS NOT NULL AND z.ADATE BETWEEN c.LOC_START
AND c.LOC_END THEN 1 WHEN c.LOC_END IS NULL AND c.LOC_START <= z.ADATE THEN 1 ELSE 0 END = 1
WHERE z.ADATE >= '01/01/2011'
AND DATEDIFF(YY, a.BIRTH_DATE, z.ADATE) - CASE
WHEN DATEADD(YY, DATEDIFF(YY, a.BIRTH_DATE, z.ADATE), a.BIRTH_DATE) > z.ADATE
THEN 1
ELSE 0
END >= 10
AND DATEDIFF(YY, a.BIRTH_DATE, z.ADATE) - CASE
WHEN DATEADD(YY, DATEDIFF(YY, a.BIRTH_DATE, z.ADATE), a.BIRTH_DATE) > z.ADATE
THEN 1
ELSE 0
END <= 26
AND
z.PX IN('0RG4070', '0RG4071', '0RG407J', '0RG40A0', '0RG40A1', '0RG40AJ', '0RG40J0', '0RG40J1', '0RG40JJ', '0RG40K0', '0RG40K1', '0RG40KJ', '0RG40Z0', '0RG40Z1', '0RG40ZJ', '0RG6070', '0RG6071', '0RG607J', '0RG60A0', '0RG60A1', '0RG60AJ', '0RG60J0', '0RG60J1', '0RG60JJ', '0RG60K0', '0RG60K1', '0RG60KJ', '0RG60Z0', '0RG60Z1', '0RG60ZJ', '0RG7070', '0RG7071', '0RG707J', '0RG70A0', '0RG70A1', '0RG70AJ', '0RG70J0', '0RG70J1', '0RG70JJ', '0RG70K0', '0RG70K1', '0RG70KJ', '0RG70Z0', '0RG70Z1', '0RG70ZJ', '0RG8070', '0RG8071', '0RG807J', '0RG80A0', '0RG80A1', '0RG80AJ', '0RG80J0', '0RG80J1', '0RG80JJ', '0RG80K0', '0RG80K1', '0RG80KJ', '0RG80Z0', '0RG80Z1', '0RG80ZJ', '0RGA070', '0RGA071', '0RGA07J', '0RGA0A0', '0RGA0A1', '0RGA0AJ', '0RGA0J0', '0RGA0J1', '0RGA0JJ', '0RGA0K0', '0RGA0K1', '0RGA0KJ', '0RGA0Z0', '0RGA0Z1', '0RGA0ZJ', '0SG0070', '0SG0071', '0SG007J', '0SG00A0', '0SG00A1', '0SG00AJ', '0SG00J0', '0SG00J1', '0SG00JJ', '0SG00K0', '0SG00K1', '0SG00KJ', '0SG00Z0', '0SG00Z1', '0SG00ZJ', '0SG1070', '0SG1071', '0SG107J', '0SG10A0', '0SG10A1', '0SG10AJ', '0SG10J0', '0SG10J1', '0SG10JJ', '0SG10K0', '0SG10K1', '0SG10KJ', '0SG10Z0', '0SG10Z1', '0SG10ZJ', '0SG3070', '0SG3071', '0SG307J', '0SG30A0', '0SG30A1', '0SG30AJ', '0SG30J0', '0SG30J1', '0SG30JJ', '0SG30K0', '0SG30K1', '0SG30KJ', '0SG30Z0', '0SG30Z1', '0SG30ZJ', '81', '81.01', '81.02', '81.03', '81.04', '81.05', '81.06', '81.07', '81.08', '81.09', '81.3', '81.35', '81.36', '81.37', '81.38', '81.39', '81.61', '81.62', '81.63', '81.64', '84.51', '0WU80JZ', '0WU84JZ', '34.74', '22532', '22533', '22548', '22551', '22554', '22556', '22558', '22586', '22590', '22595', '22600', '22610', '22612', '22614', '22630', '22633', '22634', '22800', '22802', '22804', '22808', '22810', '22812', '22818', '22819', '22830', '22840', '22841', '22842', '22843', '22844', '22845', '22846', '22847', '22848', '22849', '22850', '22852', '22853', '22854', '22855', '22856', '22857', '22858', '22859', '22861', '22862', '22864', '22865', '22867', '22868', '22869', '22870', '21740', '21742', '21743')
GROUP BY z.PERSON_ID, z.ENC_ID, z.PROCDATE, DATEDIFF(YY, a.BIRTH_DATE, z.ADATE) - CASE
WHEN DATEADD(YY, DATEDIFF(YY, a.BIRTH_DATE, z.ADATE), a.BIRTH_DATE) > z.ADATE
THEN 1
ELSE 0
END, z.ADATE, a.GENDER, a.PRIMARY_LANGUAGE, a.RACE1, b.HT, b.WT, b.MEASURE_DATE, c.GEOCODE, c.LOC_START, c.LOC_END, d.DDATE"
)
tab1b$stateCnty <-ifelse(tab1b$GEOCODE == "", NA, substr(tab1b$GEOCODE, 1, 5))
## Change code below when package is created
states <- chordsTables::stateCnty #%>%
#select(state, stateFP, countyFP, stateCnty)
tab1b2 <- tab1b %>%
left_join(., states, by = "stateCnty") %>%
arrange(PERSON_ID, PROCDATE) %>%
group_by(PERSON_ID) %>%
mutate(
end_geodt = if_else(is.na(LOC_END), as.Date("2018-05-09"), as.Date(LOC_END)),
geo_adate = if_else(ADATE <= end_geodt, 1, 0),
date_diff = as.Date(PROCDATE) - as.Date(MEASURE_DATE),
miss_htwt = if_else(!is.na(HT) & !is.na(WT), 1, 0),
min_diff = min(abs(date_diff), na.rm = T),
use_date = if_else(as.Date(PROCDATE) + min_diff == MEASURE_DATE, 1, 0),
dup_id = if_else(duplicated(ENC_ID), 1, 0),
avg_ht = mean(HT, na.rm = T),
avg_wt = mean(WT, na.rm = T),
miss_avg = if_else(!is.na(avg_ht) & !is.na(avg_wt), 1, 0),
min_yr = min(ADATE),
use_yr = if_else(min_yr == ADATE, 1, 0),
ProcedureType = if_else(spine == 1, "Spine", "Pectus")
) %>%
ungroup(.) %>%
dplyr::filter(use_yr == 1) %>%
dplyr::filter(!duplicated(PERSON_ID) & !duplicated(ENC_ID))
tab1b_sum <-
list("Sample size" =
list(
"N = " =~ dplyr::n_distinct(PERSON_ID)),
"Age" =
list("Mean (SD)" = ~ qwraps2::mean_sd(AGE, na_rm = T),
"Median (IQR)" = ~ qwraps2::median_iqr(AGE, na_rm = T)),
"Gender, Freqency(%)" =
list("Male" = ~ qwraps2::n_perc(GENDER == "M", na_rm = T)),
"Primary Language, Freqency(%)" =
list("English" = ~ qwraps2::n_perc(PRIMARY_LANGUAGE == "eng", na_rm = T),
"Spanish" = ~ qwraps2::n_perc(PRIMARY_LANGUAGE == "spa", na_rm = T)),
"Race, Freqency(%)" =
list("Native Hawaiian or Other Pacific Islander" = ~ qwraps2::n_perc(RACE1 == "HP", na_rm = T),
"American Indian/Alaska Native" = ~ qwraps2::n_perc(RACE1 == "IN", na_rm = T),
"Asian" = ~ qwraps2::n_perc(RACE1 == "AS", na_rm = T),
"Black or African American" = ~ qwraps2::n_perc(RACE1 == "BA", na_rm = T),
"White" = ~ qwraps2::n_perc(RACE1 == "WH", na_rm = T),
"More than one race" = ~ qwraps2::n_perc(RACE1 == "MU", na_rm = T),
"Unknown" = ~ qwraps2::n_perc(RACE1 == "UN", na_rm = T)),
"Height" =
list("Mean (SD)" = ~ qwraps2::mean_sd(avg_ht, na_rm = T)),
"Weight" =
list("Mean (SD)" = ~ qwraps2::mean_sd(avg_wt, na_rm = T)),
"State of patient address" =
list("Colorado" = ~ qwraps2::n_perc(state == "CO", na_rm = T),
"Wyoming" = ~ qwraps2::n_perc(state == "WY", na_rm = T),
"Nebraska" = ~ qwraps2::n_perc(state == "NE", na_rm = T),
"New Mexico" = ~ qwraps2::n_perc(state == "NM", na_rm = T),
"Oklahoma" = ~ qwraps2::n_perc(state == "OK", na_rm = T),
"South Dakota" = ~ qwraps2::n_perc(state == "SD", na_rm = T),
"Kansas" = ~ qwraps2::n_perc(state == "KS", na_rm = T),
"Other/Missing"= ~ qwraps2::n_perc((!(state %in% c("CO", "WY", "NE", "NM", "OK", "SD", "KS") & !is.na(state))) ,
na_rm = T))
)
if(length(dimnames(table(tab1b2$ProcedureType))) == 1){
tab1b_tot_sum <- qwraps2::summary_table(tab1b2, tab1b_sum)
tab1b_out <- tab1b_tot_sum
print(tab1b_out, cnames = dimnames(table(tab1b2$ProcedureType))[[1]][1])
} else{
tab1b_tot_sum <- qwraps2::summary_table(tab1b2, tab1b_sum)
tab1b_proc_sum <- qwraps2::summary_table(dplyr::group_by(tab1b2, "ProcedureType"), tab1b_sum)
tab1b_out <- cbind(tab1b_tot_sum, tab1b_proc_sum)
print(tab1b_out, cnames = c("Total", dimnames(table(tab1b2$ProcedureType))[[1]]))
}
Table 1C: Patient Location by Year
tab1c <- tab1b2 %>%
dplyr::filter(state == "CO") %>%
mutate(
YEAR = lubridate::year(PROCDATE)
) %>%
group_by(YEAR)
qwraps2::summary_table(tab1c, list("County of Residence" = with(tab1c, qwraps2::tab_summary(countyName, n_perc_args = list(digits = 2, show_symbol = TRUE)))))
Table 1D: Length of Stay by Location & Type Summary (All Years)
# Some length discharge values are 1900-01-01 so a new variable needs to be created to turn values < 0 to missing for
# length of stay
tab1d <- tab1b2 %>%
mutate(
los = as.Date(DDATE) - as.Date(ADATE),
los2 = if_else(los < 0, NA_integer_, as.integer(los))
)
tab1d_sum <- list(
"Length of Stay (Days)" =
list(
"Mean (SD)" =~ qwraps2::mean_sd(los2, na_rm = T),
"Median (IQR)" =~ qwraps2::median_iqr(los2, na_rm = T),
"Range" =~ paste(min(los2, na.rm = T), " - ", max(los2, na.rm = T))
)
)
if(length(dimnames(table(tab1d$ProcedureType))) == 1){
tab1d_tot_sum <- qwraps2::summary_table(tab1d, tab1d_sum)
tab1d_out <- tab1d_tot_sum
print(tab1d_out, cnames = dimnames(table(tab1d$ProcedureType))[[1]][1])
} else{
tab1d_tot_sum <- qwraps2::summary_table(tab1d, tab1d_sum)
tab1d_proc_sum <- qwraps2::summary_table(dplyr::group_by(tab1d, ProcedureType), tab1d_sum)
tab1d_out <- cbind(tab1d_tot_sum, tab1d_proc_sum)
print(tab1b_out, cnames = c("Total", dimnames(table(tab1b2$ProcedureType))[[1]]))
}
Table Pack 1E: Length of Stay by Location and Type
tab1e <- tab1d %>%
mutate(
YEAR = lubridate::year(PROCDATE)
)
if(length(dimnames(table(tab1e$ProcedureType))) == 1){
tab1e_tot_sum <- qwraps2::summary_table(dplyr::group_by(tab1e, YEAR), tab1d_sum)
tab1e_out <- tab1e_tot_sum
print(tab1e_out)
} else{
tab1e_tot_sum <- qwraps2::summary_table(tab1e, tab1e_sum)
tab1e_proc_sum <- qwraps2::summary_table(dplyr::group_by(tab1e, ProcedureType, YEAR), tab1d_sum)
tab1e_out <- cbind(tab1e_tot_sum, tab1e_proc_sum)
print(tab1b_out, cnames = c("Total", dimnames(table(tab1b2$ProcedureType))[[1]]))
}
Figure 1A: Histogram of Patient Age at surgery (CPT Code/Proc Code Date)
fig1a <- ggplot(data = tab1b2, aes(x = AGE)) +
geom_histogram(color="black", fill="red", binwidth = 1) +
ylab("Count") + xlab("Age")
fig1a
Data Tables Protocol: Table Set 2: Patient Demographic Tables
Table Pack 2A: Diagnosis Code Frequencies by Location
tab2a <- odbc::dbGetQuery(con,
"SELECT DISTINCT z.ENC_ID,
z.PERSON_ID,
d.DIAGNOSES_ID,
d.DX,
d.PRIMARY_DX,
d.PRINCIPAL_DX
FROM
DEMOGRAPHICS a
INNER JOIN [PROCEDURES] z
ON z.PERSON_ID = a.PERSON_ID
LEFT JOIN [DIAGNOSES] d
ON z.PERSON_ID = d.PERSON_ID AND z.ENC_ID = d.ENC_ID
WHERE z.ADATE >= '01/01/2011'
AND DATEDIFF(YY, a.BIRTH_DATE, z.ADATE) - CASE
WHEN DATEADD(YY, DATEDIFF(YY, a.BIRTH_DATE, z.ADATE), a.BIRTH_DATE) > z.ADATE
THEN 1
ELSE 0
END >= 10
AND DATEDIFF(YY, a.BIRTH_DATE, z.ADATE) - CASE
WHEN DATEADD(YY, DATEDIFF(YY, a.BIRTH_DATE, z.ADATE), a.BIRTH_DATE) > z.ADATE
THEN 1
ELSE 0
END <= 26
AND
z.PX IN('0RG4070', '0RG4071', '0RG407J', '0RG40A0', '0RG40A1', '0RG40AJ', '0RG40J0', '0RG40J1', '0RG40JJ', '0RG40K0', '0RG40K1', '0RG40KJ', '0RG40Z0', '0RG40Z1', '0RG40ZJ', '0RG6070', '0RG6071', '0RG607J', '0RG60A0', '0RG60A1', '0RG60AJ', '0RG60J0', '0RG60J1', '0RG60JJ', '0RG60K0', '0RG60K1', '0RG60KJ', '0RG60Z0', '0RG60Z1', '0RG60ZJ', '0RG7070', '0RG7071', '0RG707J', '0RG70A0', '0RG70A1', '0RG70AJ', '0RG70J0', '0RG70J1', '0RG70JJ', '0RG70K0', '0RG70K1', '0RG70KJ', '0RG70Z0', '0RG70Z1', '0RG70ZJ', '0RG8070', '0RG8071', '0RG807J', '0RG80A0', '0RG80A1', '0RG80AJ', '0RG80J0', '0RG80J1', '0RG80JJ', '0RG80K0', '0RG80K1', '0RG80KJ', '0RG80Z0', '0RG80Z1', '0RG80ZJ', '0RGA070', '0RGA071', '0RGA07J', '0RGA0A0', '0RGA0A1', '0RGA0AJ', '0RGA0J0', '0RGA0J1', '0RGA0JJ', '0RGA0K0', '0RGA0K1', '0RGA0KJ', '0RGA0Z0', '0RGA0Z1', '0RGA0ZJ', '0SG0070', '0SG0071', '0SG007J', '0SG00A0', '0SG00A1', '0SG00AJ', '0SG00J0', '0SG00J1', '0SG00JJ', '0SG00K0', '0SG00K1', '0SG00KJ', '0SG00Z0', '0SG00Z1', '0SG00ZJ', '0SG1070', '0SG1071', '0SG107J', '0SG10A0', '0SG10A1', '0SG10AJ', '0SG10J0', '0SG10J1', '0SG10JJ', '0SG10K0', '0SG10K1', '0SG10KJ', '0SG10Z0', '0SG10Z1', '0SG10ZJ', '0SG3070', '0SG3071', '0SG307J', '0SG30A0', '0SG30A1', '0SG30AJ', '0SG30J0', '0SG30J1', '0SG30JJ', '0SG30K0', '0SG30K1', '0SG30KJ', '0SG30Z0', '0SG30Z1', '0SG30ZJ', '81', '81.01', '81.02', '81.03', '81.04', '81.05', '81.06', '81.07', '81.08', '81.09', '81.3', '81.35', '81.36', '81.37', '81.38', '81.39', '81.61', '81.62', '81.63', '81.64', '84.51', '0WU80JZ', '0WU84JZ', '34.74', '22532', '22533', '22548', '22551', '22554', '22556', '22558', '22586', '22590', '22595', '22600', '22610', '22612', '22614', '22630', '22633', '22634', '22800', '22802', '22804', '22808', '22810', '22812', '22818', '22819', '22830', '22840', '22841', '22842', '22843', '22844', '22845', '22846', '22847', '22848', '22849', '22850', '22852', '22853', '22854', '22855', '22856', '22857', '22858', '22859', '22861', '22862', '22864', '22865', '22867', '22868', '22869', '22870', '21740', '21742', '21743')"
)
length(unique(tab2a$PERSON_ID))
los_tab <- tab1d %>%
select(PERSON_ID, ProcedureType, los2)
#The code in the mutate section below counts the different responses from PRIMARY_DX
#In order to determine if any secondary diagnoses can be used enlui of primary dxs
#There are no secondary diagnoses without a primary diagnosis in the DH Date
tab2a2 <- tab2a %>%
dplyr::filter(PRIMARY_DX == "P") %>%
left_join(., los_tab, by = "PERSON_ID")
tab2_test <- as.data.frame(table(tab2a2$ProcedureType, tab2a2$DX))
list_tab2 <- split(tab2_test[, c(1,2)], seq(nrow(tab2_test[, c(1,2)])))
dx_tab <- data.frame(avg_los = unlist(lapply(list_tab2,
function(x) mean(tab2a2[tab2a2$ProcedureType == x[[1]] & tab2a2$DX == x[[2]],
"los2"], na.rm = T))))
dx_tab2 <- data.frame(med_los = unlist(lapply(list_tab2,
function(x) median(tab2a2[tab2a2$ProcedureType == x[[1]] & tab2a2$DX == x[[2]],
"los2"], na.rm = T))))
tab2a3 <- bind_cols(tab2_test, dx_tab)
tab2a4 <- bind_cols(tab2a3, dx_tab2) %>%
dplyr::rename(ProcedureType = Var1, DX = Var2)
Of the r n_distinct(tab2a$PERSON_ID) patients from the initial cohort, r n_distinct(tab2a2$PERSON_ID) had primary diagnoses that could be used for the following table.
knitr::kable(tab2a4, col.names = c("Procedure Type", "Primary DX", "Count of DX", "Mean Length of Stay", "Median Length of Stay"))
Table Pack 2B: Procedure Code Frequencies & LOS by Location
tab2b <- odbc::dbGetQuery(con,
"SELECT
DISTINCT z.ENC_ID,
z.PERSON_ID,
z.PX,
MAX(CASE
WHEN z.PX IN('0RG4070', '0RG4071', '0RG407J', '0RG40A0', '0RG40A1', '0RG40AJ', '0RG40J0', '0RG40J1', '0RG40JJ', '0RG40K0', '0RG40K1', '0RG40KJ', '0RG40Z0', '0RG40Z1', '0RG40ZJ', '0RG6070', '0RG6071', '0RG607J', '0RG60A0', '0RG60A1', '0RG60AJ', '0RG60J0', '0RG60J1', '0RG60JJ', '0RG60K0', '0RG60K1', '0RG60KJ', '0RG60Z0', '0RG60Z1', '0RG60ZJ', '0RG7070', '0RG7071', '0RG707J', '0RG70A0', '0RG70A1', '0RG70AJ', '0RG70J0', '0RG70J1', '0RG70JJ', '0RG70K0', '0RG70K1', '0RG70KJ', '0RG70Z0', '0RG70Z1', '0RG70ZJ', '0RG8070', '0RG8071', '0RG807J', '0RG80A0', '0RG80A1', '0RG80AJ', '0RG80J0', '0RG80J1', '0RG80JJ', '0RG80K0', '0RG80K1', '0RG80KJ', '0RG80Z0', '0RG80Z1', '0RG80ZJ', '0RGA070', '0RGA071', '0RGA07J', '0RGA0A0', '0RGA0A1', '0RGA0AJ', '0RGA0J0', '0RGA0J1', '0RGA0JJ', '0RGA0K0', '0RGA0K1', '0RGA0KJ', '0RGA0Z0', '0RGA0Z1', '0RGA0ZJ', '0SG0070', '0SG0071', '0SG007J', '0SG00A0', '0SG00A1', '0SG00AJ', '0SG00J0', '0SG00J1', '0SG00JJ', '0SG00K0', '0SG00K1', '0SG00KJ', '0SG00Z0', '0SG00Z1', '0SG00ZJ', '0SG1070', '0SG1071', '0SG107J', '0SG10A0', '0SG10A1', '0SG10AJ', '0SG10J0', '0SG10J1', '0SG10JJ', '0SG10K0', '0SG10K1', '0SG10KJ', '0SG10Z0', '0SG10Z1', '0SG10ZJ', '0SG3070', '0SG3071', '0SG307J', '0SG30A0', '0SG30A1', '0SG30AJ', '0SG30J0', '0SG30J1', '0SG30JJ', '0SG30K0', '0SG30K1', '0SG30KJ', '0SG30Z0', '0SG30Z1', '0SG30ZJ', '81', '81.01', '81.02', '81.03', '81.04', '81.05', '81.06', '81.07', '81.08', '81.09', '81.3', '81.35', '81.36', '81.37', '81.38', '81.39', '81.61', '81.62', '81.63', '81.64', '84.51', '22532', '22533', '22548', '22551', '22554', '22556', '22558', '22586', '22590', '22595', '22600', '22610', '22612', '22614', '22630', '22633', '22634', '22800', '22802', '22804', '22808', '22810', '22812', '22818', '22819', '22830', '22840', '22841', '22842', '22843', '22844', '22845', '22846', '22847', '22848', '22849', '22850', '22852', '22853', '22854', '22855', '22856', '22857', '22858', '22859', '22861', '22862', '22864', '22865', '22867', '22868', '22869', '22870')
THEN 1 ELSE 0 END) AS spine,
MAX(CASE
WHEN z.PX IN('0WU80JZ', '0WU84JZ', '34.74', '21740', '21742', '21743')
THEN 1 ELSE 0 END) AS pectus,
z.PROCDATE,
YEAR(z.ADATE) as px_date
FROM
DEMOGRAPHICS a
INNER JOIN [PROCEDURES] z
ON z.PERSON_ID = a.PERSON_ID
WHERE z.ADATE >= '01/01/2011'
AND DATEDIFF(YY, a.BIRTH_DATE, z.ADATE) - CASE
WHEN DATEADD(YY, DATEDIFF(YY, a.BIRTH_DATE, z.ADATE), a.BIRTH_DATE) > z.ADATE
THEN 1
ELSE 0
END >= 10
AND DATEDIFF(YY, a.BIRTH_DATE, z.ADATE) - CASE
WHEN DATEADD(YY, DATEDIFF(YY, a.BIRTH_DATE, z.ADATE), a.BIRTH_DATE) > z.ADATE
THEN 1
ELSE 0
END <= 26
AND
z.PX IN('0RG4070', '0RG4071', '0RG407J', '0RG40A0', '0RG40A1', '0RG40AJ', '0RG40J0', '0RG40J1', '0RG40JJ', '0RG40K0', '0RG40K1', '0RG40KJ', '0RG40Z0', '0RG40Z1', '0RG40ZJ', '0RG6070', '0RG6071', '0RG607J', '0RG60A0', '0RG60A1', '0RG60AJ', '0RG60J0', '0RG60J1', '0RG60JJ', '0RG60K0', '0RG60K1', '0RG60KJ', '0RG60Z0', '0RG60Z1', '0RG60ZJ', '0RG7070', '0RG7071', '0RG707J', '0RG70A0', '0RG70A1', '0RG70AJ', '0RG70J0', '0RG70J1', '0RG70JJ', '0RG70K0', '0RG70K1', '0RG70KJ', '0RG70Z0', '0RG70Z1', '0RG70ZJ', '0RG8070', '0RG8071', '0RG807J', '0RG80A0', '0RG80A1', '0RG80AJ', '0RG80J0', '0RG80J1', '0RG80JJ', '0RG80K0', '0RG80K1', '0RG80KJ', '0RG80Z0', '0RG80Z1', '0RG80ZJ', '0RGA070', '0RGA071', '0RGA07J', '0RGA0A0', '0RGA0A1', '0RGA0AJ', '0RGA0J0', '0RGA0J1', '0RGA0JJ', '0RGA0K0', '0RGA0K1', '0RGA0KJ', '0RGA0Z0', '0RGA0Z1', '0RGA0ZJ', '0SG0070', '0SG0071', '0SG007J', '0SG00A0', '0SG00A1', '0SG00AJ', '0SG00J0', '0SG00J1', '0SG00JJ', '0SG00K0', '0SG00K1', '0SG00KJ', '0SG00Z0', '0SG00Z1', '0SG00ZJ', '0SG1070', '0SG1071', '0SG107J', '0SG10A0', '0SG10A1', '0SG10AJ', '0SG10J0', '0SG10J1', '0SG10JJ', '0SG10K0', '0SG10K1', '0SG10KJ', '0SG10Z0', '0SG10Z1', '0SG10ZJ', '0SG3070', '0SG3071', '0SG307J', '0SG30A0', '0SG30A1', '0SG30AJ', '0SG30J0', '0SG30J1', '0SG30JJ', '0SG30K0', '0SG30K1', '0SG30KJ', '0SG30Z0', '0SG30Z1', '0SG30ZJ', '81', '81.01', '81.02', '81.03', '81.04', '81.05', '81.06', '81.07', '81.08', '81.09', '81.3', '81.35', '81.36', '81.37', '81.38', '81.39', '81.61', '81.62', '81.63', '81.64', '84.51', '0WU80JZ', '0WU84JZ', '34.74', '22532', '22533', '22548', '22551', '22554', '22556', '22558', '22586', '22590', '22595', '22600', '22610', '22612', '22614', '22630', '22633', '22634', '22800', '22802', '22804', '22808', '22810', '22812', '22818', '22819', '22830', '22840', '22841', '22842', '22843', '22844', '22845', '22846', '22847', '22848', '22849', '22850', '22852', '22853', '22854', '22855', '22856', '22857', '22858', '22859', '22861', '22862', '22864', '22865', '22867', '22868', '22869', '22870', '21740', '21742', '21743')
GROUP BY z.PERSON_ID, z.ENC_ID, z.PX, z.PROCDATE, YEAR(z.ADATE)"
)
tab2b2 <- tab2b %>%
group_by(PERSON_ID) %>%
mutate(
min_dt = min(as.Date(PROCDATE)),
use_dt = if_else(as.Date(min_dt) == as.Date(PROCDATE), 1, 0)
) %>%
ungroup(.) %>%
dplyr::filter(use_dt == 1) %>%
left_join(., los_tab, by = "PERSON_ID") %>%
group_by(PERSON_ID) %>%
mutate(
px_num = paste0("px_", 1:length(PERSON_ID))
) %>%
ungroup(.)
tab2b_wide <- tidyr::spread(tab2b2, px_num, PX) %>%
mutate(
all_pxs = if_else(is.na(px_2), px_1,
if_else(is.na(px_3), paste(px_1, px_2, sep = ", "),
if_else(is.na(px_4), paste(px_1, px_2, px_3, sep = ", "),
if_else(is.na(px_5), paste(px_1, px_2, px_3, px_4, sep = ", "),
paste(px_1, px_2, px_3, px_4, px_5, sep = ", ")))))
)
table(tab2b_wide$all_pxs)
#Get frequencies of px combinations
tab2b_test <- as.data.frame(table(tab2b_wide$ProcedureType, tab2b_wide$all_pxs))
# Create a list of unique pxs and px types to cycle through to get means and medians
list_tab2b <- split(tab2b_test[, c(1,2)], seq(nrow(tab2b_test[, c(1,2)])))
names(list_tab2b)
#get means for all uniqe combinations of pxs and px types
px_tab <- data.frame(avg_los = unlist(lapply(list_tab2b,
function(x) mean(as.numeric(tab2b_wide$los2[tab2b_wide$ProcedureType == x[[1]] & tab2b_wide$all_pxs == x[[2]]]), na.rm = T))))
mean(as.numeric(tab2b_wide$los2[tab2b_wide$ProcedureType == list_tab2b[[58]][[1]] & tab2b_wide$all_pxs == list_tab2b[[58]][[2]]]), na.rm = T)
#get medians for all uniqe combinations of pxs and px types
px_tab2 <- data.frame(med_los = unlist(lapply(list_tab2b,
function(x) median(as.numeric(tab2b_wide$los2[tab2b_wide$ProcedureType == x[[1]] & tab2b_wide$all_pxs == x[[2]]]), na.rm = T))))
median(as.numeric(tab2b_wide[tab2b_wide$ProcedureType == list_tab2b[[1]][[1]] & tab2b_wide$all_pxs == list_tab2b[[1]][[2]],
"los2"]), na.rm = T)
tab2b3 <- bind_cols(tab2b_test, px_tab)
tab2b4 <- bind_cols(tab2b3, px_tab2) %>%
dplyr::rename(ProcedureType = Var1, PX = Var2)
Of the r n_distinct(tab2b$PERSON_ID) patients from the initial cohort, r n_distinct(tab2b2$PERSON_ID) had one or more valid procedure/CPT codes that could be used for the following table.
knitr::kable(tab2b4, col.names = c("Procedure Type", "Unique Combination of PXs", "Count", "Average LOS", "Median LOS"), digits = 2)
Table 2C: Social History Frequencies by Location
tab2c <- odbc::dbGetQuery(con,
"SELECT
DISTINCT z.ENC_ID,
z.PERSON_ID,
d.TOBACCO_USER,
d.ALCOHOL_USER,
d.ILL_DRUG_USER,
d.CONDOM_YN,
d.PILL_YN,
d.DIAPHRAGM_YN,
d.IUD_YN,
d.SURGICAL_YN,
d.SPERMICIDE_YN,
d.IMPLANT_YN,
d.INJECTION_YN,
d.SPONGE_YN,
d.BC_INSERTS_YN,
d.CONTACT_DATE
FROM
DEMOGRAPHICS a
INNER JOIN [PROCEDURES] z
ON z.PERSON_ID = a.PERSON_ID
LEFT JOIN [SOCIAL_HISTORY] d
ON z.PERSON_ID = d.PERSON_ID AND z.ENC_ID = d.ENC_ID
WHERE z.ADATE >= '01/01/2011'
AND DATEDIFF(YY, a.BIRTH_DATE, z.ADATE) - CASE
WHEN DATEADD(YY, DATEDIFF(YY, a.BIRTH_DATE, z.ADATE), a.BIRTH_DATE) > z.ADATE
THEN 1
ELSE 0
END >= 10
AND DATEDIFF(YY, a.BIRTH_DATE, z.ADATE) - CASE
WHEN DATEADD(YY, DATEDIFF(YY, a.BIRTH_DATE, z.ADATE), a.BIRTH_DATE) > z.ADATE
THEN 1
ELSE 0
END <= 26
AND
z.PX IN('0RG4070', '0RG4071', '0RG407J', '0RG40A0', '0RG40A1', '0RG40AJ', '0RG40J0', '0RG40J1', '0RG40JJ', '0RG40K0', '0RG40K1', '0RG40KJ', '0RG40Z0', '0RG40Z1', '0RG40ZJ', '0RG6070', '0RG6071', '0RG607J', '0RG60A0', '0RG60A1', '0RG60AJ', '0RG60J0', '0RG60J1', '0RG60JJ', '0RG60K0', '0RG60K1', '0RG60KJ', '0RG60Z0', '0RG60Z1', '0RG60ZJ', '0RG7070', '0RG7071', '0RG707J', '0RG70A0', '0RG70A1', '0RG70AJ', '0RG70J0', '0RG70J1', '0RG70JJ', '0RG70K0', '0RG70K1', '0RG70KJ', '0RG70Z0', '0RG70Z1', '0RG70ZJ', '0RG8070', '0RG8071', '0RG807J', '0RG80A0', '0RG80A1', '0RG80AJ', '0RG80J0', '0RG80J1', '0RG80JJ', '0RG80K0', '0RG80K1', '0RG80KJ', '0RG80Z0', '0RG80Z1', '0RG80ZJ', '0RGA070', '0RGA071', '0RGA07J', '0RGA0A0', '0RGA0A1', '0RGA0AJ', '0RGA0J0', '0RGA0J1', '0RGA0JJ', '0RGA0K0', '0RGA0K1', '0RGA0KJ', '0RGA0Z0', '0RGA0Z1', '0RGA0ZJ', '0SG0070', '0SG0071', '0SG007J', '0SG00A0', '0SG00A1', '0SG00AJ', '0SG00J0', '0SG00J1', '0SG00JJ', '0SG00K0', '0SG00K1', '0SG00KJ', '0SG00Z0', '0SG00Z1', '0SG00ZJ', '0SG1070', '0SG1071', '0SG107J', '0SG10A0', '0SG10A1', '0SG10AJ', '0SG10J0', '0SG10J1', '0SG10JJ', '0SG10K0', '0SG10K1', '0SG10KJ', '0SG10Z0', '0SG10Z1', '0SG10ZJ', '0SG3070', '0SG3071', '0SG307J', '0SG30A0', '0SG30A1', '0SG30AJ', '0SG30J0', '0SG30J1', '0SG30JJ', '0SG30K0', '0SG30K1', '0SG30KJ', '0SG30Z0', '0SG30Z1', '0SG30ZJ', '81', '81.01', '81.02', '81.03', '81.04', '81.05', '81.06', '81.07', '81.08', '81.09', '81.3', '81.35', '81.36', '81.37', '81.38', '81.39', '81.61', '81.62', '81.63', '81.64', '84.51', '0WU80JZ', '0WU84JZ', '34.74', '22532', '22533', '22548', '22551', '22554', '22556', '22558', '22586', '22590', '22595', '22600', '22610', '22612', '22614', '22630', '22633', '22634', '22800', '22802', '22804', '22808', '22810', '22812', '22818', '22819', '22830', '22840', '22841', '22842', '22843', '22844', '22845', '22846', '22847', '22848', '22849', '22850', '22852', '22853', '22854', '22855', '22856', '22857', '22858', '22859', '22861', '22862', '22864', '22865', '22867', '22868', '22869', '22870', '21740', '21742', '21743')"
)
# Determine if date of social history assessment was during procedure encounter
tab2c$visit <- ave(tab2c$PERSON_ID == tab2c$PERSON_ID, tab2c$PERSON_ID, FUN = cumsum)
# Get count of how many didn't get asked smoking
tab2c2 <- tab2c %>%
left_join(., tab1d, by = c("PERSON_ID", "ENC_ID")) %>%
group_by(PERSON_ID) %>%
mutate(
sh_dt = if_else(CONTACT_DATE >= ADATE & CONTACT_DATE <= DDATE, 1, 0),
max_vis = max(visit, na.rm = T),
use_vis = if_else(sh_dt == 1, 1,
if_else(max_vis == 1, 1, 0))
) %>%
dplyr::filter(use_vis == 1) %>%
mutate(
ever_smoke = if_else(TOBACCO_USER == "Y", 1, 0),
max_smoke = max(ever_smoke),
use_smoke = if_else(ever_smoke == max_smoke, 1, 0),
smoke_na = if_else(max_smoke != 1 & TOBACCO_USER == "X", 1, 0),
max_smoke_na = max(smoke_na),
bc_use = if_else(CONDOM_YN == "Y" |
PILL_YN == "Y" |
DIAPHRAGM_YN == "Y" |
IUD_YN == "Y" |
SURGICAL_YN == "Y" |
SPERMICIDE_YN == "Y" |
IMPLANT_YN == "Y" |
INJECTION_YN == "Y" |
SPONGE_YN == "Y" |
BC_INSERTS_YN == "Y", 1, 0),
max_bc = max(bc_use, na.rm = T),
alc_use = if_else(ALCOHOL_USER == "Y", 1, 0),
max_alc = max(alc_use, na.rm = T),
drug_use = if_else(ILL_DRUG_USER == "Y", 1, 0)
) %>%
dplyr::filter(use_smoke == 1 & TOBACCO_USER != "X") %>%
dplyr::filter(!duplicated(PERSON_ID)) %>%
ungroup(.)
mean_los_sh <- c(mean(tab2c2$los2[tab2c2$max_smoke == 1], na.rm = T),
mean(tab2c2$los2[tab2c2$max_smoke_na == 1], na.rm = T),
mean(tab2c2$los2[tab2c2$max_alc == 1], na.rm = T),
mean(tab2c2$los2[tab2c2$drug_use == 1], na.rm = T),
mean(tab2c2$los2[tab2c2$max_bc == 1], na.rm = T))
median_los_sh <- c(median(tab2c2$los2[tab2c2$max_smoke == 1], na.rm = T),
median(tab2c2$los2[tab2c2$max_smoke_na == 1], na.rm = T),
median(tab2c2$los2[tab2c2$max_alc == 1], na.rm = T),
median(tab2c2$los2[tab2c2$drug_use == 1], na.rm = T),
median(tab2c2$los2[tab2c2$max_bc == 1], na.rm = T))
count_sh <- c(tally(tab2c2[tab2c2$max_smoke == 1, "los2"])[[1]],
tally(tab2c2[tab2c2$max_smoke_na == 1, "los2"])[[1]],
tally(tab2c2[tab2c2$max_alc == 1, "los2"])[[1]],
tally(tab2c2[tab2c2$drug_use == 1, "los2"])[[1]],
tally(tab2c2[tab2c2$max_bc == 1, "los2"])[[1]])
names_sh <- c("Tobacco Use", "Not asked about tobacco use", "Alcohol Use", "Illegal Drug Use", "Birth Control Use")
tab2c_sum <- data.frame(names = names_sh, counts = count_sh, means = mean_los_sh, medians = median_los_sh)
Of the r n_distinct(tab2c$PERSON_ID) patients from the initial cohort, r n_distinct(tab2c2$PERSON_ID) had valid data from the SOCIAL_HISTORY table that was used to create the table below.
knitr::kable(tab2c_sum, col.names = c(paste0("Variables N = ", nrow(tab2c2)), "Count", "Mean LOS", "Median LOS"))
Data Tables Protocol: Table Set 3: Provider Information
Table 3A: Type of Surgeon/Physician of Record for Index
The Total values for both Spine and Pectus are for any age of patient.
# tab3a <- odbc::dbGetQuery(con,
# "SELECT
# DISTINCT z.ENC_ID,
# z.PERSON_ID,
# z.PX,
# z.PROVIDER,
# z.PERFORMINGPROVIDER,
# d.SPECIALTY,
# d.SPECIALTY2,
# d.SPECIALTY3,
# d.SPECIALTY4,
# d.PROVIDER_TYPE,
# MAX(CASE
# WHEN z.PX IN('0RG4070', '0RG4071', '0RG407J', '0RG40A0', '0RG40A1', '0RG40AJ', '0RG40J0', '0RG40J1', '0RG40JJ', '0RG40K0', '0RG40K1', '0RG40KJ', '0RG40Z0', '0RG40Z1', '0RG40ZJ', '0RG6070', '0RG6071', '0RG607J', '0RG60A0', '0RG60A1', '0RG60AJ', '0RG60J0', '0RG60J1', '0RG60JJ', '0RG60K0', '0RG60K1', '0RG60KJ', '0RG60Z0', '0RG60Z1', '0RG60ZJ', '0RG7070', '0RG7071', '0RG707J', '0RG70A0', '0RG70A1', '0RG70AJ', '0RG70J0', '0RG70J1', '0RG70JJ', '0RG70K0', '0RG70K1', '0RG70KJ', '0RG70Z0', '0RG70Z1', '0RG70ZJ', '0RG8070', '0RG8071', '0RG807J', '0RG80A0', '0RG80A1', '0RG80AJ', '0RG80J0', '0RG80J1', '0RG80JJ', '0RG80K0', '0RG80K1', '0RG80KJ', '0RG80Z0', '0RG80Z1', '0RG80ZJ', '0RGA070', '0RGA071', '0RGA07J', '0RGA0A0', '0RGA0A1', '0RGA0AJ', '0RGA0J0', '0RGA0J1', '0RGA0JJ', '0RGA0K0', '0RGA0K1', '0RGA0KJ', '0RGA0Z0', '0RGA0Z1', '0RGA0ZJ', '0SG0070', '0SG0071', '0SG007J', '0SG00A0', '0SG00A1', '0SG00AJ', '0SG00J0', '0SG00J1', '0SG00JJ', '0SG00K0', '0SG00K1', '0SG00KJ', '0SG00Z0', '0SG00Z1', '0SG00ZJ', '0SG1070', '0SG1071', '0SG107J', '0SG10A0', '0SG10A1', '0SG10AJ', '0SG10J0', '0SG10J1', '0SG10JJ', '0SG10K0', '0SG10K1', '0SG10KJ', '0SG10Z0', '0SG10Z1', '0SG10ZJ', '0SG3070', '0SG3071', '0SG307J', '0SG30A0', '0SG30A1', '0SG30AJ', '0SG30J0', '0SG30J1', '0SG30JJ', '0SG30K0', '0SG30K1', '0SG30KJ', '0SG30Z0', '0SG30Z1', '0SG30ZJ', '81', '81.01', '81.02', '81.03', '81.04', '81.05', '81.06', '81.07', '81.08', '81.09', '81.3', '81.35', '81.36', '81.37', '81.38', '81.39', '81.61', '81.62', '81.63', '81.64', '84.51', '22532', '22533', '22548', '22551', '22554', '22556', '22558', '22586', '22590', '22595', '22600', '22610', '22612', '22614', '22630', '22633', '22634', '22800', '22802', '22804', '22808', '22810', '22812', '22818', '22819', '22830', '22840', '22841', '22842', '22843', '22844', '22845', '22846', '22847', '22848', '22849', '22850', '22852', '22853', '22854', '22855', '22856', '22857', '22858', '22859', '22861', '22862', '22864', '22865', '22867', '22868', '22869', '22870')
# THEN 1 ELSE 0 END) AS spine,
# MAX(CASE
# WHEN z.PX IN('0WU80JZ', '0WU84JZ', '34.74', '21740', '21742', '21743')
# THEN 1 ELSE 0 END) AS pectus,
# z.PROCDATE,
# YEAR(z.ADATE) as px_date
# FROM
# DEMOGRAPHICS a
# INNER JOIN [PROCEDURES] z
# ON z.PERSON_ID = a.PERSON_ID
# LEFT JOIN [PROVIDER_SPECIALTY] d
# ON z.PERFORMINGPROVIDER = d.PROVIDER
# WHERE z.ADATE >= '01/01/2011'
# AND DATEDIFF(YY, a.BIRTH_DATE, z.ADATE) - CASE
# WHEN DATEADD(YY, DATEDIFF(YY, a.BIRTH_DATE, z.ADATE), a.BIRTH_DATE) > z.ADATE
# THEN 1
# ELSE 0
# END >= 10
# AND DATEDIFF(YY, a.BIRTH_DATE, z.ADATE) - CASE
# WHEN DATEADD(YY, DATEDIFF(YY, a.BIRTH_DATE, z.ADATE), a.BIRTH_DATE) > z.ADATE
# THEN 1
# ELSE 0
# END <= 26
# AND
# z.PX IN('0RG4070', '0RG4071', '0RG407J', '0RG40A0', '0RG40A1', '0RG40AJ', '0RG40J0', '0RG40J1', '0RG40JJ', '0RG40K0', '0RG40K1', '0RG40KJ', '0RG40Z0', '0RG40Z1', '0RG40ZJ', '0RG6070', '0RG6071', '0RG607J', '0RG60A0', '0RG60A1', '0RG60AJ', '0RG60J0', '0RG60J1', '0RG60JJ', '0RG60K0', '0RG60K1', '0RG60KJ', '0RG60Z0', '0RG60Z1', '0RG60ZJ', '0RG7070', '0RG7071', '0RG707J', '0RG70A0', '0RG70A1', '0RG70AJ', '0RG70J0', '0RG70J1', '0RG70JJ', '0RG70K0', '0RG70K1', '0RG70KJ', '0RG70Z0', '0RG70Z1', '0RG70ZJ', '0RG8070', '0RG8071', '0RG807J', '0RG80A0', '0RG80A1', '0RG80AJ', '0RG80J0', '0RG80J1', '0RG80JJ', '0RG80K0', '0RG80K1', '0RG80KJ', '0RG80Z0', '0RG80Z1', '0RG80ZJ', '0RGA070', '0RGA071', '0RGA07J', '0RGA0A0', '0RGA0A1', '0RGA0AJ', '0RGA0J0', '0RGA0J1', '0RGA0JJ', '0RGA0K0', '0RGA0K1', '0RGA0KJ', '0RGA0Z0', '0RGA0Z1', '0RGA0ZJ', '0SG0070', '0SG0071', '0SG007J', '0SG00A0', '0SG00A1', '0SG00AJ', '0SG00J0', '0SG00J1', '0SG00JJ', '0SG00K0', '0SG00K1', '0SG00KJ', '0SG00Z0', '0SG00Z1', '0SG00ZJ', '0SG1070', '0SG1071', '0SG107J', '0SG10A0', '0SG10A1', '0SG10AJ', '0SG10J0', '0SG10J1', '0SG10JJ', '0SG10K0', '0SG10K1', '0SG10KJ', '0SG10Z0', '0SG10Z1', '0SG10ZJ', '0SG3070', '0SG3071', '0SG307J', '0SG30A0', '0SG30A1', '0SG30AJ', '0SG30J0', '0SG30J1', '0SG30JJ', '0SG30K0', '0SG30K1', '0SG30KJ', '0SG30Z0', '0SG30Z1', '0SG30ZJ', '81', '81.01', '81.02', '81.03', '81.04', '81.05', '81.06', '81.07', '81.08', '81.09', '81.3', '81.35', '81.36', '81.37', '81.38', '81.39', '81.61', '81.62', '81.63', '81.64', '84.51', '0WU80JZ', '0WU84JZ', '34.74', '22532', '22533', '22548', '22551', '22554', '22556', '22558', '22586', '22590', '22595', '22600', '22610', '22612', '22614', '22630', '22633', '22634', '22800', '22802', '22804', '22808', '22810', '22812', '22818', '22819', '22830', '22840', '22841', '22842', '22843', '22844', '22845', '22846', '22847', '22848', '22849', '22850', '22852', '22853', '22854', '22855', '22856', '22857', '22858', '22859', '22861', '22862', '22864', '22865', '22867', '22868', '22869', '22870', '21740', '21742', '21743')
# GROUP BY z.PERSON_ID, z.ENC_ID, z.PX, z.PROVIDER,
# z.PERFORMINGPROVIDER,
# d.SPECIALTY,
# d.SPECIALTY2,
# d.SPECIALTY3,
# d.SPECIALTY4,
# d.PROVIDER_TYPE, z.PROCDATE, YEAR(z.ADATE)"
# )
#
#
# tab3a2 <- tab3a %>%
# left_join(., specialties, by = "SPECIALTY") %>%
# dplyr::filter(PROVIDER_TYPE %in% c(61, 25, 77, 999)) %>% # Filter to only include physicians, residents, fellows and unknowns
# dplyr::filter(ENC_ID %in% tab1b2$ENC_ID) %>% #Only include valid encounter ids
# dplyr::filter(!duplicated(ENC_ID)) # Deduplicate encounter ID
# Removed PX and procdate from below to get
tab3a_prov <- odbc::dbGetQuery(con,
"SELECT
z.ENC_ID,
z.PERSON_ID,
z.PROVIDER,
z.PERFORMINGPROVIDER,
d.SPECIALTY,
d.SPECIALTY2,
d.SPECIALTY3,
d.SPECIALTY4,
d.PROVIDER_TYPE,
d.PROVIDER_BIRTH_YEAR,
d.YEAR_GRADUATED,
d.PROVIDER_RACE,
d.PROVIDER_HISPANIC,
MAX(CASE
WHEN z.PX IN('0RG4070', '0RG4071', '0RG407J', '0RG40A0', '0RG40A1', '0RG40AJ', '0RG40J0', '0RG40J1', '0RG40JJ', '0RG40K0', '0RG40K1', '0RG40KJ', '0RG40Z0', '0RG40Z1', '0RG40ZJ', '0RG6070', '0RG6071', '0RG607J', '0RG60A0', '0RG60A1', '0RG60AJ', '0RG60J0', '0RG60J1', '0RG60JJ', '0RG60K0', '0RG60K1', '0RG60KJ', '0RG60Z0', '0RG60Z1', '0RG60ZJ', '0RG7070', '0RG7071', '0RG707J', '0RG70A0', '0RG70A1', '0RG70AJ', '0RG70J0', '0RG70J1', '0RG70JJ', '0RG70K0', '0RG70K1', '0RG70KJ', '0RG70Z0', '0RG70Z1', '0RG70ZJ', '0RG8070', '0RG8071', '0RG807J', '0RG80A0', '0RG80A1', '0RG80AJ', '0RG80J0', '0RG80J1', '0RG80JJ', '0RG80K0', '0RG80K1', '0RG80KJ', '0RG80Z0', '0RG80Z1', '0RG80ZJ', '0RGA070', '0RGA071', '0RGA07J', '0RGA0A0', '0RGA0A1', '0RGA0AJ', '0RGA0J0', '0RGA0J1', '0RGA0JJ', '0RGA0K0', '0RGA0K1', '0RGA0KJ', '0RGA0Z0', '0RGA0Z1', '0RGA0ZJ', '0SG0070', '0SG0071', '0SG007J', '0SG00A0', '0SG00A1', '0SG00AJ', '0SG00J0', '0SG00J1', '0SG00JJ', '0SG00K0', '0SG00K1', '0SG00KJ', '0SG00Z0', '0SG00Z1', '0SG00ZJ', '0SG1070', '0SG1071', '0SG107J', '0SG10A0', '0SG10A1', '0SG10AJ', '0SG10J0', '0SG10J1', '0SG10JJ', '0SG10K0', '0SG10K1', '0SG10KJ', '0SG10Z0', '0SG10Z1', '0SG10ZJ', '0SG3070', '0SG3071', '0SG307J', '0SG30A0', '0SG30A1', '0SG30AJ', '0SG30J0', '0SG30J1', '0SG30JJ', '0SG30K0', '0SG30K1', '0SG30KJ', '0SG30Z0', '0SG30Z1', '0SG30ZJ', '81', '81.01', '81.02', '81.03', '81.04', '81.05', '81.06', '81.07', '81.08', '81.09', '81.3', '81.35', '81.36', '81.37', '81.38', '81.39', '81.61', '81.62', '81.63', '81.64', '84.51', '22532', '22533', '22548', '22551', '22554', '22556', '22558', '22586', '22590', '22595', '22600', '22610', '22612', '22614', '22630', '22633', '22634', '22800', '22802', '22804', '22808', '22810', '22812', '22818', '22819', '22830', '22840', '22841', '22842', '22843', '22844', '22845', '22846', '22847', '22848', '22849', '22850', '22852', '22853', '22854', '22855', '22856', '22857', '22858', '22859', '22861', '22862', '22864', '22865', '22867', '22868', '22869', '22870')
THEN 1 ELSE 0 END) AS spine,
MAX(CASE
WHEN z.PX IN('0WU80JZ', '0WU84JZ', '34.74', '21740', '21742', '21743')
THEN 1 ELSE 0 END) AS pectus,
YEAR(z.ADATE) as px_date
FROM
PROCEDURES z
LEFT JOIN [PROVIDER_SPECIALTY] d
ON z.PERFORMINGPROVIDER = d.PROVIDER
WHERE z.ADATE >= '01/01/2011'
AND
z.PERFORMINGPROVIDER IN('E_3465', 'E_13066', 'E_13066', 'E_13066', '088377', 'E_13066', 'E_13066', 'E_14465', 'E_1526', 'E_1526', 'E_4104', 'E_5277', 'E_1322', 'E_13066', 'E_1322', 'E_13066', '116038', '158352', '145318', '145466', '123364', '130666', '162974', '169649', '125401', '086330', 'E_1322', '132274', 'E_13066', '113399', '073361', '116038', '123364', '133595', '144576', '145318', '140848', '125401', '144576', 'E_13066', '125401', '124586', 'E_13066', '073361', '128850', '124453', '158352', '100305', '141531', '162974', '132274', '046771', 'E_13066', 'E_4046', '133322', '125401', '141531', '070979', '135863', '123364', '124453', '158378', '131375', '133595', '133322', '086330', 'E_1526', '133322', '156604', 'E_13066', '088377', '162974', '141531', '079038', '077321', '157230', '100305', '100305', '141440', '133322', '073411', '133595', '128850', '156604', 'E_3844', '144576', '128850', 'E_1526', '123364', '007047', '158378', '124453', '141481', '135863', '156604', '141465', '124453', '124453', '128850', '146787', '119578', '147918', '124586', 'E_3661', '086330', '153767', '110676', '111153')
AND
z.PX IN('0RG4070', '0RG4071', '0RG407J', '0RG40A0', '0RG40A1', '0RG40AJ', '0RG40J0', '0RG40J1', '0RG40JJ', '0RG40K0', '0RG40K1', '0RG40KJ', '0RG40Z0', '0RG40Z1', '0RG40ZJ', '0RG6070', '0RG6071', '0RG607J', '0RG60A0', '0RG60A1', '0RG60AJ', '0RG60J0', '0RG60J1', '0RG60JJ', '0RG60K0', '0RG60K1', '0RG60KJ', '0RG60Z0', '0RG60Z1', '0RG60ZJ', '0RG7070', '0RG7071', '0RG707J', '0RG70A0', '0RG70A1', '0RG70AJ', '0RG70J0', '0RG70J1', '0RG70JJ', '0RG70K0', '0RG70K1', '0RG70KJ', '0RG70Z0', '0RG70Z1', '0RG70ZJ', '0RG8070', '0RG8071', '0RG807J', '0RG80A0', '0RG80A1', '0RG80AJ', '0RG80J0', '0RG80J1', '0RG80JJ', '0RG80K0', '0RG80K1', '0RG80KJ', '0RG80Z0', '0RG80Z1', '0RG80ZJ', '0RGA070', '0RGA071', '0RGA07J', '0RGA0A0', '0RGA0A1', '0RGA0AJ', '0RGA0J0', '0RGA0J1', '0RGA0JJ', '0RGA0K0', '0RGA0K1', '0RGA0KJ', '0RGA0Z0', '0RGA0Z1', '0RGA0ZJ', '0SG0070', '0SG0071', '0SG007J', '0SG00A0', '0SG00A1', '0SG00AJ', '0SG00J0', '0SG00J1', '0SG00JJ', '0SG00K0', '0SG00K1', '0SG00KJ', '0SG00Z0', '0SG00Z1', '0SG00ZJ', '0SG1070', '0SG1071', '0SG107J', '0SG10A0', '0SG10A1', '0SG10AJ', '0SG10J0', '0SG10J1', '0SG10JJ', '0SG10K0', '0SG10K1', '0SG10KJ', '0SG10Z0', '0SG10Z1', '0SG10ZJ', '0SG3070', '0SG3071', '0SG307J', '0SG30A0', '0SG30A1', '0SG30AJ', '0SG30J0', '0SG30J1', '0SG30JJ', '0SG30K0', '0SG30K1', '0SG30KJ', '0SG30Z0', '0SG30Z1', '0SG30ZJ', '81', '81.01', '81.02', '81.03', '81.04', '81.05', '81.06', '81.07', '81.08', '81.09', '81.3', '81.35', '81.36', '81.37', '81.38', '81.39', '81.61', '81.62', '81.63', '81.64', '84.51', '0WU80JZ', '0WU84JZ', '34.74', '22532', '22533', '22548', '22551', '22554', '22556', '22558', '22586', '22590', '22595', '22600', '22610', '22612', '22614', '22630', '22633', '22634', '22800', '22802', '22804', '22808', '22810', '22812', '22818', '22819', '22830', '22840', '22841', '22842', '22843', '22844', '22845', '22846', '22847', '22848', '22849', '22850', '22852', '22853', '22854', '22855', '22856', '22857', '22858', '22859', '22861', '22862', '22864', '22865', '22867', '22868', '22869', '22870', '21740', '21742', '21743')
GROUP BY z.ENC_ID,
z.PERSON_ID,
z.PROVIDER,
z.PERFORMINGPROVIDER,
d.SPECIALTY,
d.SPECIALTY2,
d.SPECIALTY3,
d.SPECIALTY4,
d.PROVIDER_TYPE,
d.PROVIDER_BIRTH_YEAR,
d.YEAR_GRADUATED,
d.PROVIDER_RACE,
d.PROVIDER_HISPANIC, YEAR(z.ADATE)"
)
tab3a2_prov <- tab3a_prov %>%
left_join(., specialties, by = "SPECIALTY") %>%
dplyr::filter(!duplicated(ENC_ID)) %>%
group_by(Description, px_date) %>%
mutate(
cohort_s = if_else(ENC_ID %in% tab1b2$ENC_ID[tab1b2$spine == 1], 1, 0),
cohort_p = if_else(ENC_ID %in% tab1b2$ENC_ID[tab1b2$pectus == 1], 1, 0)
) %>%
summarise(n_cohort_s = sum(cohort_s), spine = sum(spine), n_cohort_p = sum(cohort_p), pectus = sum(pectus)) %>%
dplyr::select(px_date, Description, n_cohort_s, spine, n_cohort_p, pectus)
This table contains information only for physicians, not fellows, residents, or other providers. Of the r n_distinct(tab1b2$PERSON_ID) total patients, r n_distinct(tab3a_prov$PERSON_ID) had provider data that was used to create the table below.
knitr::kable(tab3a2_prov, col.names = c("Year", "Provider Specialty", "Cohort Spine PXs", "Total Spine Procedures", "Cohort Pectus PXs", "Total Pectus Procedures"))
Table Pack 3B: Surgeon Demographics per Facility
The Counts for both Spine and Pectus are for any age of patient.
tab3a_prov$n_procs <- ave(tab3a_prov$PERFORMINGPROVIDER == tab3a_prov$PERFORMINGPROVIDER,
tab3a_prov$PERFORMINGPROVIDER,
FUN = cumsum)
prov_type$PROVIDER_TYPE <- as.character(prov_type$PROVIDER_TYPE)
tab3b <- tab3a_prov %>%
left_join(., specialties, by = "SPECIALTY") %>%
left_join(., prov_type, by = "PROVIDER_TYPE") %>%
group_by(PERFORMINGPROVIDER) %>%
mutate(
sum_spine = sum(spine),
sum_pectus = sum(pectus),
max_proc = max(n_procs, na.rm = T),
cohort_spine = if_else(ENC_ID %in% tab1b2$ENC_ID[tab1b2$spine == 1], 1, 0),
cohort_pectus = if_else(ENC_ID %in% tab1b2$ENC_ID[tab1b2$pectus == 1], 1, 0),
sum_cohort_s = sum(cohort_spine),
sum_cohort_p = sum(cohort_pectus),
p_race = dplyr::recode(PROVIDER_RACE,
HP = "Native Hawaiian or Other Pacific Islander",
IN = "American Indian/Alaska Native",
AS = "Asian",
BA = "Black or African American",
WH = "White",
MU = "More than one race, particular races unknown or not reported",
OT= "Other",
UN = "Unknown or Not Reported"),
p_hisp = dplyr::recode(PROVIDER_HISPANIC,
Y = "Yes",
N = "No",
U = "Unknown")
) %>%
dplyr::filter(!duplicated(PERFORMINGPROVIDER)) %>%
ungroup(.) %>%
select(PERFORMINGPROVIDER, sum_cohort_s, sum_spine, sum_cohort_p, sum_pectus, prov_desc, Description, PROVIDER_BIRTH_YEAR, p_race, p_hisp, YEAR_GRADUATED, max_proc)
knitr::kable(tab3b, col.names = c("Physician ID", "Num Cohort S", "Num Spine PXs", "Num Cohort P", "Num Pectus PXs", "Provider Type", "Phys Specialty", "Phys Birth Year", "Phys Year Graduated", "Phys Race", "Phys Ethnicity", "Total Volume"))
Data Tables Protocol: Table Set 4: Rx Information
Table 4A: Basic RX Information on Patient Discharge post Index Procedure CHCO
(Percent total of inclusion population – procedure code + inclusion criteria.)
UCCC/McLeod documentation built on May 16, 2019, 11:10 p.m.
R Package Documentation
Browse R Packages
We want your feedback!
Note that we can't provide technical support on individual packages. You should contact the package authors for that.
options(scipen=999, digits = 5, width = 88) options(qwraps2_markup = "markdown") options(stringAsFactors = F) options(dplyr.print_max = 10) options(dplyr.pring_min = 5) knitr::opts_chunk$set(cache = TRUE, echo = FALSE, results = "hide", message = FALSE, warning = FALSE) startTime <- Sys.time() library(knitr) library(tidyverse) library(qwraps2) con <- DBI::dbConnect(odbc::odbc(), Driver = "SQL Server", Server = "cc-s-d05.ucdenver.pvt", Database = "VDW_3_1_DH_0418")
start_dt <- "2011-01-01" end_dt <- "2016-12-31" ## helper functions # returns a vector with the column numbers of character variables in the data frame charVars <- function(df) grep('^ch',sapply(df,class)) # trims all character variables in a dataframe, sets blank to NA trimChrVars <- function(df){ for(i in charVars(df)){ df[,i] <- gsub('\\s+$','',df[,i]) df[,i] <- ifelse(nchar(df[,i])==0,NA,df[,i]) } df } # age category calculator ageCatCalc <- function(age){ ageCat <- factor( ifelse(is.na(age)==TRUE, 0, ifelse(age<0 , 1, ifelse(age>=0 & age<2, 2, ifelse(age>=2 & age<5, 3, ifelse(age>=5 & age<10, 4, ifelse (age>=10 & age<15, 5, ifelse(age>=15 & age<19, 6, ifelse(age>=19 & age<22, 7, ifelse(age>=22 & age<45, 8, ifelse(age>=45 & age<65, 9, ifelse(age>=65 & age<75, 10, ifelse(age>=75 & age<90, 11, ifelse(age>=90 , 12, 13)))))) ))))))), levels=0:13, labels=c('Missing','Negative','0-1','2-4','5-9','10-14','15-18','19-21','22-44','45-64','65-74','75-89','90+','Other') ) return(ageCat) } # Provider specialties specialties <- chordsTables::specialties # Medication Names med_names <- read_csv("Q:/MattCM/CHORDS/surg2rec/med-names.csv") devtools::use_data(med_names) # Provider type prov_type <- chordsTables::prov_type names(prov_type) <- c("PROVIDER_TYPE", "prov_desc") #Create long version of codes file codes <- readr::read_csv("Q:/MattCM/CHORDS/surg2rec/inclusion codes.csv") codes2 <- codes %>% tidyr::gather(.) %>% dplyr::filter(complete.cases(.)) %>% dplyr::rename(code_type = key, code = value) %>% dplyr::mutate( spine = if_else(stringr::str_detect(code_type, "spine"), 1, 0), pectus = if_else(stringr::str_detect(code_type, "pectus"), 1, 0), ProcedureType = if_else(spine == 1, "Spine", "Pectus") )
Table Set 1: Facility Demographics
Table 1A. Surgeries by Year and Surgery Type
This table contains the number of procedures performed by year and type of procedure
tab1a <- odbc::dbGetQuery(con, "SELECT DISTINCT z.ENC_ID, z.PERSON_ID, MAX(CASE WHEN z.PX IN('0RG4070', '0RG4071', '0RG407J', '0RG40A0', '0RG40A1', '0RG40AJ', '0RG40J0', '0RG40J1', '0RG40JJ', '0RG40K0', '0RG40K1', '0RG40KJ', '0RG40Z0', '0RG40Z1', '0RG40ZJ', '0RG6070', '0RG6071', '0RG607J', '0RG60A0', '0RG60A1', '0RG60AJ', '0RG60J0', '0RG60J1', '0RG60JJ', '0RG60K0', '0RG60K1', '0RG60KJ', '0RG60Z0', '0RG60Z1', '0RG60ZJ', '0RG7070', '0RG7071', '0RG707J', '0RG70A0', '0RG70A1', '0RG70AJ', '0RG70J0', '0RG70J1', '0RG70JJ', '0RG70K0', '0RG70K1', '0RG70KJ', '0RG70Z0', '0RG70Z1', '0RG70ZJ', '0RG8070', '0RG8071', '0RG807J', '0RG80A0', '0RG80A1', '0RG80AJ', '0RG80J0', '0RG80J1', '0RG80JJ', '0RG80K0', '0RG80K1', '0RG80KJ', '0RG80Z0', '0RG80Z1', '0RG80ZJ', '0RGA070', '0RGA071', '0RGA07J', '0RGA0A0', '0RGA0A1', '0RGA0AJ', '0RGA0J0', '0RGA0J1', '0RGA0JJ', '0RGA0K0', '0RGA0K1', '0RGA0KJ', '0RGA0Z0', '0RGA0Z1', '0RGA0ZJ', '0SG0070', '0SG0071', '0SG007J', '0SG00A0', '0SG00A1', '0SG00AJ', '0SG00J0', '0SG00J1', '0SG00JJ', '0SG00K0', '0SG00K1', '0SG00KJ', '0SG00Z0', '0SG00Z1', '0SG00ZJ', '0SG1070', '0SG1071', '0SG107J', '0SG10A0', '0SG10A1', '0SG10AJ', '0SG10J0', '0SG10J1', '0SG10JJ', '0SG10K0', '0SG10K1', '0SG10KJ', '0SG10Z0', '0SG10Z1', '0SG10ZJ', '0SG3070', '0SG3071', '0SG307J', '0SG30A0', '0SG30A1', '0SG30AJ', '0SG30J0', '0SG30J1', '0SG30JJ', '0SG30K0', '0SG30K1', '0SG30KJ', '0SG30Z0', '0SG30Z1', '0SG30ZJ', '81', '81.01', '81.02', '81.03', '81.04', '81.05', '81.06', '81.07', '81.08', '81.09', '81.3', '81.35', '81.36', '81.37', '81.38', '81.39', '81.61', '81.62', '81.63', '81.64', '84.51', '22532', '22533', '22548', '22551', '22554', '22556', '22558', '22586', '22590', '22595', '22600', '22610', '22612', '22614', '22630', '22633', '22634', '22800', '22802', '22804', '22808', '22810', '22812', '22818', '22819', '22830', '22840', '22841', '22842', '22843', '22844', '22845', '22846', '22847', '22848', '22849', '22850', '22852', '22853', '22854', '22855', '22856', '22857', '22858', '22859', '22861', '22862', '22864', '22865', '22867', '22868', '22869', '22870') THEN 1 ELSE 0 END) AS spine, MAX(CASE WHEN z.PX IN('0WU80JZ', '0WU84JZ', '34.74', '21740', '21742', '21743') THEN 1 ELSE 0 END) AS pectus, z.PROCDATE, YEAR(z.ADATE) as px_date FROM DEMOGRAPHICS a INNER JOIN [PROCEDURES] z ON z.PERSON_ID = a.PERSON_ID WHERE z.ADATE >= '01/01/2011' AND DATEDIFF(YY, a.BIRTH_DATE, z.ADATE) - CASE WHEN DATEADD(YY, DATEDIFF(YY, a.BIRTH_DATE, z.ADATE), a.BIRTH_DATE) > z.ADATE THEN 1 ELSE 0 END >= 10 AND DATEDIFF(YY, a.BIRTH_DATE, z.ADATE) - CASE WHEN DATEADD(YY, DATEDIFF(YY, a.BIRTH_DATE, z.ADATE), a.BIRTH_DATE) > z.ADATE THEN 1 ELSE 0 END <= 26 AND z.PX IN('0RG4070', '0RG4071', '0RG407J', '0RG40A0', '0RG40A1', '0RG40AJ', '0RG40J0', '0RG40J1', '0RG40JJ', '0RG40K0', '0RG40K1', '0RG40KJ', '0RG40Z0', '0RG40Z1', '0RG40ZJ', '0RG6070', '0RG6071', '0RG607J', '0RG60A0', '0RG60A1', '0RG60AJ', '0RG60J0', '0RG60J1', '0RG60JJ', '0RG60K0', '0RG60K1', '0RG60KJ', '0RG60Z0', '0RG60Z1', '0RG60ZJ', '0RG7070', '0RG7071', '0RG707J', '0RG70A0', '0RG70A1', '0RG70AJ', '0RG70J0', '0RG70J1', '0RG70JJ', '0RG70K0', '0RG70K1', '0RG70KJ', '0RG70Z0', '0RG70Z1', '0RG70ZJ', '0RG8070', '0RG8071', '0RG807J', '0RG80A0', '0RG80A1', '0RG80AJ', '0RG80J0', '0RG80J1', '0RG80JJ', '0RG80K0', '0RG80K1', '0RG80KJ', '0RG80Z0', '0RG80Z1', '0RG80ZJ', '0RGA070', '0RGA071', '0RGA07J', '0RGA0A0', '0RGA0A1', '0RGA0AJ', '0RGA0J0', '0RGA0J1', '0RGA0JJ', '0RGA0K0', '0RGA0K1', '0RGA0KJ', '0RGA0Z0', '0RGA0Z1', '0RGA0ZJ', '0SG0070', '0SG0071', '0SG007J', '0SG00A0', '0SG00A1', '0SG00AJ', '0SG00J0', '0SG00J1', '0SG00JJ', '0SG00K0', '0SG00K1', '0SG00KJ', '0SG00Z0', '0SG00Z1', '0SG00ZJ', '0SG1070', '0SG1071', '0SG107J', '0SG10A0', '0SG10A1', '0SG10AJ', '0SG10J0', '0SG10J1', '0SG10JJ', '0SG10K0', '0SG10K1', '0SG10KJ', '0SG10Z0', '0SG10Z1', '0SG10ZJ', '0SG3070', '0SG3071', '0SG307J', '0SG30A0', '0SG30A1', '0SG30AJ', '0SG30J0', '0SG30J1', '0SG30JJ', '0SG30K0', '0SG30K1', '0SG30KJ', '0SG30Z0', '0SG30Z1', '0SG30ZJ', '81', '81.01', '81.02', '81.03', '81.04', '81.05', '81.06', '81.07', '81.08', '81.09', '81.3', '81.35', '81.36', '81.37', '81.38', '81.39', '81.61', '81.62', '81.63', '81.64', '84.51', '0WU80JZ', '0WU84JZ', '34.74', '22532', '22533', '22548', '22551', '22554', '22556', '22558', '22586', '22590', '22595', '22600', '22610', '22612', '22614', '22630', '22633', '22634', '22800', '22802', '22804', '22808', '22810', '22812', '22818', '22819', '22830', '22840', '22841', '22842', '22843', '22844', '22845', '22846', '22847', '22848', '22849', '22850', '22852', '22853', '22854', '22855', '22856', '22857', '22858', '22859', '22861', '22862', '22864', '22865', '22867', '22868', '22869', '22870', '21740', '21742', '21743') GROUP BY z.PERSON_ID, z.ENC_ID, z.PROCDATE, YEAR(z.ADATE)" ) tab1a2 <- tab1a %>% group_by(PERSON_ID) %>% mutate( min_dt = min(PROCDATE), use_dt = if_else(min_dt == PROCDATE, 1, 0) ) %>% ungroup(.) %>% dplyr::filter(use_dt == 1) %>% group_by(px_date) %>% summarise(spine = sum(spine), pectus = sum(pectus)) %>% ungroup(.) tab1a_tot <- odbc::dbGetQuery(con, "SELECT count(*) as n_pxs, YEAR(z.ADATE) as px_date FROM DEMOGRAPHICS a JOIN [PROCEDURES] z ON z.PERSON_ID = a.PERSON_ID WHERE z.ADATE >= '01/01/2011' AND DATEDIFF(YY, a.BIRTH_DATE, z.ADATE) - CASE WHEN DATEADD(YY, DATEDIFF(YY, a.BIRTH_DATE, z.ADATE), a.BIRTH_DATE) > z.ADATE THEN 1 ELSE 0 END >= 10 AND DATEDIFF(YY, a.BIRTH_DATE, z.ADATE) - CASE WHEN DATEADD(YY, DATEDIFF(YY, a.BIRTH_DATE, z.ADATE), a.BIRTH_DATE) > z.ADATE THEN 1 ELSE 0 END <= 26 GROUP BY YEAR(z.ADATE) ORDER BY YEAR(z.ADATE)" ) tab1a_all <- tab1a2 %>% left_join(tab1a_tot, by = "px_date") %>% mutate( spine2 = paste0(spine, " (", round((spine/n_pxs) * 100, 2), "%)"), pectus2 = paste0(pectus, " (", round((pectus/n_pxs) * 100, 2), "%)"), total = paste0(spine + pectus, " (", round(((spine + pectus)/n_pxs) * 100, 2), "%)") ) %>% select(px_date, spine2, pectus2, total)
knitr::kable(tab1a_all, col.names = c("Year", "Spine", "Pectus", "Total"))
Table 1B: Cumulative Demographic Demographic Differences by Facility
Heights and weights in this table represent an average of the heights and weights recorded during the procedure encounter. This was done because 1) height and weight weren't measured at the same time for some individuals and 2) height and weight weren't measured during consistent times for each participant (i.e,g measures could be at any point in the encounter).
tab1b <- odbc::dbGetQuery(con, "SELECT DISTINCT z.ENC_ID, z.PERSON_ID, MAX(CASE WHEN z.PX IN('0RG4070', '0RG4071', '0RG407J', '0RG40A0', '0RG40A1', '0RG40AJ', '0RG40J0', '0RG40J1', '0RG40JJ', '0RG40K0', '0RG40K1', '0RG40KJ', '0RG40Z0', '0RG40Z1', '0RG40ZJ', '0RG6070', '0RG6071', '0RG607J', '0RG60A0', '0RG60A1', '0RG60AJ', '0RG60J0', '0RG60J1', '0RG60JJ', '0RG60K0', '0RG60K1', '0RG60KJ', '0RG60Z0', '0RG60Z1', '0RG60ZJ', '0RG7070', '0RG7071', '0RG707J', '0RG70A0', '0RG70A1', '0RG70AJ', '0RG70J0', '0RG70J1', '0RG70JJ', '0RG70K0', '0RG70K1', '0RG70KJ', '0RG70Z0', '0RG70Z1', '0RG70ZJ', '0RG8070', '0RG8071', '0RG807J', '0RG80A0', '0RG80A1', '0RG80AJ', '0RG80J0', '0RG80J1', '0RG80JJ', '0RG80K0', '0RG80K1', '0RG80KJ', '0RG80Z0', '0RG80Z1', '0RG80ZJ', '0RGA070', '0RGA071', '0RGA07J', '0RGA0A0', '0RGA0A1', '0RGA0AJ', '0RGA0J0', '0RGA0J1', '0RGA0JJ', '0RGA0K0', '0RGA0K1', '0RGA0KJ', '0RGA0Z0', '0RGA0Z1', '0RGA0ZJ', '0SG0070', '0SG0071', '0SG007J', '0SG00A0', '0SG00A1', '0SG00AJ', '0SG00J0', '0SG00J1', '0SG00JJ', '0SG00K0', '0SG00K1', '0SG00KJ', '0SG00Z0', '0SG00Z1', '0SG00ZJ', '0SG1070', '0SG1071', '0SG107J', '0SG10A0', '0SG10A1', '0SG10AJ', '0SG10J0', '0SG10J1', '0SG10JJ', '0SG10K0', '0SG10K1', '0SG10KJ', '0SG10Z0', '0SG10Z1', '0SG10ZJ', '0SG3070', '0SG3071', '0SG307J', '0SG30A0', '0SG30A1', '0SG30AJ', '0SG30J0', '0SG30J1', '0SG30JJ', '0SG30K0', '0SG30K1', '0SG30KJ', '0SG30Z0', '0SG30Z1', '0SG30ZJ', '81', '81.01', '81.02', '81.03', '81.04', '81.05', '81.06', '81.07', '81.08', '81.09', '81.3', '81.35', '81.36', '81.37', '81.38', '81.39', '81.61', '81.62', '81.63', '81.64', '84.51', '22532', '22533', '22548', '22551', '22554', '22556', '22558', '22586', '22590', '22595', '22600', '22610', '22612', '22614', '22630', '22633', '22634', '22800', '22802', '22804', '22808', '22810', '22812', '22818', '22819', '22830', '22840', '22841', '22842', '22843', '22844', '22845', '22846', '22847', '22848', '22849', '22850', '22852', '22853', '22854', '22855', '22856', '22857', '22858', '22859', '22861', '22862', '22864', '22865', '22867', '22868', '22869', '22870') THEN 1 ELSE 0 END) AS spine, MAX(CASE WHEN z.PX IN('0WU80JZ', '0WU84JZ', '34.74', '21740', '21742', '21743') THEN 1 ELSE 0 END) AS pectus, DATEDIFF(YY, a.BIRTH_DATE, z.ADATE) - CASE WHEN DATEADD(YY, DATEDIFF(YY, a.BIRTH_DATE, z.ADATE), a.BIRTH_DATE) > z.ADATE THEN 1 ELSE 0 END AS [AGE], z.ADATE, z.PROCDATE, a.GENDER, a.PRIMARY_LANGUAGE, a.RACE1, b.HT, b.WT, b.MEASURE_DATE, c.GEOCODE, c.LOC_START, c.LOC_END, d.DDATE FROM DEMOGRAPHICS a INNER JOIN [PROCEDURES] z ON z.PERSON_ID = a.PERSON_ID LEFT JOIN [ENCOUNTERS] d ON z.PERSON_ID = d.PERSON_ID AND z.ENC_ID = d.ENC_ID LEFT JOIN [VITAL_SIGNS] b ON z.PERSON_ID = b.PERSON_ID AND z.ENC_ID = b.ENC_ID LEFT JOIN [CENSUS_LOCATION] c ON z.PERSON_ID = c.PERSON_ID AND CASE WHEN c.LOC_END IS NOT NULL AND z.ADATE BETWEEN c.LOC_START AND c.LOC_END THEN 1 WHEN c.LOC_END IS NULL AND c.LOC_START <= z.ADATE THEN 1 ELSE 0 END = 1 WHERE z.ADATE >= '01/01/2011' AND DATEDIFF(YY, a.BIRTH_DATE, z.ADATE) - CASE WHEN DATEADD(YY, DATEDIFF(YY, a.BIRTH_DATE, z.ADATE), a.BIRTH_DATE) > z.ADATE THEN 1 ELSE 0 END >= 10 AND DATEDIFF(YY, a.BIRTH_DATE, z.ADATE) - CASE WHEN DATEADD(YY, DATEDIFF(YY, a.BIRTH_DATE, z.ADATE), a.BIRTH_DATE) > z.ADATE THEN 1 ELSE 0 END <= 26 AND z.PX IN('0RG4070', '0RG4071', '0RG407J', '0RG40A0', '0RG40A1', '0RG40AJ', '0RG40J0', '0RG40J1', '0RG40JJ', '0RG40K0', '0RG40K1', '0RG40KJ', '0RG40Z0', '0RG40Z1', '0RG40ZJ', '0RG6070', '0RG6071', '0RG607J', '0RG60A0', '0RG60A1', '0RG60AJ', '0RG60J0', '0RG60J1', '0RG60JJ', '0RG60K0', '0RG60K1', '0RG60KJ', '0RG60Z0', '0RG60Z1', '0RG60ZJ', '0RG7070', '0RG7071', '0RG707J', '0RG70A0', '0RG70A1', '0RG70AJ', '0RG70J0', '0RG70J1', '0RG70JJ', '0RG70K0', '0RG70K1', '0RG70KJ', '0RG70Z0', '0RG70Z1', '0RG70ZJ', '0RG8070', '0RG8071', '0RG807J', '0RG80A0', '0RG80A1', '0RG80AJ', '0RG80J0', '0RG80J1', '0RG80JJ', '0RG80K0', '0RG80K1', '0RG80KJ', '0RG80Z0', '0RG80Z1', '0RG80ZJ', '0RGA070', '0RGA071', '0RGA07J', '0RGA0A0', '0RGA0A1', '0RGA0AJ', '0RGA0J0', '0RGA0J1', '0RGA0JJ', '0RGA0K0', '0RGA0K1', '0RGA0KJ', '0RGA0Z0', '0RGA0Z1', '0RGA0ZJ', '0SG0070', '0SG0071', '0SG007J', '0SG00A0', '0SG00A1', '0SG00AJ', '0SG00J0', '0SG00J1', '0SG00JJ', '0SG00K0', '0SG00K1', '0SG00KJ', '0SG00Z0', '0SG00Z1', '0SG00ZJ', '0SG1070', '0SG1071', '0SG107J', '0SG10A0', '0SG10A1', '0SG10AJ', '0SG10J0', '0SG10J1', '0SG10JJ', '0SG10K0', '0SG10K1', '0SG10KJ', '0SG10Z0', '0SG10Z1', '0SG10ZJ', '0SG3070', '0SG3071', '0SG307J', '0SG30A0', '0SG30A1', '0SG30AJ', '0SG30J0', '0SG30J1', '0SG30JJ', '0SG30K0', '0SG30K1', '0SG30KJ', '0SG30Z0', '0SG30Z1', '0SG30ZJ', '81', '81.01', '81.02', '81.03', '81.04', '81.05', '81.06', '81.07', '81.08', '81.09', '81.3', '81.35', '81.36', '81.37', '81.38', '81.39', '81.61', '81.62', '81.63', '81.64', '84.51', '0WU80JZ', '0WU84JZ', '34.74', '22532', '22533', '22548', '22551', '22554', '22556', '22558', '22586', '22590', '22595', '22600', '22610', '22612', '22614', '22630', '22633', '22634', '22800', '22802', '22804', '22808', '22810', '22812', '22818', '22819', '22830', '22840', '22841', '22842', '22843', '22844', '22845', '22846', '22847', '22848', '22849', '22850', '22852', '22853', '22854', '22855', '22856', '22857', '22858', '22859', '22861', '22862', '22864', '22865', '22867', '22868', '22869', '22870', '21740', '21742', '21743') GROUP BY z.PERSON_ID, z.ENC_ID, z.PROCDATE, DATEDIFF(YY, a.BIRTH_DATE, z.ADATE) - CASE WHEN DATEADD(YY, DATEDIFF(YY, a.BIRTH_DATE, z.ADATE), a.BIRTH_DATE) > z.ADATE THEN 1 ELSE 0 END, z.ADATE, a.GENDER, a.PRIMARY_LANGUAGE, a.RACE1, b.HT, b.WT, b.MEASURE_DATE, c.GEOCODE, c.LOC_START, c.LOC_END, d.DDATE" ) tab1b$stateCnty <-ifelse(tab1b$GEOCODE == "", NA, substr(tab1b$GEOCODE, 1, 5)) ## Change code below when package is created states <- chordsTables::stateCnty #%>% #select(state, stateFP, countyFP, stateCnty) tab1b2 <- tab1b %>% left_join(., states, by = "stateCnty") %>% arrange(PERSON_ID, PROCDATE) %>% group_by(PERSON_ID) %>% mutate( end_geodt = if_else(is.na(LOC_END), as.Date("2018-05-09"), as.Date(LOC_END)), geo_adate = if_else(ADATE <= end_geodt, 1, 0), date_diff = as.Date(PROCDATE) - as.Date(MEASURE_DATE), miss_htwt = if_else(!is.na(HT) & !is.na(WT), 1, 0), min_diff = min(abs(date_diff), na.rm = T), use_date = if_else(as.Date(PROCDATE) + min_diff == MEASURE_DATE, 1, 0), dup_id = if_else(duplicated(ENC_ID), 1, 0), avg_ht = mean(HT, na.rm = T), avg_wt = mean(WT, na.rm = T), miss_avg = if_else(!is.na(avg_ht) & !is.na(avg_wt), 1, 0), min_yr = min(ADATE), use_yr = if_else(min_yr == ADATE, 1, 0), ProcedureType = if_else(spine == 1, "Spine", "Pectus") ) %>% ungroup(.) %>% dplyr::filter(use_yr == 1) %>% dplyr::filter(!duplicated(PERSON_ID) & !duplicated(ENC_ID)) tab1b_sum <- list("Sample size" = list( "N = " =~ dplyr::n_distinct(PERSON_ID)), "Age" = list("Mean (SD)" = ~ qwraps2::mean_sd(AGE, na_rm = T), "Median (IQR)" = ~ qwraps2::median_iqr(AGE, na_rm = T)), "Gender, Freqency(%)" = list("Male" = ~ qwraps2::n_perc(GENDER == "M", na_rm = T)), "Primary Language, Freqency(%)" = list("English" = ~ qwraps2::n_perc(PRIMARY_LANGUAGE == "eng", na_rm = T), "Spanish" = ~ qwraps2::n_perc(PRIMARY_LANGUAGE == "spa", na_rm = T)), "Race, Freqency(%)" = list("Native Hawaiian or Other Pacific Islander" = ~ qwraps2::n_perc(RACE1 == "HP", na_rm = T), "American Indian/Alaska Native" = ~ qwraps2::n_perc(RACE1 == "IN", na_rm = T), "Asian" = ~ qwraps2::n_perc(RACE1 == "AS", na_rm = T), "Black or African American" = ~ qwraps2::n_perc(RACE1 == "BA", na_rm = T), "White" = ~ qwraps2::n_perc(RACE1 == "WH", na_rm = T), "More than one race" = ~ qwraps2::n_perc(RACE1 == "MU", na_rm = T), "Unknown" = ~ qwraps2::n_perc(RACE1 == "UN", na_rm = T)), "Height" = list("Mean (SD)" = ~ qwraps2::mean_sd(avg_ht, na_rm = T)), "Weight" = list("Mean (SD)" = ~ qwraps2::mean_sd(avg_wt, na_rm = T)), "State of patient address" = list("Colorado" = ~ qwraps2::n_perc(state == "CO", na_rm = T), "Wyoming" = ~ qwraps2::n_perc(state == "WY", na_rm = T), "Nebraska" = ~ qwraps2::n_perc(state == "NE", na_rm = T), "New Mexico" = ~ qwraps2::n_perc(state == "NM", na_rm = T), "Oklahoma" = ~ qwraps2::n_perc(state == "OK", na_rm = T), "South Dakota" = ~ qwraps2::n_perc(state == "SD", na_rm = T), "Kansas" = ~ qwraps2::n_perc(state == "KS", na_rm = T), "Other/Missing"= ~ qwraps2::n_perc((!(state %in% c("CO", "WY", "NE", "NM", "OK", "SD", "KS") & !is.na(state))) , na_rm = T)) )
if(length(dimnames(table(tab1b2$ProcedureType))) == 1){ tab1b_tot_sum <- qwraps2::summary_table(tab1b2, tab1b_sum) tab1b_out <- tab1b_tot_sum print(tab1b_out, cnames = dimnames(table(tab1b2$ProcedureType))[[1]][1]) } else{ tab1b_tot_sum <- qwraps2::summary_table(tab1b2, tab1b_sum) tab1b_proc_sum <- qwraps2::summary_table(dplyr::group_by(tab1b2, "ProcedureType"), tab1b_sum) tab1b_out <- cbind(tab1b_tot_sum, tab1b_proc_sum) print(tab1b_out, cnames = c("Total", dimnames(table(tab1b2$ProcedureType))[[1]])) }
Table 1C: Patient Location by Year
tab1c <- tab1b2 %>% dplyr::filter(state == "CO") %>% mutate( YEAR = lubridate::year(PROCDATE) ) %>% group_by(YEAR) qwraps2::summary_table(tab1c, list("County of Residence" = with(tab1c, qwraps2::tab_summary(countyName, n_perc_args = list(digits = 2, show_symbol = TRUE)))))
Table 1D: Length of Stay by Location & Type Summary (All Years)
# Some length discharge values are 1900-01-01 so a new variable needs to be created to turn values < 0 to missing for # length of stay tab1d <- tab1b2 %>% mutate( los = as.Date(DDATE) - as.Date(ADATE), los2 = if_else(los < 0, NA_integer_, as.integer(los)) ) tab1d_sum <- list( "Length of Stay (Days)" = list( "Mean (SD)" =~ qwraps2::mean_sd(los2, na_rm = T), "Median (IQR)" =~ qwraps2::median_iqr(los2, na_rm = T), "Range" =~ paste(min(los2, na.rm = T), " - ", max(los2, na.rm = T)) ) )
if(length(dimnames(table(tab1d$ProcedureType))) == 1){ tab1d_tot_sum <- qwraps2::summary_table(tab1d, tab1d_sum) tab1d_out <- tab1d_tot_sum print(tab1d_out, cnames = dimnames(table(tab1d$ProcedureType))[[1]][1]) } else{ tab1d_tot_sum <- qwraps2::summary_table(tab1d, tab1d_sum) tab1d_proc_sum <- qwraps2::summary_table(dplyr::group_by(tab1d, ProcedureType), tab1d_sum) tab1d_out <- cbind(tab1d_tot_sum, tab1d_proc_sum) print(tab1b_out, cnames = c("Total", dimnames(table(tab1b2$ProcedureType))[[1]])) }
Table Pack 1E: Length of Stay by Location and Type
tab1e <- tab1d %>% mutate( YEAR = lubridate::year(PROCDATE) ) if(length(dimnames(table(tab1e$ProcedureType))) == 1){ tab1e_tot_sum <- qwraps2::summary_table(dplyr::group_by(tab1e, YEAR), tab1d_sum) tab1e_out <- tab1e_tot_sum print(tab1e_out) } else{ tab1e_tot_sum <- qwraps2::summary_table(tab1e, tab1e_sum) tab1e_proc_sum <- qwraps2::summary_table(dplyr::group_by(tab1e, ProcedureType, YEAR), tab1d_sum) tab1e_out <- cbind(tab1e_tot_sum, tab1e_proc_sum) print(tab1b_out, cnames = c("Total", dimnames(table(tab1b2$ProcedureType))[[1]])) }
Figure 1A: Histogram of Patient Age at surgery (CPT Code/Proc Code Date)
fig1a <- ggplot(data = tab1b2, aes(x = AGE)) + geom_histogram(color="black", fill="red", binwidth = 1) + ylab("Count") + xlab("Age") fig1a
Data Tables Protocol: Table Set 2: Patient Demographic Tables
Table Pack 2A: Diagnosis Code Frequencies by Location
tab2a <- odbc::dbGetQuery(con, "SELECT DISTINCT z.ENC_ID, z.PERSON_ID, d.DIAGNOSES_ID, d.DX, d.PRIMARY_DX, d.PRINCIPAL_DX FROM DEMOGRAPHICS a INNER JOIN [PROCEDURES] z ON z.PERSON_ID = a.PERSON_ID LEFT JOIN [DIAGNOSES] d ON z.PERSON_ID = d.PERSON_ID AND z.ENC_ID = d.ENC_ID WHERE z.ADATE >= '01/01/2011' AND DATEDIFF(YY, a.BIRTH_DATE, z.ADATE) - CASE WHEN DATEADD(YY, DATEDIFF(YY, a.BIRTH_DATE, z.ADATE), a.BIRTH_DATE) > z.ADATE THEN 1 ELSE 0 END >= 10 AND DATEDIFF(YY, a.BIRTH_DATE, z.ADATE) - CASE WHEN DATEADD(YY, DATEDIFF(YY, a.BIRTH_DATE, z.ADATE), a.BIRTH_DATE) > z.ADATE THEN 1 ELSE 0 END <= 26 AND z.PX IN('0RG4070', '0RG4071', '0RG407J', '0RG40A0', '0RG40A1', '0RG40AJ', '0RG40J0', '0RG40J1', '0RG40JJ', '0RG40K0', '0RG40K1', '0RG40KJ', '0RG40Z0', '0RG40Z1', '0RG40ZJ', '0RG6070', '0RG6071', '0RG607J', '0RG60A0', '0RG60A1', '0RG60AJ', '0RG60J0', '0RG60J1', '0RG60JJ', '0RG60K0', '0RG60K1', '0RG60KJ', '0RG60Z0', '0RG60Z1', '0RG60ZJ', '0RG7070', '0RG7071', '0RG707J', '0RG70A0', '0RG70A1', '0RG70AJ', '0RG70J0', '0RG70J1', '0RG70JJ', '0RG70K0', '0RG70K1', '0RG70KJ', '0RG70Z0', '0RG70Z1', '0RG70ZJ', '0RG8070', '0RG8071', '0RG807J', '0RG80A0', '0RG80A1', '0RG80AJ', '0RG80J0', '0RG80J1', '0RG80JJ', '0RG80K0', '0RG80K1', '0RG80KJ', '0RG80Z0', '0RG80Z1', '0RG80ZJ', '0RGA070', '0RGA071', '0RGA07J', '0RGA0A0', '0RGA0A1', '0RGA0AJ', '0RGA0J0', '0RGA0J1', '0RGA0JJ', '0RGA0K0', '0RGA0K1', '0RGA0KJ', '0RGA0Z0', '0RGA0Z1', '0RGA0ZJ', '0SG0070', '0SG0071', '0SG007J', '0SG00A0', '0SG00A1', '0SG00AJ', '0SG00J0', '0SG00J1', '0SG00JJ', '0SG00K0', '0SG00K1', '0SG00KJ', '0SG00Z0', '0SG00Z1', '0SG00ZJ', '0SG1070', '0SG1071', '0SG107J', '0SG10A0', '0SG10A1', '0SG10AJ', '0SG10J0', '0SG10J1', '0SG10JJ', '0SG10K0', '0SG10K1', '0SG10KJ', '0SG10Z0', '0SG10Z1', '0SG10ZJ', '0SG3070', '0SG3071', '0SG307J', '0SG30A0', '0SG30A1', '0SG30AJ', '0SG30J0', '0SG30J1', '0SG30JJ', '0SG30K0', '0SG30K1', '0SG30KJ', '0SG30Z0', '0SG30Z1', '0SG30ZJ', '81', '81.01', '81.02', '81.03', '81.04', '81.05', '81.06', '81.07', '81.08', '81.09', '81.3', '81.35', '81.36', '81.37', '81.38', '81.39', '81.61', '81.62', '81.63', '81.64', '84.51', '0WU80JZ', '0WU84JZ', '34.74', '22532', '22533', '22548', '22551', '22554', '22556', '22558', '22586', '22590', '22595', '22600', '22610', '22612', '22614', '22630', '22633', '22634', '22800', '22802', '22804', '22808', '22810', '22812', '22818', '22819', '22830', '22840', '22841', '22842', '22843', '22844', '22845', '22846', '22847', '22848', '22849', '22850', '22852', '22853', '22854', '22855', '22856', '22857', '22858', '22859', '22861', '22862', '22864', '22865', '22867', '22868', '22869', '22870', '21740', '21742', '21743')" ) length(unique(tab2a$PERSON_ID)) los_tab <- tab1d %>% select(PERSON_ID, ProcedureType, los2) #The code in the mutate section below counts the different responses from PRIMARY_DX #In order to determine if any secondary diagnoses can be used enlui of primary dxs #There are no secondary diagnoses without a primary diagnosis in the DH Date tab2a2 <- tab2a %>% dplyr::filter(PRIMARY_DX == "P") %>% left_join(., los_tab, by = "PERSON_ID") tab2_test <- as.data.frame(table(tab2a2$ProcedureType, tab2a2$DX)) list_tab2 <- split(tab2_test[, c(1,2)], seq(nrow(tab2_test[, c(1,2)]))) dx_tab <- data.frame(avg_los = unlist(lapply(list_tab2, function(x) mean(tab2a2[tab2a2$ProcedureType == x[[1]] & tab2a2$DX == x[[2]], "los2"], na.rm = T)))) dx_tab2 <- data.frame(med_los = unlist(lapply(list_tab2, function(x) median(tab2a2[tab2a2$ProcedureType == x[[1]] & tab2a2$DX == x[[2]], "los2"], na.rm = T)))) tab2a3 <- bind_cols(tab2_test, dx_tab) tab2a4 <- bind_cols(tab2a3, dx_tab2) %>% dplyr::rename(ProcedureType = Var1, DX = Var2)
Of the r n_distinct(tab2a$PERSON_ID) patients from the initial cohort, r n_distinct(tab2a2$PERSON_ID) had primary diagnoses that could be used for the following table.
knitr::kable(tab2a4, col.names = c("Procedure Type", "Primary DX", "Count of DX", "Mean Length of Stay", "Median Length of Stay"))
Table Pack 2B: Procedure Code Frequencies & LOS by Location
tab2b <- odbc::dbGetQuery(con, "SELECT DISTINCT z.ENC_ID, z.PERSON_ID, z.PX, MAX(CASE WHEN z.PX IN('0RG4070', '0RG4071', '0RG407J', '0RG40A0', '0RG40A1', '0RG40AJ', '0RG40J0', '0RG40J1', '0RG40JJ', '0RG40K0', '0RG40K1', '0RG40KJ', '0RG40Z0', '0RG40Z1', '0RG40ZJ', '0RG6070', '0RG6071', '0RG607J', '0RG60A0', '0RG60A1', '0RG60AJ', '0RG60J0', '0RG60J1', '0RG60JJ', '0RG60K0', '0RG60K1', '0RG60KJ', '0RG60Z0', '0RG60Z1', '0RG60ZJ', '0RG7070', '0RG7071', '0RG707J', '0RG70A0', '0RG70A1', '0RG70AJ', '0RG70J0', '0RG70J1', '0RG70JJ', '0RG70K0', '0RG70K1', '0RG70KJ', '0RG70Z0', '0RG70Z1', '0RG70ZJ', '0RG8070', '0RG8071', '0RG807J', '0RG80A0', '0RG80A1', '0RG80AJ', '0RG80J0', '0RG80J1', '0RG80JJ', '0RG80K0', '0RG80K1', '0RG80KJ', '0RG80Z0', '0RG80Z1', '0RG80ZJ', '0RGA070', '0RGA071', '0RGA07J', '0RGA0A0', '0RGA0A1', '0RGA0AJ', '0RGA0J0', '0RGA0J1', '0RGA0JJ', '0RGA0K0', '0RGA0K1', '0RGA0KJ', '0RGA0Z0', '0RGA0Z1', '0RGA0ZJ', '0SG0070', '0SG0071', '0SG007J', '0SG00A0', '0SG00A1', '0SG00AJ', '0SG00J0', '0SG00J1', '0SG00JJ', '0SG00K0', '0SG00K1', '0SG00KJ', '0SG00Z0', '0SG00Z1', '0SG00ZJ', '0SG1070', '0SG1071', '0SG107J', '0SG10A0', '0SG10A1', '0SG10AJ', '0SG10J0', '0SG10J1', '0SG10JJ', '0SG10K0', '0SG10K1', '0SG10KJ', '0SG10Z0', '0SG10Z1', '0SG10ZJ', '0SG3070', '0SG3071', '0SG307J', '0SG30A0', '0SG30A1', '0SG30AJ', '0SG30J0', '0SG30J1', '0SG30JJ', '0SG30K0', '0SG30K1', '0SG30KJ', '0SG30Z0', '0SG30Z1', '0SG30ZJ', '81', '81.01', '81.02', '81.03', '81.04', '81.05', '81.06', '81.07', '81.08', '81.09', '81.3', '81.35', '81.36', '81.37', '81.38', '81.39', '81.61', '81.62', '81.63', '81.64', '84.51', '22532', '22533', '22548', '22551', '22554', '22556', '22558', '22586', '22590', '22595', '22600', '22610', '22612', '22614', '22630', '22633', '22634', '22800', '22802', '22804', '22808', '22810', '22812', '22818', '22819', '22830', '22840', '22841', '22842', '22843', '22844', '22845', '22846', '22847', '22848', '22849', '22850', '22852', '22853', '22854', '22855', '22856', '22857', '22858', '22859', '22861', '22862', '22864', '22865', '22867', '22868', '22869', '22870') THEN 1 ELSE 0 END) AS spine, MAX(CASE WHEN z.PX IN('0WU80JZ', '0WU84JZ', '34.74', '21740', '21742', '21743') THEN 1 ELSE 0 END) AS pectus, z.PROCDATE, YEAR(z.ADATE) as px_date FROM DEMOGRAPHICS a INNER JOIN [PROCEDURES] z ON z.PERSON_ID = a.PERSON_ID WHERE z.ADATE >= '01/01/2011' AND DATEDIFF(YY, a.BIRTH_DATE, z.ADATE) - CASE WHEN DATEADD(YY, DATEDIFF(YY, a.BIRTH_DATE, z.ADATE), a.BIRTH_DATE) > z.ADATE THEN 1 ELSE 0 END >= 10 AND DATEDIFF(YY, a.BIRTH_DATE, z.ADATE) - CASE WHEN DATEADD(YY, DATEDIFF(YY, a.BIRTH_DATE, z.ADATE), a.BIRTH_DATE) > z.ADATE THEN 1 ELSE 0 END <= 26 AND z.PX IN('0RG4070', '0RG4071', '0RG407J', '0RG40A0', '0RG40A1', '0RG40AJ', '0RG40J0', '0RG40J1', '0RG40JJ', '0RG40K0', '0RG40K1', '0RG40KJ', '0RG40Z0', '0RG40Z1', '0RG40ZJ', '0RG6070', '0RG6071', '0RG607J', '0RG60A0', '0RG60A1', '0RG60AJ', '0RG60J0', '0RG60J1', '0RG60JJ', '0RG60K0', '0RG60K1', '0RG60KJ', '0RG60Z0', '0RG60Z1', '0RG60ZJ', '0RG7070', '0RG7071', '0RG707J', '0RG70A0', '0RG70A1', '0RG70AJ', '0RG70J0', '0RG70J1', '0RG70JJ', '0RG70K0', '0RG70K1', '0RG70KJ', '0RG70Z0', '0RG70Z1', '0RG70ZJ', '0RG8070', '0RG8071', '0RG807J', '0RG80A0', '0RG80A1', '0RG80AJ', '0RG80J0', '0RG80J1', '0RG80JJ', '0RG80K0', '0RG80K1', '0RG80KJ', '0RG80Z0', '0RG80Z1', '0RG80ZJ', '0RGA070', '0RGA071', '0RGA07J', '0RGA0A0', '0RGA0A1', '0RGA0AJ', '0RGA0J0', '0RGA0J1', '0RGA0JJ', '0RGA0K0', '0RGA0K1', '0RGA0KJ', '0RGA0Z0', '0RGA0Z1', '0RGA0ZJ', '0SG0070', '0SG0071', '0SG007J', '0SG00A0', '0SG00A1', '0SG00AJ', '0SG00J0', '0SG00J1', '0SG00JJ', '0SG00K0', '0SG00K1', '0SG00KJ', '0SG00Z0', '0SG00Z1', '0SG00ZJ', '0SG1070', '0SG1071', '0SG107J', '0SG10A0', '0SG10A1', '0SG10AJ', '0SG10J0', '0SG10J1', '0SG10JJ', '0SG10K0', '0SG10K1', '0SG10KJ', '0SG10Z0', '0SG10Z1', '0SG10ZJ', '0SG3070', '0SG3071', '0SG307J', '0SG30A0', '0SG30A1', '0SG30AJ', '0SG30J0', '0SG30J1', '0SG30JJ', '0SG30K0', '0SG30K1', '0SG30KJ', '0SG30Z0', '0SG30Z1', '0SG30ZJ', '81', '81.01', '81.02', '81.03', '81.04', '81.05', '81.06', '81.07', '81.08', '81.09', '81.3', '81.35', '81.36', '81.37', '81.38', '81.39', '81.61', '81.62', '81.63', '81.64', '84.51', '0WU80JZ', '0WU84JZ', '34.74', '22532', '22533', '22548', '22551', '22554', '22556', '22558', '22586', '22590', '22595', '22600', '22610', '22612', '22614', '22630', '22633', '22634', '22800', '22802', '22804', '22808', '22810', '22812', '22818', '22819', '22830', '22840', '22841', '22842', '22843', '22844', '22845', '22846', '22847', '22848', '22849', '22850', '22852', '22853', '22854', '22855', '22856', '22857', '22858', '22859', '22861', '22862', '22864', '22865', '22867', '22868', '22869', '22870', '21740', '21742', '21743') GROUP BY z.PERSON_ID, z.ENC_ID, z.PX, z.PROCDATE, YEAR(z.ADATE)" ) tab2b2 <- tab2b %>% group_by(PERSON_ID) %>% mutate( min_dt = min(as.Date(PROCDATE)), use_dt = if_else(as.Date(min_dt) == as.Date(PROCDATE), 1, 0) ) %>% ungroup(.) %>% dplyr::filter(use_dt == 1) %>% left_join(., los_tab, by = "PERSON_ID") %>% group_by(PERSON_ID) %>% mutate( px_num = paste0("px_", 1:length(PERSON_ID)) ) %>% ungroup(.) tab2b_wide <- tidyr::spread(tab2b2, px_num, PX) %>% mutate( all_pxs = if_else(is.na(px_2), px_1, if_else(is.na(px_3), paste(px_1, px_2, sep = ", "), if_else(is.na(px_4), paste(px_1, px_2, px_3, sep = ", "), if_else(is.na(px_5), paste(px_1, px_2, px_3, px_4, sep = ", "), paste(px_1, px_2, px_3, px_4, px_5, sep = ", "))))) ) table(tab2b_wide$all_pxs) #Get frequencies of px combinations tab2b_test <- as.data.frame(table(tab2b_wide$ProcedureType, tab2b_wide$all_pxs)) # Create a list of unique pxs and px types to cycle through to get means and medians list_tab2b <- split(tab2b_test[, c(1,2)], seq(nrow(tab2b_test[, c(1,2)]))) names(list_tab2b) #get means for all uniqe combinations of pxs and px types px_tab <- data.frame(avg_los = unlist(lapply(list_tab2b, function(x) mean(as.numeric(tab2b_wide$los2[tab2b_wide$ProcedureType == x[[1]] & tab2b_wide$all_pxs == x[[2]]]), na.rm = T)))) mean(as.numeric(tab2b_wide$los2[tab2b_wide$ProcedureType == list_tab2b[[58]][[1]] & tab2b_wide$all_pxs == list_tab2b[[58]][[2]]]), na.rm = T) #get medians for all uniqe combinations of pxs and px types px_tab2 <- data.frame(med_los = unlist(lapply(list_tab2b, function(x) median(as.numeric(tab2b_wide$los2[tab2b_wide$ProcedureType == x[[1]] & tab2b_wide$all_pxs == x[[2]]]), na.rm = T)))) median(as.numeric(tab2b_wide[tab2b_wide$ProcedureType == list_tab2b[[1]][[1]] & tab2b_wide$all_pxs == list_tab2b[[1]][[2]], "los2"]), na.rm = T) tab2b3 <- bind_cols(tab2b_test, px_tab) tab2b4 <- bind_cols(tab2b3, px_tab2) %>% dplyr::rename(ProcedureType = Var1, PX = Var2)
Of the r n_distinct(tab2b$PERSON_ID) patients from the initial cohort, r n_distinct(tab2b2$PERSON_ID) had one or more valid procedure/CPT codes that could be used for the following table.
knitr::kable(tab2b4, col.names = c("Procedure Type", "Unique Combination of PXs", "Count", "Average LOS", "Median LOS"), digits = 2)
Table 2C: Social History Frequencies by Location
tab2c <- odbc::dbGetQuery(con, "SELECT DISTINCT z.ENC_ID, z.PERSON_ID, d.TOBACCO_USER, d.ALCOHOL_USER, d.ILL_DRUG_USER, d.CONDOM_YN, d.PILL_YN, d.DIAPHRAGM_YN, d.IUD_YN, d.SURGICAL_YN, d.SPERMICIDE_YN, d.IMPLANT_YN, d.INJECTION_YN, d.SPONGE_YN, d.BC_INSERTS_YN, d.CONTACT_DATE FROM DEMOGRAPHICS a INNER JOIN [PROCEDURES] z ON z.PERSON_ID = a.PERSON_ID LEFT JOIN [SOCIAL_HISTORY] d ON z.PERSON_ID = d.PERSON_ID AND z.ENC_ID = d.ENC_ID WHERE z.ADATE >= '01/01/2011' AND DATEDIFF(YY, a.BIRTH_DATE, z.ADATE) - CASE WHEN DATEADD(YY, DATEDIFF(YY, a.BIRTH_DATE, z.ADATE), a.BIRTH_DATE) > z.ADATE THEN 1 ELSE 0 END >= 10 AND DATEDIFF(YY, a.BIRTH_DATE, z.ADATE) - CASE WHEN DATEADD(YY, DATEDIFF(YY, a.BIRTH_DATE, z.ADATE), a.BIRTH_DATE) > z.ADATE THEN 1 ELSE 0 END <= 26 AND z.PX IN('0RG4070', '0RG4071', '0RG407J', '0RG40A0', '0RG40A1', '0RG40AJ', '0RG40J0', '0RG40J1', '0RG40JJ', '0RG40K0', '0RG40K1', '0RG40KJ', '0RG40Z0', '0RG40Z1', '0RG40ZJ', '0RG6070', '0RG6071', '0RG607J', '0RG60A0', '0RG60A1', '0RG60AJ', '0RG60J0', '0RG60J1', '0RG60JJ', '0RG60K0', '0RG60K1', '0RG60KJ', '0RG60Z0', '0RG60Z1', '0RG60ZJ', '0RG7070', '0RG7071', '0RG707J', '0RG70A0', '0RG70A1', '0RG70AJ', '0RG70J0', '0RG70J1', '0RG70JJ', '0RG70K0', '0RG70K1', '0RG70KJ', '0RG70Z0', '0RG70Z1', '0RG70ZJ', '0RG8070', '0RG8071', '0RG807J', '0RG80A0', '0RG80A1', '0RG80AJ', '0RG80J0', '0RG80J1', '0RG80JJ', '0RG80K0', '0RG80K1', '0RG80KJ', '0RG80Z0', '0RG80Z1', '0RG80ZJ', '0RGA070', '0RGA071', '0RGA07J', '0RGA0A0', '0RGA0A1', '0RGA0AJ', '0RGA0J0', '0RGA0J1', '0RGA0JJ', '0RGA0K0', '0RGA0K1', '0RGA0KJ', '0RGA0Z0', '0RGA0Z1', '0RGA0ZJ', '0SG0070', '0SG0071', '0SG007J', '0SG00A0', '0SG00A1', '0SG00AJ', '0SG00J0', '0SG00J1', '0SG00JJ', '0SG00K0', '0SG00K1', '0SG00KJ', '0SG00Z0', '0SG00Z1', '0SG00ZJ', '0SG1070', '0SG1071', '0SG107J', '0SG10A0', '0SG10A1', '0SG10AJ', '0SG10J0', '0SG10J1', '0SG10JJ', '0SG10K0', '0SG10K1', '0SG10KJ', '0SG10Z0', '0SG10Z1', '0SG10ZJ', '0SG3070', '0SG3071', '0SG307J', '0SG30A0', '0SG30A1', '0SG30AJ', '0SG30J0', '0SG30J1', '0SG30JJ', '0SG30K0', '0SG30K1', '0SG30KJ', '0SG30Z0', '0SG30Z1', '0SG30ZJ', '81', '81.01', '81.02', '81.03', '81.04', '81.05', '81.06', '81.07', '81.08', '81.09', '81.3', '81.35', '81.36', '81.37', '81.38', '81.39', '81.61', '81.62', '81.63', '81.64', '84.51', '0WU80JZ', '0WU84JZ', '34.74', '22532', '22533', '22548', '22551', '22554', '22556', '22558', '22586', '22590', '22595', '22600', '22610', '22612', '22614', '22630', '22633', '22634', '22800', '22802', '22804', '22808', '22810', '22812', '22818', '22819', '22830', '22840', '22841', '22842', '22843', '22844', '22845', '22846', '22847', '22848', '22849', '22850', '22852', '22853', '22854', '22855', '22856', '22857', '22858', '22859', '22861', '22862', '22864', '22865', '22867', '22868', '22869', '22870', '21740', '21742', '21743')" ) # Determine if date of social history assessment was during procedure encounter tab2c$visit <- ave(tab2c$PERSON_ID == tab2c$PERSON_ID, tab2c$PERSON_ID, FUN = cumsum) # Get count of how many didn't get asked smoking tab2c2 <- tab2c %>% left_join(., tab1d, by = c("PERSON_ID", "ENC_ID")) %>% group_by(PERSON_ID) %>% mutate( sh_dt = if_else(CONTACT_DATE >= ADATE & CONTACT_DATE <= DDATE, 1, 0), max_vis = max(visit, na.rm = T), use_vis = if_else(sh_dt == 1, 1, if_else(max_vis == 1, 1, 0)) ) %>% dplyr::filter(use_vis == 1) %>% mutate( ever_smoke = if_else(TOBACCO_USER == "Y", 1, 0), max_smoke = max(ever_smoke), use_smoke = if_else(ever_smoke == max_smoke, 1, 0), smoke_na = if_else(max_smoke != 1 & TOBACCO_USER == "X", 1, 0), max_smoke_na = max(smoke_na), bc_use = if_else(CONDOM_YN == "Y" | PILL_YN == "Y" | DIAPHRAGM_YN == "Y" | IUD_YN == "Y" | SURGICAL_YN == "Y" | SPERMICIDE_YN == "Y" | IMPLANT_YN == "Y" | INJECTION_YN == "Y" | SPONGE_YN == "Y" | BC_INSERTS_YN == "Y", 1, 0), max_bc = max(bc_use, na.rm = T), alc_use = if_else(ALCOHOL_USER == "Y", 1, 0), max_alc = max(alc_use, na.rm = T), drug_use = if_else(ILL_DRUG_USER == "Y", 1, 0) ) %>% dplyr::filter(use_smoke == 1 & TOBACCO_USER != "X") %>% dplyr::filter(!duplicated(PERSON_ID)) %>% ungroup(.) mean_los_sh <- c(mean(tab2c2$los2[tab2c2$max_smoke == 1], na.rm = T), mean(tab2c2$los2[tab2c2$max_smoke_na == 1], na.rm = T), mean(tab2c2$los2[tab2c2$max_alc == 1], na.rm = T), mean(tab2c2$los2[tab2c2$drug_use == 1], na.rm = T), mean(tab2c2$los2[tab2c2$max_bc == 1], na.rm = T)) median_los_sh <- c(median(tab2c2$los2[tab2c2$max_smoke == 1], na.rm = T), median(tab2c2$los2[tab2c2$max_smoke_na == 1], na.rm = T), median(tab2c2$los2[tab2c2$max_alc == 1], na.rm = T), median(tab2c2$los2[tab2c2$drug_use == 1], na.rm = T), median(tab2c2$los2[tab2c2$max_bc == 1], na.rm = T)) count_sh <- c(tally(tab2c2[tab2c2$max_smoke == 1, "los2"])[[1]], tally(tab2c2[tab2c2$max_smoke_na == 1, "los2"])[[1]], tally(tab2c2[tab2c2$max_alc == 1, "los2"])[[1]], tally(tab2c2[tab2c2$drug_use == 1, "los2"])[[1]], tally(tab2c2[tab2c2$max_bc == 1, "los2"])[[1]]) names_sh <- c("Tobacco Use", "Not asked about tobacco use", "Alcohol Use", "Illegal Drug Use", "Birth Control Use") tab2c_sum <- data.frame(names = names_sh, counts = count_sh, means = mean_los_sh, medians = median_los_sh)
Of the r n_distinct(tab2c$PERSON_ID) patients from the initial cohort, r n_distinct(tab2c2$PERSON_ID) had valid data from the SOCIAL_HISTORY table that was used to create the table below.
knitr::kable(tab2c_sum, col.names = c(paste0("Variables N = ", nrow(tab2c2)), "Count", "Mean LOS", "Median LOS"))
Data Tables Protocol: Table Set 3: Provider Information
Table 3A: Type of Surgeon/Physician of Record for Index
The Total values for both Spine and Pectus are for any age of patient.
# tab3a <- odbc::dbGetQuery(con, # "SELECT # DISTINCT z.ENC_ID, # z.PERSON_ID, # z.PX, # z.PROVIDER, # z.PERFORMINGPROVIDER, # d.SPECIALTY, # d.SPECIALTY2, # d.SPECIALTY3, # d.SPECIALTY4, # d.PROVIDER_TYPE, # MAX(CASE # WHEN z.PX IN('0RG4070', '0RG4071', '0RG407J', '0RG40A0', '0RG40A1', '0RG40AJ', '0RG40J0', '0RG40J1', '0RG40JJ', '0RG40K0', '0RG40K1', '0RG40KJ', '0RG40Z0', '0RG40Z1', '0RG40ZJ', '0RG6070', '0RG6071', '0RG607J', '0RG60A0', '0RG60A1', '0RG60AJ', '0RG60J0', '0RG60J1', '0RG60JJ', '0RG60K0', '0RG60K1', '0RG60KJ', '0RG60Z0', '0RG60Z1', '0RG60ZJ', '0RG7070', '0RG7071', '0RG707J', '0RG70A0', '0RG70A1', '0RG70AJ', '0RG70J0', '0RG70J1', '0RG70JJ', '0RG70K0', '0RG70K1', '0RG70KJ', '0RG70Z0', '0RG70Z1', '0RG70ZJ', '0RG8070', '0RG8071', '0RG807J', '0RG80A0', '0RG80A1', '0RG80AJ', '0RG80J0', '0RG80J1', '0RG80JJ', '0RG80K0', '0RG80K1', '0RG80KJ', '0RG80Z0', '0RG80Z1', '0RG80ZJ', '0RGA070', '0RGA071', '0RGA07J', '0RGA0A0', '0RGA0A1', '0RGA0AJ', '0RGA0J0', '0RGA0J1', '0RGA0JJ', '0RGA0K0', '0RGA0K1', '0RGA0KJ', '0RGA0Z0', '0RGA0Z1', '0RGA0ZJ', '0SG0070', '0SG0071', '0SG007J', '0SG00A0', '0SG00A1', '0SG00AJ', '0SG00J0', '0SG00J1', '0SG00JJ', '0SG00K0', '0SG00K1', '0SG00KJ', '0SG00Z0', '0SG00Z1', '0SG00ZJ', '0SG1070', '0SG1071', '0SG107J', '0SG10A0', '0SG10A1', '0SG10AJ', '0SG10J0', '0SG10J1', '0SG10JJ', '0SG10K0', '0SG10K1', '0SG10KJ', '0SG10Z0', '0SG10Z1', '0SG10ZJ', '0SG3070', '0SG3071', '0SG307J', '0SG30A0', '0SG30A1', '0SG30AJ', '0SG30J0', '0SG30J1', '0SG30JJ', '0SG30K0', '0SG30K1', '0SG30KJ', '0SG30Z0', '0SG30Z1', '0SG30ZJ', '81', '81.01', '81.02', '81.03', '81.04', '81.05', '81.06', '81.07', '81.08', '81.09', '81.3', '81.35', '81.36', '81.37', '81.38', '81.39', '81.61', '81.62', '81.63', '81.64', '84.51', '22532', '22533', '22548', '22551', '22554', '22556', '22558', '22586', '22590', '22595', '22600', '22610', '22612', '22614', '22630', '22633', '22634', '22800', '22802', '22804', '22808', '22810', '22812', '22818', '22819', '22830', '22840', '22841', '22842', '22843', '22844', '22845', '22846', '22847', '22848', '22849', '22850', '22852', '22853', '22854', '22855', '22856', '22857', '22858', '22859', '22861', '22862', '22864', '22865', '22867', '22868', '22869', '22870') # THEN 1 ELSE 0 END) AS spine, # MAX(CASE # WHEN z.PX IN('0WU80JZ', '0WU84JZ', '34.74', '21740', '21742', '21743') # THEN 1 ELSE 0 END) AS pectus, # z.PROCDATE, # YEAR(z.ADATE) as px_date # FROM # DEMOGRAPHICS a # INNER JOIN [PROCEDURES] z # ON z.PERSON_ID = a.PERSON_ID # LEFT JOIN [PROVIDER_SPECIALTY] d # ON z.PERFORMINGPROVIDER = d.PROVIDER # WHERE z.ADATE >= '01/01/2011' # AND DATEDIFF(YY, a.BIRTH_DATE, z.ADATE) - CASE # WHEN DATEADD(YY, DATEDIFF(YY, a.BIRTH_DATE, z.ADATE), a.BIRTH_DATE) > z.ADATE # THEN 1 # ELSE 0 # END >= 10 # AND DATEDIFF(YY, a.BIRTH_DATE, z.ADATE) - CASE # WHEN DATEADD(YY, DATEDIFF(YY, a.BIRTH_DATE, z.ADATE), a.BIRTH_DATE) > z.ADATE # THEN 1 # ELSE 0 # END <= 26 # AND # z.PX IN('0RG4070', '0RG4071', '0RG407J', '0RG40A0', '0RG40A1', '0RG40AJ', '0RG40J0', '0RG40J1', '0RG40JJ', '0RG40K0', '0RG40K1', '0RG40KJ', '0RG40Z0', '0RG40Z1', '0RG40ZJ', '0RG6070', '0RG6071', '0RG607J', '0RG60A0', '0RG60A1', '0RG60AJ', '0RG60J0', '0RG60J1', '0RG60JJ', '0RG60K0', '0RG60K1', '0RG60KJ', '0RG60Z0', '0RG60Z1', '0RG60ZJ', '0RG7070', '0RG7071', '0RG707J', '0RG70A0', '0RG70A1', '0RG70AJ', '0RG70J0', '0RG70J1', '0RG70JJ', '0RG70K0', '0RG70K1', '0RG70KJ', '0RG70Z0', '0RG70Z1', '0RG70ZJ', '0RG8070', '0RG8071', '0RG807J', '0RG80A0', '0RG80A1', '0RG80AJ', '0RG80J0', '0RG80J1', '0RG80JJ', '0RG80K0', '0RG80K1', '0RG80KJ', '0RG80Z0', '0RG80Z1', '0RG80ZJ', '0RGA070', '0RGA071', '0RGA07J', '0RGA0A0', '0RGA0A1', '0RGA0AJ', '0RGA0J0', '0RGA0J1', '0RGA0JJ', '0RGA0K0', '0RGA0K1', '0RGA0KJ', '0RGA0Z0', '0RGA0Z1', '0RGA0ZJ', '0SG0070', '0SG0071', '0SG007J', '0SG00A0', '0SG00A1', '0SG00AJ', '0SG00J0', '0SG00J1', '0SG00JJ', '0SG00K0', '0SG00K1', '0SG00KJ', '0SG00Z0', '0SG00Z1', '0SG00ZJ', '0SG1070', '0SG1071', '0SG107J', '0SG10A0', '0SG10A1', '0SG10AJ', '0SG10J0', '0SG10J1', '0SG10JJ', '0SG10K0', '0SG10K1', '0SG10KJ', '0SG10Z0', '0SG10Z1', '0SG10ZJ', '0SG3070', '0SG3071', '0SG307J', '0SG30A0', '0SG30A1', '0SG30AJ', '0SG30J0', '0SG30J1', '0SG30JJ', '0SG30K0', '0SG30K1', '0SG30KJ', '0SG30Z0', '0SG30Z1', '0SG30ZJ', '81', '81.01', '81.02', '81.03', '81.04', '81.05', '81.06', '81.07', '81.08', '81.09', '81.3', '81.35', '81.36', '81.37', '81.38', '81.39', '81.61', '81.62', '81.63', '81.64', '84.51', '0WU80JZ', '0WU84JZ', '34.74', '22532', '22533', '22548', '22551', '22554', '22556', '22558', '22586', '22590', '22595', '22600', '22610', '22612', '22614', '22630', '22633', '22634', '22800', '22802', '22804', '22808', '22810', '22812', '22818', '22819', '22830', '22840', '22841', '22842', '22843', '22844', '22845', '22846', '22847', '22848', '22849', '22850', '22852', '22853', '22854', '22855', '22856', '22857', '22858', '22859', '22861', '22862', '22864', '22865', '22867', '22868', '22869', '22870', '21740', '21742', '21743') # GROUP BY z.PERSON_ID, z.ENC_ID, z.PX, z.PROVIDER, # z.PERFORMINGPROVIDER, # d.SPECIALTY, # d.SPECIALTY2, # d.SPECIALTY3, # d.SPECIALTY4, # d.PROVIDER_TYPE, z.PROCDATE, YEAR(z.ADATE)" # ) # # # tab3a2 <- tab3a %>% # left_join(., specialties, by = "SPECIALTY") %>% # dplyr::filter(PROVIDER_TYPE %in% c(61, 25, 77, 999)) %>% # Filter to only include physicians, residents, fellows and unknowns # dplyr::filter(ENC_ID %in% tab1b2$ENC_ID) %>% #Only include valid encounter ids # dplyr::filter(!duplicated(ENC_ID)) # Deduplicate encounter ID # Removed PX and procdate from below to get tab3a_prov <- odbc::dbGetQuery(con, "SELECT z.ENC_ID, z.PERSON_ID, z.PROVIDER, z.PERFORMINGPROVIDER, d.SPECIALTY, d.SPECIALTY2, d.SPECIALTY3, d.SPECIALTY4, d.PROVIDER_TYPE, d.PROVIDER_BIRTH_YEAR, d.YEAR_GRADUATED, d.PROVIDER_RACE, d.PROVIDER_HISPANIC, MAX(CASE WHEN z.PX IN('0RG4070', '0RG4071', '0RG407J', '0RG40A0', '0RG40A1', '0RG40AJ', '0RG40J0', '0RG40J1', '0RG40JJ', '0RG40K0', '0RG40K1', '0RG40KJ', '0RG40Z0', '0RG40Z1', '0RG40ZJ', '0RG6070', '0RG6071', '0RG607J', '0RG60A0', '0RG60A1', '0RG60AJ', '0RG60J0', '0RG60J1', '0RG60JJ', '0RG60K0', '0RG60K1', '0RG60KJ', '0RG60Z0', '0RG60Z1', '0RG60ZJ', '0RG7070', '0RG7071', '0RG707J', '0RG70A0', '0RG70A1', '0RG70AJ', '0RG70J0', '0RG70J1', '0RG70JJ', '0RG70K0', '0RG70K1', '0RG70KJ', '0RG70Z0', '0RG70Z1', '0RG70ZJ', '0RG8070', '0RG8071', '0RG807J', '0RG80A0', '0RG80A1', '0RG80AJ', '0RG80J0', '0RG80J1', '0RG80JJ', '0RG80K0', '0RG80K1', '0RG80KJ', '0RG80Z0', '0RG80Z1', '0RG80ZJ', '0RGA070', '0RGA071', '0RGA07J', '0RGA0A0', '0RGA0A1', '0RGA0AJ', '0RGA0J0', '0RGA0J1', '0RGA0JJ', '0RGA0K0', '0RGA0K1', '0RGA0KJ', '0RGA0Z0', '0RGA0Z1', '0RGA0ZJ', '0SG0070', '0SG0071', '0SG007J', '0SG00A0', '0SG00A1', '0SG00AJ', '0SG00J0', '0SG00J1', '0SG00JJ', '0SG00K0', '0SG00K1', '0SG00KJ', '0SG00Z0', '0SG00Z1', '0SG00ZJ', '0SG1070', '0SG1071', '0SG107J', '0SG10A0', '0SG10A1', '0SG10AJ', '0SG10J0', '0SG10J1', '0SG10JJ', '0SG10K0', '0SG10K1', '0SG10KJ', '0SG10Z0', '0SG10Z1', '0SG10ZJ', '0SG3070', '0SG3071', '0SG307J', '0SG30A0', '0SG30A1', '0SG30AJ', '0SG30J0', '0SG30J1', '0SG30JJ', '0SG30K0', '0SG30K1', '0SG30KJ', '0SG30Z0', '0SG30Z1', '0SG30ZJ', '81', '81.01', '81.02', '81.03', '81.04', '81.05', '81.06', '81.07', '81.08', '81.09', '81.3', '81.35', '81.36', '81.37', '81.38', '81.39', '81.61', '81.62', '81.63', '81.64', '84.51', '22532', '22533', '22548', '22551', '22554', '22556', '22558', '22586', '22590', '22595', '22600', '22610', '22612', '22614', '22630', '22633', '22634', '22800', '22802', '22804', '22808', '22810', '22812', '22818', '22819', '22830', '22840', '22841', '22842', '22843', '22844', '22845', '22846', '22847', '22848', '22849', '22850', '22852', '22853', '22854', '22855', '22856', '22857', '22858', '22859', '22861', '22862', '22864', '22865', '22867', '22868', '22869', '22870') THEN 1 ELSE 0 END) AS spine, MAX(CASE WHEN z.PX IN('0WU80JZ', '0WU84JZ', '34.74', '21740', '21742', '21743') THEN 1 ELSE 0 END) AS pectus, YEAR(z.ADATE) as px_date FROM PROCEDURES z LEFT JOIN [PROVIDER_SPECIALTY] d ON z.PERFORMINGPROVIDER = d.PROVIDER WHERE z.ADATE >= '01/01/2011' AND z.PERFORMINGPROVIDER IN('E_3465', 'E_13066', 'E_13066', 'E_13066', '088377', 'E_13066', 'E_13066', 'E_14465', 'E_1526', 'E_1526', 'E_4104', 'E_5277', 'E_1322', 'E_13066', 'E_1322', 'E_13066', '116038', '158352', '145318', '145466', '123364', '130666', '162974', '169649', '125401', '086330', 'E_1322', '132274', 'E_13066', '113399', '073361', '116038', '123364', '133595', '144576', '145318', '140848', '125401', '144576', 'E_13066', '125401', '124586', 'E_13066', '073361', '128850', '124453', '158352', '100305', '141531', '162974', '132274', '046771', 'E_13066', 'E_4046', '133322', '125401', '141531', '070979', '135863', '123364', '124453', '158378', '131375', '133595', '133322', '086330', 'E_1526', '133322', '156604', 'E_13066', '088377', '162974', '141531', '079038', '077321', '157230', '100305', '100305', '141440', '133322', '073411', '133595', '128850', '156604', 'E_3844', '144576', '128850', 'E_1526', '123364', '007047', '158378', '124453', '141481', '135863', '156604', '141465', '124453', '124453', '128850', '146787', '119578', '147918', '124586', 'E_3661', '086330', '153767', '110676', '111153') AND z.PX IN('0RG4070', '0RG4071', '0RG407J', '0RG40A0', '0RG40A1', '0RG40AJ', '0RG40J0', '0RG40J1', '0RG40JJ', '0RG40K0', '0RG40K1', '0RG40KJ', '0RG40Z0', '0RG40Z1', '0RG40ZJ', '0RG6070', '0RG6071', '0RG607J', '0RG60A0', '0RG60A1', '0RG60AJ', '0RG60J0', '0RG60J1', '0RG60JJ', '0RG60K0', '0RG60K1', '0RG60KJ', '0RG60Z0', '0RG60Z1', '0RG60ZJ', '0RG7070', '0RG7071', '0RG707J', '0RG70A0', '0RG70A1', '0RG70AJ', '0RG70J0', '0RG70J1', '0RG70JJ', '0RG70K0', '0RG70K1', '0RG70KJ', '0RG70Z0', '0RG70Z1', '0RG70ZJ', '0RG8070', '0RG8071', '0RG807J', '0RG80A0', '0RG80A1', '0RG80AJ', '0RG80J0', '0RG80J1', '0RG80JJ', '0RG80K0', '0RG80K1', '0RG80KJ', '0RG80Z0', '0RG80Z1', '0RG80ZJ', '0RGA070', '0RGA071', '0RGA07J', '0RGA0A0', '0RGA0A1', '0RGA0AJ', '0RGA0J0', '0RGA0J1', '0RGA0JJ', '0RGA0K0', '0RGA0K1', '0RGA0KJ', '0RGA0Z0', '0RGA0Z1', '0RGA0ZJ', '0SG0070', '0SG0071', '0SG007J', '0SG00A0', '0SG00A1', '0SG00AJ', '0SG00J0', '0SG00J1', '0SG00JJ', '0SG00K0', '0SG00K1', '0SG00KJ', '0SG00Z0', '0SG00Z1', '0SG00ZJ', '0SG1070', '0SG1071', '0SG107J', '0SG10A0', '0SG10A1', '0SG10AJ', '0SG10J0', '0SG10J1', '0SG10JJ', '0SG10K0', '0SG10K1', '0SG10KJ', '0SG10Z0', '0SG10Z1', '0SG10ZJ', '0SG3070', '0SG3071', '0SG307J', '0SG30A0', '0SG30A1', '0SG30AJ', '0SG30J0', '0SG30J1', '0SG30JJ', '0SG30K0', '0SG30K1', '0SG30KJ', '0SG30Z0', '0SG30Z1', '0SG30ZJ', '81', '81.01', '81.02', '81.03', '81.04', '81.05', '81.06', '81.07', '81.08', '81.09', '81.3', '81.35', '81.36', '81.37', '81.38', '81.39', '81.61', '81.62', '81.63', '81.64', '84.51', '0WU80JZ', '0WU84JZ', '34.74', '22532', '22533', '22548', '22551', '22554', '22556', '22558', '22586', '22590', '22595', '22600', '22610', '22612', '22614', '22630', '22633', '22634', '22800', '22802', '22804', '22808', '22810', '22812', '22818', '22819', '22830', '22840', '22841', '22842', '22843', '22844', '22845', '22846', '22847', '22848', '22849', '22850', '22852', '22853', '22854', '22855', '22856', '22857', '22858', '22859', '22861', '22862', '22864', '22865', '22867', '22868', '22869', '22870', '21740', '21742', '21743') GROUP BY z.ENC_ID, z.PERSON_ID, z.PROVIDER, z.PERFORMINGPROVIDER, d.SPECIALTY, d.SPECIALTY2, d.SPECIALTY3, d.SPECIALTY4, d.PROVIDER_TYPE, d.PROVIDER_BIRTH_YEAR, d.YEAR_GRADUATED, d.PROVIDER_RACE, d.PROVIDER_HISPANIC, YEAR(z.ADATE)" ) tab3a2_prov <- tab3a_prov %>% left_join(., specialties, by = "SPECIALTY") %>% dplyr::filter(!duplicated(ENC_ID)) %>% group_by(Description, px_date) %>% mutate( cohort_s = if_else(ENC_ID %in% tab1b2$ENC_ID[tab1b2$spine == 1], 1, 0), cohort_p = if_else(ENC_ID %in% tab1b2$ENC_ID[tab1b2$pectus == 1], 1, 0) ) %>% summarise(n_cohort_s = sum(cohort_s), spine = sum(spine), n_cohort_p = sum(cohort_p), pectus = sum(pectus)) %>% dplyr::select(px_date, Description, n_cohort_s, spine, n_cohort_p, pectus)
This table contains information only for physicians, not fellows, residents, or other providers. Of the r n_distinct(tab1b2$PERSON_ID) total patients, r n_distinct(tab3a_prov$PERSON_ID) had provider data that was used to create the table below.
knitr::kable(tab3a2_prov, col.names = c("Year", "Provider Specialty", "Cohort Spine PXs", "Total Spine Procedures", "Cohort Pectus PXs", "Total Pectus Procedures"))
Table Pack 3B: Surgeon Demographics per Facility
The Counts for both Spine and Pectus are for any age of patient.
tab3a_prov$n_procs <- ave(tab3a_prov$PERFORMINGPROVIDER == tab3a_prov$PERFORMINGPROVIDER, tab3a_prov$PERFORMINGPROVIDER, FUN = cumsum) prov_type$PROVIDER_TYPE <- as.character(prov_type$PROVIDER_TYPE) tab3b <- tab3a_prov %>% left_join(., specialties, by = "SPECIALTY") %>% left_join(., prov_type, by = "PROVIDER_TYPE") %>% group_by(PERFORMINGPROVIDER) %>% mutate( sum_spine = sum(spine), sum_pectus = sum(pectus), max_proc = max(n_procs, na.rm = T), cohort_spine = if_else(ENC_ID %in% tab1b2$ENC_ID[tab1b2$spine == 1], 1, 0), cohort_pectus = if_else(ENC_ID %in% tab1b2$ENC_ID[tab1b2$pectus == 1], 1, 0), sum_cohort_s = sum(cohort_spine), sum_cohort_p = sum(cohort_pectus), p_race = dplyr::recode(PROVIDER_RACE, HP = "Native Hawaiian or Other Pacific Islander", IN = "American Indian/Alaska Native", AS = "Asian", BA = "Black or African American", WH = "White", MU = "More than one race, particular races unknown or not reported", OT= "Other", UN = "Unknown or Not Reported"), p_hisp = dplyr::recode(PROVIDER_HISPANIC, Y = "Yes", N = "No", U = "Unknown") ) %>% dplyr::filter(!duplicated(PERFORMINGPROVIDER)) %>% ungroup(.) %>% select(PERFORMINGPROVIDER, sum_cohort_s, sum_spine, sum_cohort_p, sum_pectus, prov_desc, Description, PROVIDER_BIRTH_YEAR, p_race, p_hisp, YEAR_GRADUATED, max_proc)
knitr::kable(tab3b, col.names = c("Physician ID", "Num Cohort S", "Num Spine PXs", "Num Cohort P", "Num Pectus PXs", "Provider Type", "Phys Specialty", "Phys Birth Year", "Phys Year Graduated", "Phys Race", "Phys Ethnicity", "Total Volume"))
Data Tables Protocol: Table Set 4: Rx Information
Table 4A: Basic RX Information on Patient Discharge post Index Procedure CHCO
(Percent total of inclusion population – procedure code + inclusion criteria.)
R Package Documentation
Browse R Packages
We want your feedback!
Note that we can't provide technical support on individual packages. You should contact the package authors for that.
Embedding an R snippet on your website
Add the following code to your website.
For more information on customizing the embed code, read Embedding Snippets.