-
Notifications
You must be signed in to change notification settings - Fork 1
/
Emergency admissions in 65 plus.R
225 lines (168 loc) · 9.72 KB
/
Emergency admissions in 65 plus.R
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
# ScotPHO indicators: count of emergency admissions in those aged 65>= years (count of CIS).
# New indicator for ScotPHO profiles tool introduced in sep 2023
# The data generated by this script produces file for ScotPHO profiles tool AND and extract which can be sent
# to the IMPROVEMENT SERVICE - as it is an indicator present in their CPOP (communnity planning and outcomes )
# Part 1 - Extract data from SMRA
# Part 2 - Create the different geographies basefiles
# Part 3 - Run analysis functions
# In the past IS received data from PHS secondary care team but in 2023 scotpho picked up as regular request
# supply data with an IR number to ensure data can be traced back to code used to generate
# \\Isdsf00d03\cl-out\IR2020\IR2022-00429
# \\Isdsf00d03\cl-out\IR2023\IR2023-00713
# \\Isdsf00d03\cl-out\IR2024\IR2024-00602
###############################################.
## Packages/Filepaths/Functions ----
###############################################.
source("1.indicator_analysis.R") #Normal indicator functions
source("2.deprivation_analysis.R") # deprivation function
# additional packages
library(lubridate)
###############################################.
## Part 1 - Extract data from SMRA ----
###############################################.
# SMRA login information
channel <- suppressWarnings(dbConnect(odbc(), dsn="SMRA",
uid=.rs.askForPassword("SMRA Username:"),
pwd=.rs.askForPassword("SMRA Password:")))
#time how long extraction takes to run
start <- Sys.time()
#ensure that fields are sorted in correct order wduring SQL extract
sort_var <- "link_no, admission_date, discharge_date, admission, discharge, uri"
# only CIS where emergency admission is in the first episode of CIS
# includes all emergency daycase - note that Secondary Care team annual publication estimates for emergency admissions exclude emergency daycases (unless the daycase CIS includes one or more cis where admission type is inpatient)
# includes CIS which ending in the FYE of interest -
# takes postcode, sex and age on admission
emergency_cis <- as_tibble(dbGetQuery(channel, statement=paste0(
"SELECT *
from (
SELECT
link_no, cis_marker,
row_number() OVER (PARTITION BY link_no, cis_marker ORDER BY ", sort_var, ") rn,
min(admission_date) OVER (PARTITION BY link_no, cis_marker) dadmit,
max(discharge_date) OVER (PARTITION BY link_no, cis_marker) ddisch,
min(AGE_IN_YEARS) OVER (PARTITION BY link_no, cis_marker) age,
FIRST_VALUE(sex) OVER (PARTITION BY link_no, cis_marker ORDER BY ", sort_var, ") sex_grp,
FIRST_VALUE(dr_postcode) OVER (PARTITION BY link_no, cis_marker ORDER BY ", sort_var, ") pc7,
FIRST_VALUE(admission_type) OVER (PARTITION BY link_no, cis_marker ORDER BY ", sort_var, ") adm_type
FROM ANALYSIS.SMR01_PI z)
WHERE rn = 1
AND age > 64
AND sex_grp not in ('9', '0')
AND ddisch BETWEEN '1 April 2002' and '31 MARCH 2023'
AND (adm_type between '20' and '22' or adm_type between '30' and '39')
ORDER BY link_no, cis_marker"))) %>%
setNames(tolower(names(.))) #variables to lower case
### Calculate time taken in minutes
end <- Sys.time()
end - start # 14minutes to extract 2002 to 2022 data (~4.5 million rows)
emergency_cis <- emergency_cis %>%
create_agegroups() %>% # needed for standardisation
mutate(staymonth=month(ddisch),
year = case_when(staymonth >3 ~ year(ddisch), staymonth <= 3 ~ year(ddisch)-1, TRUE ~ 0))
# open lookup that will allow attachment of postcode to datazone2011.
postcode_lookup <- read_rds('/conf/linkage/output/lookups/Unicode/Geography/Scottish Postcode Directory/Scottish_Postcode_Directory_2024_1.rds') %>%
setNames(tolower(names(.))) %>% #variables to lower case
select (pc7, datazone2011, datazone2001)
# Match geography information (datazone) to emergency admission data
data_ea65 <- left_join(emergency_cis , postcode_lookup, "pc7")
# aggregate to give total emergency admissions by datazone for only scottish residents
data_ea65 <-data_ea65 %>%
subset(!(is.na(datazone2011))) %>% #select out non-scottish resident
group_by(year, age_grp, sex_grp, datazone2011, datazone2001) %>%
summarise(numerator=n()) %>%
ungroup()
saveRDS(data_ea65, paste0(data_folder, 'Prepared Data/smr01_emergency65_basefile.rds'))
rm(emergency_cis) #tidy large file
######################################################################################.
## Part 2 - Prepare extract for Improvement Service ----
## ScotPHO have agreement to provide Improvement service with annual figures
## IS use crude rates rather than standardised rates
## Once data checks complete send to NICK CASSIDY (nicholas.cassidy@improvementservice.org.uk)
######################################################################################.
# Reading file
data_ea65<- readRDS(paste0(data_folder, 'Prepared Data/smr01_emergency65_basefile.rds'))
# prepare 2011 datazone population denominator file (aged 65 and over)
# annual populations from 2009 to latest
dz11_populations <- read_csv("https://www.opendata.nhs.scot/dataset/7f010430-6ce1-4813-b25c-f7f335bdc4dc/resource/c505f490-c201-44bd-abd1-1bd7a64285ee/download/dz2011-pop-est_07092021.csv") %>%
setNames(tolower(names(.))) %>% #variables to lower case
filter(year >= 2002 & substr(datazone, 1, 3) != "S92" & sex != "All") %>% #years and no Scotland
mutate(sex = recode(sex, "Male" = 1, "Female" = 2, "f" = 2, "m" = 1)) %>%
select(year, sex, datazone, age0:age90plus) %>%
gather(age, pop, -c(year, sex, datazone)) %>% #wide to long format
mutate(age = as.numeric(gsub("age|plus", "", age))) %>% #re-coding age variable
filter(age>=65) %>%
group_by(year, datazone) %>%
summarise(pop=sum(pop)) %>%
ungroup() %>%
rename(datazone2011=datazone)
# temporary solution for delays in release of 2022 SAPE - reapply 2021 population to the 2022 data
dz11_pop_2022 <-dz11_populations %>%
filter (year==2021) %>%
mutate (year=2022)
dz11_populations <- rbind(dz11_populations,dz11_pop_2022)
# open ScotPHO geography look-up that enables matching datazones to all parent geographies
# (there should be no duplicate dz to parent matches ie. datazones dont map to more than one NHS board/CA)
geo_lookup <- readRDS(paste0(lookups, "Geography/DataZone11_All_Geographies_Lookup.rds"))
# Improvement service use crude rates so no need to split data by age and sex
emergency_admissions_forIS <-data_ea65 %>%
group_by(year, datazone2011) %>%
summarise(emergency_cis=sum(numerator)) %>%
ungroup()
# Match on emergency admissions data to populations
emergency_admissions_forIS <- full_join(x = dz11_populations, y = emergency_admissions_forIS,
by = c("year", "datazone2011"))
# Match on parent geogrpahies
emergency_admissions_forIS <- left_join(emergency_admissions_forIS , geo_lookup, "datazone2011") %>%
select(-adp, -hb2019, -hscp2019,-hscp_locality) %>%
mutate(scotland = as.factor("S00000001"))
# Gather data into long format so all geographies are stacked on top of each other & calculate crude rate
emergency_admissions_forIS <- emergency_admissions_forIS %>%
gather(geolevel, code, datazone2011, intzone2011:scotland) %>%
ungroup() %>%
select(-c(geolevel)) %>%
group_by(code, year) %>%
summarise_all(sum, na.rm =T) %>%
ungroup() %>%
mutate(crate=(emergency_cis/pop)*100000,
crate= ifelse(is.infinite(crate), NA, crate), ## Converting Infinites to NA and NA's to 0s to allow proper functioning
year=paste0(year,"/",year+1))
# Save out CSV file that can be sent to Improvement Service
# log informatin request and s
write_csv(emergency_admissions_forIS, file = paste0(data_folder, "Data to be checked/ScotPHO ImprovementService_Emergency_Admissions_65.csv"))
rm(emergency_admissions_forIS) #tidy large file
######################################################################################.
## Part 3 - Prepare basefiles for ScotPHO profiles indicator ----
##
######################################################################################.
# Reading file
data_ea65<- readRDS(paste0(data_folder, 'Prepared Data/smr01_emergency65_basefile.rds'))
# Datazone2011
dz11 <- data_ea65 %>%
group_by(year, datazone2011, sex_grp, age_grp) %>%
summarize(numerator = n()) %>% ungroup() %>% rename(datazone = datazone2011)
saveRDS(dz11, file=paste0(data_folder, 'Prepared Data/emergency_stays65_dz11_raw.rds'))
###############################################.
#Deprivation basefile
# DZ 2001 data needed up to 2013 to enable matching to advised SIMD
dz01_dep <- data_ea65 %>%
group_by(year, datazone2001, sex_grp, age_grp) %>%
summarize(numerator = n()) %>% ungroup() %>% rename(datazone = datazone2001) %>%
subset(year<=2013)
dep_file <- rbind(dz01_dep, dz11 %>% subset(year>=2014)) #joining dz01 and dz11
saveRDS(dep_file, file=paste0(data_folder, 'Prepared Data/emergency_stays65_depr_raw.rds'))
###############################################.
## Part 3 - Run analysis functions ----
###############################################.
###############################################.
# Emergency hospital stays in >=65years
analyze_first(filename = "emergency_stays65_dz11", geography = "datazone11", measure = "stdrate",
pop = "DZ11_pop_65+", yearstart = 2002, yearend = 2022,
time_agg = 3, epop_age = "normal")
analyze_second(filename = "emergency_stays65_dz11", measure = "stdrate", time_agg = 3,
epop_total = 39000, ind_id = 99103, year_type = "financial")
#Deprivation analysis function (runs against admissions all ages)
analyze_deprivation(filename="emergency_stays65_depr", measure="stdrate", time_agg=3,
yearstart= 2002, yearend=2022,year_type = "financial",
pop = "depr_pop_65+", epop_age="normal",
epop_total =39000, ind_id = 99103)
##END