In folkehelseinstituttet/spulsconnect: Sykdompulsen Connect

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

Analysing the raw data

We can get all values that exist in the raw data. There are three tables: Konsultasjon, KonsultasjonDiagnose, and KonsultasjonTakst

# Make sure you have these two packages loaded!
library(data.table)
library(magrittr)

# Obtain a list of all available age groups
d <- spulsconnect::tbl("Konsultasjon", db="SykdomspulsenAnalyse") %>%
  dplyr::count(PasientAlder) %>%
  dplyr::collect()
setDT(d)
setorder(d, PasientAlder)
d[]

# Obtain a list of all available practice types
d <- spulsconnect::tbl("Konsultasjon", db="SykdomspulsenAnalyse") %>%
  dplyr::count(Praksis) %>%
  dplyr::collect()
setDT(d)
setorder(d, Praksis)
d[]

# Obtain a list of all available diagnosis codes
# Note that these are also available in the variable:
# spulsconnect::config$norsyss_icpc2_codes
d <- spulsconnect::tbl("KonsultasjonDiagnose", db="SykdomspulsenAnalyse") %>%
  dplyr::count(Diagnose) %>%
  dplyr::collect()
setDT(d)
setorder(d, Diagnose)
d[]

# Obtain a list of all available takst codes
# Note that these are also available in the variable:
# spulsconnect::config$takstkoder
d <- spulsconnect::tbl("KonsultasjonTakst", db="SykdomspulsenAnalyse") %>%
  dplyr::count(Takst) %>%
  dplyr::collect()
setDT(d)
setorder(d, Takst)
d[]

Accessing NorSySS data

If you want access to the data after the ICPC-2 codes have been transformed into syndromes, there are two ways to access this data.

If you want to work directly with ICPC-2 codes, then there are two ways to access this data.

Directly pulling clean data from the database

You can access the data directly from the Sykdomspulsen_surv database, which is the production database for Sykdomspulsen. Inside this database the data has been cleaned and aggregated according to the definitions that have previously been agreed upon.

To access this, use the function spulsconnect::tbl("data_norsyss"). This provides a dbplyr tbl connection, which can then be manipulated using standard dplyr functions.

Note that the "syndromes" are listed under the variable tag_outcome and they end with two groupings of letters. For example, gastro_vk_ot and influensa_vk_o. The groupings of letters have the following meanings:

Who performed the consultation?
# v = (lege)vakt
# k = (lege)kontor

How did the consultation take place?
# o = oppmote
# t = telefon
# e = ekonsultasjon

Below we list some useful examples. Remember that year and week are isoyear and isoweek!

# Get all tag_outcome's (syndromes) that are available
spulsconnect::tbl("data_norsyss") %>%
  dplyr::distinct(tag_outcome) %>%
  dplyr::collect()

# Get all granularity_geo's that are available
spulsconnect::tbl("data_norsyss") %>%
  dplyr::distinct(granularity_geo) %>%
  dplyr::collect()

# Extract some data
d <- spulsconnect::tbl("data_norsyss") %>%
  dplyr::filter(tag_outcome %in% c("gastro_vk_ot")) %>%
  dplyr::filter(year == 2020) %>%
  dplyr::filter(age == "totalt") %>%
  dplyr::filter(location_code %in% c("norge","municip0301")) %>%
  dplyr::collect()

# Count the number of consultations in 2020 in Norway
d <- spulsconnect::tbl("data_norsyss") %>%
  dplyr::filter(tag_outcome %in% c("gastro_vk_ot")) %>%
  dplyr::filter(year == 2020) %>%
  dplyr::filter(age == "totalt") %>%
  dplyr::filter(location_code %in% c("norge")) %>%
  dplyr::summarize(n=sum(n,na.rm=T)) %>%
  dplyr::collect()

Creating your own syndromes

If you want to create your own syndromes, then it is suggested that you use the function norsyss_download_aggregated_diagnoses. This is the simplest way to access the data with custom syndromes. We have verified that all of the data joins and subsequent cleaning of municipalities are correct.

The function norsyss_download_aggregated_diagnoses will create a text file containing municipality-level aggregated data (municipality corresponds to medical provider, not necessarily where the patient lives).

You can specify your desired list of ICPC-2 codes via the diags argument. The following code will download the data from 2020-01-01 until today, with two syndrome lists:

Influenza (R80)
Gastro (D11, D70, D73)

file <- norsyss_download_aggregated_diagnoses(
  date_from = "2020-01-01",
  date_to = lubridate::today(),
  folder = getwd(),
  diags = list(
    "influenza" = c("R80"),
    "gastro" = c("D11", "D70", "D73")
  )
)

d <- data.table::fread(file)

Raw data via `spulsconnect`

The following code will download the raw data from 2020-01-01 until today.

d <- norsyss_download_raw(
  date_from = "2020-01-01",
  date_to = lubridate::today()
)

Raw data via basic R

The following code will download the raw data from 2020-01-01 until today.

# Specify dates
date_from <- "2020-01-01"
date_to <- lubridate::today()

# Connect to the database
con <- RODBC::odbcDriverConnect("driver={Sql Server};server=dm-prod;database=SykdomspulsenAnalyse; trusted_connection=yes")

# Download the data
command <- paste0(
    "select Id,Diagnose,PasientAlder,PasientKommune,BehandlerKommune,Konsultasjonsdato,Takst,Praksis from Konsultasjon join KonsultasjonDiagnose on Id=KonsultasjonId join KonsultasjonTakst on Id=KonsultasjonTakst.KonsultasjonId where Konsultasjonsdato >='",
    date_from,
    "' AND Konsultasjonsdato <= '",
    date_to,
    "'"
)
d <- RODBC::sqlQuery(con, command)
setDT(d)