In Moohan/slfhelper: Useful functions for working with the Source Linkage Files

knitr::opts_chunk$set(
  collapse = TRUE,
  comment = "#>"
)

library(slfhelper)

Examples using SLFhelper

1. A&E attendances in East Lothian by age group.

Produce a table to compare A&E Attendances for the following age groups (0-17, 18-64, 65-74, 75-84, 85+) for 2018/19 in East Lothian HSCP.

# read in data required from slf individual file - filter for year 2018/19
el_1819 <- read_slf_individual(
  year = "1819",
  # select variables needed
  col_select = c("age", "ae_attendances"),
  # filter partnership for East Lothian
  partnerships = "S37000010"
)

# create age bands
age_labs <- c("0-17", "18-64", "65-74", "75-84", "85+") # create age labels

# create age group variable
el_1819 <- el_1819 %>%
  mutate(age_group = cut(age,
    breaks = c(-1, 17, 64, 74, 84, 150), labels = age_labs
  ))

# produce summary table
output_table_1 <- el_1819 %>%
  group_by(age_group) %>%
  summarise(attendances = sum(ae_attendances)) %>%
  ungroup()

1. Outpatient attendances by specialty and gender.

Create a table to compare the number of outpatient attendances (SMR00) broken down by specialty and gender in 2017/18 in Scotland.

# read in specialty lookup with names
spec_lookup <-
  read_csv("/conf/linkage/output/lookups/Unicode/National Reference Files/Specialty.csv") %>%
  select(
    spec = Speccode,
    spec_name = Description
  )

# read in data required from slf episode file - filter year = 2017/18
op_1718 <- read_slf_episode(
  year = "1718",
  # select columns
  col_select = c("recid", "gender", "spec"),
  # filter on recid for outpatients
  recids = "00B"
)

# produce output
output_table_2 <- op_1718 %>%
  # get counts by specialty and gender
  count(spec, gender) %>%
  # exclude those with no gender recorded
  filter(gender == 1 | gender == 2) %>%
  # recode gender into M/F
  mutate(gender = recode(as.character(gender), "1" = "Male", "2" = "Female")) %>%
  # move gender to separate columns
  pivot_wider(names_from = gender, values_from = n) %>%
  # match on specialty names
  left_join(spec_lookup) %>%
  # reorder variables
  select(spec, spec_name, Male, Female)

1. Hospital admissions & beddays by HB of residence.

Produce a table to compare the number of admissions, bed days and average length of stay (split into elective and non-elective) by Health Board of Residence in 2018/19.

# Read in names for Health Boards
hb_lookup <-
  read_csv("/conf/linkage/output/lookups/Unicode/Geography/Scottish Postcode Directory/Codes and Names/Health Board Area 2019 Lookup.csv") %>%
  select(
    hb2019 = HealthBoardArea2019Code,
    hb_desc = HealthBoardArea2019Name
  )

# read in data required from slf individual file - filter for 2018/19
indiv_1819 <- read_slf_individual(
  year = "1819",
  # Select columns of interest
  col_select = c(
    "hb2019", "cij_el", "cij_non_el",
    "acute_el_inpatient_beddays",
    "mh_el_inpatient_beddays",
    "gls_el_inpatient_beddays",
    "acute_non_el_inpatient_beddays",
    "mh_non_el_inpatient_beddays",
    "gls_non_el_inpatient_beddays"
  )
)


# calculate total bed days and add on HB names
indiv_1819_inc_totals <- indiv_1819 %>%
  # calculate overall bed days
  mutate(
    elective_beddays = acute_el_inpatient_beddays + mh_el_inpatient_beddays +
      gls_el_inpatient_beddays,
    non_elective_beddays = acute_non_el_inpatient_beddays + mh_non_el_inpatient_beddays +
      gls_non_el_inpatient_beddays
  ) %>%
  # match on HB name
  left_join(hb_lookup)

# produce summary table
output_table_3 <- indiv_1819_inc_totals %>%
  # group by HB of residence
  group_by(hb2019, hb_desc) %>%
  # produce summary table
  summarise(
    elective_adm = sum(cij_el),
    non_elective_adm = sum(cij_non_el),
    elective_beddays = sum(elective_beddays),
    non_elective_beddays = sum(non_elective_beddays)
  ) %>%
  # calculate average length of stay
  mutate(
    elective_alos = elective_beddays / elective_adm,
    non_elective_alos = non_elective_beddays / non_elective_adm
  )

1. GP Out of Hours Consulations in South Ayrshire.

Create a table showing the number of GP Out of Hours consultations for patients with dementia in South Ayrshire HSCP in 2019/20 broken down by type of consultation.

# read in data required from slf episode file - filter for year = 2019/20
sa_1920 <- read_slf_episode(
  year = "1920",
  # select columns
  col_select = c("dementia", "smrtype"),
  # filter for South Ayrshire HSCP
  partnerships = "S37000027",
  # Filter for GP OOH data
  recids = "OoH"
)

# select dementia patients
sa_dementia_1920 <- sa_1920 %>%
  filter(dementia == 1)

# produce summary table
output_table_4 <- sa_dementia_1920 %>%
  count(smrtype)

1. Costs in Aberdeen City.

Produce a table to show the number of patients and the total costs for Aberdeen City HSCP in 2018/19. Include a breakdown of costs for the following services: Acute (inpatients & daycases), GLS, Mental Health and Maternity, Outpatients, A&E, GP Out of Hours, Community Prescribing.

# read in data required from slf individual file - filter year = 2018/19
ab_1819 <- read_slf_individual(
  year = "1819",
  # select columns
  col_select = c(
    "acute_cost", "gls_cost", "mh_cost", "mat_cost",
    "op_cost_attend", "ae_cost", "ooh_cost", "pis_cost",
    "health_net_cost"
  ),
  # filter for Aberdeen City
  partnerships = "S37000001"
)

# Have used variables which exclude the cost of outpatient attendances which did
# not attend (DNA) but you could also include this if needed.

# produce summary table
output_table_5 <- ab_1819 %>%
  # rename outatients variable
  rename(op_cost = op_cost_attend) %>%
  # sum of all cost variables and number of patients
  summarise(across(ends_with("_cost"), ~ sum(.x, na.rm = TRUE)),
    patients = n()
  ) %>%
  # switch to rows
  pivot_longer(everything())

1. Deaths from Dementia / Alzheimers

Produce a chart to show the number of deaths from 2015/16 to 2019/20 in Scotland where the main cause of death was recorded as Dementia/Alzheimers (ICD 10 codes: G30, F01-F03, F05.1).

# read in data required from slf episode file - filter for years 2015/16 to 2019/20
deaths <- read_slf_episode(
  year = c("1516", "1617", "1718", "1819", "1920"),
  # select columns
  col_select = c("year", "deathdiag1"),
  # Filter for death records
  recids = "NRS"
)

# extract 3 & 4 digit codes and select those with dementia
dementia_deaths <- deaths %>%
  # extract 3 & 4 digit ICD 10 codes
  mutate(
    diag_3d = str_sub(deathdiag1, 1, 3),
    diag_4d = str_sub(deathdiag1, 1, 4)
  ) %>%
  # select dementia codes
  filter(diag_3d == "G30" | diag_3d == "F00" | diag_3d == "F01" |
    diag_3d == "F02" | diag_3d == "F03" | diag_4d == "F051")

# produce summary table
output_table_6 <- dementia_deaths %>%
  count(year) %>%
  rename(deaths = n)

1. Number and cost of prescriptions for MS

Create a table to compare the number and cost of prescribed items for patients with Multiple Sclerosis (MS) by HSCP in 2018/19. Include the number of dispensed items and cost per patient.

# read in HSCP names (used in exercises 7 & 9)
hscp_lookup <- read_csv("/conf/linkage/output/lookups/Unicode/Geography/Scottish Postcode Directory/Codes and Names/Integration Authority 2019 Lookup.csv") %>%
  select(
    hscp2019 = IntegrationAuthority2019Code,
    hscp_desc = IntegrationAuthority2019Name
  )

# read in data required from slf episode file - filter for year = 2018/19
pis_1819 <- read_slf_individual("1819",
  col_select = c("hscp2019", "ms", "pis_paid_items", "pis_cost")
)


# select all patients with MS & add on HSCP name
ms_1819 <- pis_1819 %>%
  filter(ms == 1) %>%
  left_join(hscp_lookup)

# produce summary table
output_table_7 <- ms_1819 %>%
  # group by hscp
  group_by(hscp2019, hscp_desc) %>%
  # sum up number of items, costs & patients with MS (not all will have had prescription)
  summarise(
    pis_paid_items = sum(pis_paid_items),
    pis_cost = sum(pis_cost),
    patients = sum(ms)
  ) %>%
  ungroup() %>%
  # calculate number of items / cost per patient
  mutate(
    items_per_patient = pis_paid_items / patients,
    cost_per_patient = pis_cost / patients
  )

1. A&E attendance in last 3 months of life.

Produce a table to show the number of deaths in Glasgow City HSCP in 2019/20 and what proportion had an A&E attendance in the last 3 months of life.

# extract all deaths in Glasgow City in 1920 - Filter year = 1920
gc_deaths <- read_slf_episode(
  year = "1920",
  # select columns
  col_select = c("anon_chi", "death_date"),
  # filter for Glasgow City
  partnerships = "S37000015",
  # Filter for death records
  recids = "NRS"
) %>%
  # exclude those with missing chi
  filter(anon_chi != "") %>%
  # exclude duplicates
  distinct(anon_chi, death_date)

# extract all A&E attendances in 1819 & 1920
ae <- read_slf_episode(
  year = c("1819", "1920"),
  # select columns
  col_select = c("anon_chi", "recid", "record_keydate1"),
  # filter for A&E data
  recids = "AE2"
) %>%
  # exclude those with missing chi
  filter(anon_chi != "") %>%
  # rename date of attendance
  rename(attendance_date = record_keydate1)

# select A&E attendances for those individuals who are in the GC deaths file
ae_gc <- ae %>%
  # filter A&E attendances for those in deaths file
  semi_join(gc_deaths) %>%
  # match on date of death
  left_join(gc_deaths)

# select A&E attendances which are within 3 months of death (counted as 91 days)
ae_gc_3m <- ae_gc %>%
  # create 3 month interval
  mutate(int_3m = interval(death_date - days(91), death_date)) %>%
  # flag if attendance is in 3 month interval
  mutate(att_3m = if_else(attendance_date %within% int_3m, 1, 0)) %>%
  # select only those attendances in 3 months before death
  filter(att_3m == 1)

# create list of patients with A&E attendance in 3m period
pats_ae_3m <- ae_gc_3m %>%
  # select only chi and attendance flag
  select(anon_chi, att_3m) %>%
  # restrict to one row per person
  distinct()

# final output for total number of deaths and number with an A&E attendance in last 3 months
output_table_8 <- gc_deaths %>%
  # match on attendance flag
  left_join(pats_ae_3m) %>%
  # summarise total deaths and deaths with A&E attendance in last 3 months
  summarise(
    deaths = n(),
    deaths_with_ae_att = sum(att_3m, na.rm = TRUE)
  ) %>%
  # calculate %
  mutate(prop_ae_3m = deaths_with_ae_att / deaths)

1. Non elective admissions in Geriatric Medicine.

Create a table showing the number of non-elective admissions with any part of the stay (Continuous Inpatient Journey, CIJ) in the specialty Geriatric Medicine, by HSCP in 2019/20. Also include the associated bed days, cost and number of patients.

# extract data required from episode file
smr_1920 <- read_slf_episode(
  year = "1920",
  col_select = c(
    "anon_chi", "record_keydate1", "record_keydate2",
    "spec", "hscp2019", "yearstay", "cost_total_net",
    "cij_marker", "cij_pattype"
  ),
  recids = c("01B", "GLS", "04B")
) %>%
  # exclude those with missing chi
  filter(anon_chi != "")

# flag episodes in Geriatric Medicine specialty AB
smr_1920 <- smr_1920 %>%
  mutate(ger_med = if_else(spec == "AB", 1, 0))

# select only those from non-elective stays
smr_1920_ne <- smr_1920 %>%
  filter(cij_pattype == "Non-Elective")

# aggregate to cij level
# we want to keep eariest admission and latest discharge, keep flag if any episode was in spec AB
# take hscp from the last record and sum beddays & cost
cij_1920 <- smr_1920_ne %>%
  arrange(anon_chi, cij_marker, record_keydate1, record_keydate2) %>%
  group_by(anon_chi, cij_marker) %>%
  summarise(
    across(record_keydate1, min),
    across(c(record_keydate2, ger_med), max),
    across(c(cij_pattype, hscp2019), last),
    across(c(yearstay, cost_total_net), sum)
  ) %>%
  ungroup()

# select only admissions with part of their stay in Geriatric Medicine specialty
cij_ger_med <- cij_1920 %>%
  filter(ger_med == 1)

# aggregate up to patient level
# we want to keep eariest admission and latest discharge, keep flag if any episode was in spec AB
# take hscp from the last record and sum beddays & cost
pat_1920 <- cij_ger_med %>%
  group_by(anon_chi, hscp2019) %>%
  summarise(
    across(c(ger_med, yearstay, cost_total_net), sum)
  ) %>%
  ungroup()

# produce output
# note patients may be counted in more than one hscp
output_table_9 <- pat_1920 %>%
  # match on hscp names
  left_join(hscp_lookup) %>%
  # group up to hscp level
  group_by(hscp2019, hscp_desc) %>%
  # sum up measures
  summarise(
    admissions = sum(ger_med),
    beddays = sum(yearstay),
    cost = sum(cost_total_net),
    patients = n()
  ) %>%
  ungroup()

Moohan/slfhelper documentation built on Feb. 1, 2025, 6:43 p.m.