NDI Final Analysis/Pipeline Build

NDI Analysis - 2021 Cohort

Summary of Findings

1. No SSS clients that were entered into the NDI search and were successful searched by NDI confirmed to have died by suicide.
2. There was one “False Match” reported by NDI. This is an individual who has died by suicide, but was not a strong match to SSS client data. This individual was confirmed to not be a current or previous SSS client (Last name, Birth Year, Residential State did not match). This was confirmed using data and an internet search of client vs. decedent.
3. There were 17 “True Matches” reported by the NDI. This is a NDI decedant who is a strong match to a SSS client, and is assumed to be deceased. Of the 17 potential client deaths, zero are reported as suicide or intentional self-harm. However, there were a number of client matches that died by other means of concern:

Deident Client	ICD Codes	Type
13276LRC	Other psychoactive substance abuse	Primary ICD
14859LRC	Alcohol abuse	Primary ICD
13720LRC	Other psychoactive substance abuse; Poisoning by, adverse effect of and underdosing of cocaine	Secondary and Quaternary ICD
18539LRC	Mental and behavioral disorders due to use of tobacco Unspecified mental and behavioural disorder	Secondary ICD
16112HRC	Alcoholic cirrhosis of liver	Primary ICD
17569MRC	Alcohol abuse; Mental and behavioral disorders due to use of tobacco Harmful use.	Primary and Secondary ICD

Use of Code

Summary of Steps:

1. Data is reformatted from character-based NDI submission style to meaningful columns and headers
2. Dataset is created of all clients marked by NDI as potential matches and classified as “True Match: Assumed Dead”. This includes all NDI hits in which a deceased individual in their database was a strong enough match to our client cohort be considered a deceased client.
3. A second dataset is created of all clients marked by NDI as “FALSE Match: Assumed Alive”. This includes NDI hits where a deceased individual in their system was not a strong enough match to our client cohort to be considered a match.
4. Within the FALSE match cohort, data is subsetted to only include those individuals who had a primary or secondary ICD code including Suicide. This allows us to double check that even though NDI marked this individual as a “FASE match”, the identified individual is in fact not an SSS client.
5. When possible, ICD codes are automatically replaced by COD from the CDC ICD.Code Manual.
6. A merged dataset is exported to a CSV file and all individuals remaining (TRUE matches: Potential SSS clients who have died by any means, FALSE Matches: Assumed not to be SSS clients who died by suicide). Each data entry is then confirmed by staff.

Directions:

To successfully run this script, place this file in a folder with the “CAUSE” file provided by the NDI search and the 7 CSV file of ICD Codes from the current year. Click the “RUN” button above. An excel file will be produced that includes only the entries of interested. Follow the directions in the “Interactive Data Table for Sorting” section to double check no entries were missed. Save the excel file under a new name so that it does not get overwritten in the event you run this script again. Complete the excel file.

Zip file contents Provided by NDI Analysis

File Name	File Type	Description
_Edits	PDF	User File Edit Results
PRTCAUSE	PDF	Cause of Death Report
SUMMARY	PDF	Summary Retrieval Statistics
REQFORMS	PDF	Death Certificate Request Forms
REPORT	PDF	NDI Retrieval Report
COMBINED	TXT	Combined File of Matching User and NDI Records
CAUSE	TXT	Cause of Death File, includes all personal information and match data
MATCH	TXT	User records involved in matches, in the same record format as submitted by the user
NOMATCH	TXT	User records not involved in matches with any NDI records, in the same record format as submitted by the user.
REJECTS	TXT	User records rejected by the NDI edit program and not included in the NDI search, in the same record format as submitted by the user

Analysis Code

Packages and Files

#Upload necessary packages
library(tidyr)
library(dplyr)
library(DT)
library(writexl)

# Import Files
#File must first be opened in excel and converted from txt file to CSV file.
cause <- read.table("FS2021-X088#00_R1.CAUSE", sep="\t", header=FALSE)

File Formatting

Reformatting from NDI submission style

#Split single imported string into columns based on character counts provided in the NDI Userguide
currated.cause=separate(cause, V1, 
into = c("Last.Name", "First.Name", "Middle.Initial", "SSN", "DOB.Month", "DOB.Day", "DOB.Year", "Father.Surname", "AoD.Unit", "AoD.Number", "Sex", "Race", "Mar.Status", "Res.State", "Birth.State", "Case.Num/Risk.Level", "Case.Created", "Blank.Field", "State.Death", "Year.Death", "St.Death.Code", "Alias", "Death.Cert.Num", "DOD.Month", "DOD.Day", "DOD.Year", "NDI:First.Name", "NDI:Middle.Initial", "NDI:Last.Name", "NDI:Father.Surname", "NDI:Surname", "NDI:SSN", "NDI:DOB.Month", "NDI:DOB.Day", "NDI:DOB.Year", "NDI:Age.Death", "NDI:Sex", "NDI:Race", "NDI:Marital.Status", "NDI:Res.State", "NDI:Birth.State", "Blank.Field.2", "Exact.Match.Indicator", "Match.Seq", "Num.Poss.Matches", "Prob.Score", "Class.Code", "Status.Code", "Blank.Field.3", "ICD.Code", "ICD.Recode", "ICD.Recode.2", "ICD.Recode.3", "Num.Entity.Axis", "Entity.Axis.Cond", "Numb.Record.Axis", "Record.Axis.Cond"), 
sep = c(20, 35, 36, 45, 47, 49, 53, 71, 72, 74, 75, 76, 77, 79, 81, 91, 97, 100, 112, 116, 119, 120, 126, 128, 130, 132, 134, 135, 136, 137, 138, 147, 148, 149, 152, 153, 154, 155, 156, 157, 158, 164, 165, 168, 171, 176, 177, 178, 179, 183, 188, 191,194,196,336, 338), remove = FALSE)

#remove the first column, which contains the string data used to create separate columns
currated.cause = subset(currated.cause, select = -c(1) )

#put an "NA" in all cells that contain nothing but white space. This must be done as the spaces are read in as characters due to file type at upload.
currated.cause[] <- lapply(currated.cause, function(x)
      type.convert(replace(x, grepl("^\\s*$", trimws(x)), NA), as.is = TRUE))

#Remove columns that have no values in any rows
currated.cause <- currated.cause[,colSums(is.na(currated.cause))<nrow(currated.cause)]

#Remove white spaces remaining as "character" reads at the end of the following columns. 
currated.cause$Last.Name=(gsub(" ","",currated.cause$Last.Name))
currated.cause$`NDI:First.Name`=(gsub(" ","",currated.cause$`NDI:First.Name`))
currated.cause$`NDI:DOB.Year`=(gsub(" ","",currated.cause$`NDI:DOB.Year`))
currated.cause$ICD.Code=(gsub(" ","",currated.cause$ICD.Code))

#Combine Month, Day and Year of birth into a single column
currated.cause$DOB <- paste(currated.cause$DOB.Month, "-", currated.cause$DOB.Day, "-", currated.cause$DOB.Year)
#Move the new single DOB variable to the DOB location in file
currated.cause=currated.cause %>% relocate(DOB, .after=DOB.Year)
#Remove original, seperate Month, Day, Year of birth columns
currated.cause <- currated.cause[ -c(3:5) ]

#Repeat process above for Date of Death and First/Last Name variables. 
currated.cause$DOD <- paste(currated.cause$DOD.Month, "-", currated.cause$DOD.Day, "-", currated.cause$DOD.Year)
currated.cause=currated.cause %>% relocate(DOD, .after=DOD.Year)
currated.cause <- currated.cause[ -c(17:19) ]

currated.cause$Last.First <- paste(currated.cause$Last.Name, ",", currated.cause$First.Name)
currated.cause=currated.cause %>% relocate(Last.First, .after=First.Name)
currated.cause <- currated.cause[ -c(1:2) ]

#Recode numerical Sex column with matching terms
currated.cause = currated.cause %>% mutate(Sex=recode(Sex, 
                         `1`="Male", `2`="Female", `9`= "Unknown",  "NA"= "Unknown"))

#Recode numerical Race column with matching terms
currated.cause = currated.cause %>% mutate(Race=recode(Race, 
                         `1`="White", `2`="Black",`3`= "Indian", `4`= "Chinese", `5`="Japanese", `6`="Hawaiian", `7`="Other-nonwhite",  `8`="Filipino", `0`="Other Asian/Pacific Islander",`9`= "Unknown",  "NA"="Unknown"))

#Recode numerical Marital Status column with matching terms
currated.cause = currated.cause %>% mutate(Mar.Status=recode(Mar.Status, 
                         `1`="Single", `2`="Married",`3`= "Widowed",  `4`= "Divorced",  `9`= "Unknown",  "NA"="Unknown"))

#Recode numerical Residential State column with matching terms
currated.cause = currated.cause %>% mutate(Res.State=recode(Res.State, 
                         `1`="Alabama", `2`="Alaska",`3`= "Arizona", `4`= "Arkansas", `5`="California", `6`="Colorado", `7`="Connecticut",  `8`="Delaware", `9`="District of Columbia", `10`= "Florida",  `11`="Georgia", `12`="Hawaii", `13`= "Idaho", `14`= "Illinois", `15`="Indiana", `16`="Iowa", `17`="Kansas", `18`="Kentucky", `19`="Louisiana", `20`= "Maine",  `21`="Maryland", `22`="Massachusetts", `23`= "Michigan", `24`= "Minnesota",  `25`="Mississippi", `26`="Missouri", `27`="Montana",`28`="Nebrasks", `29`="Nevada", `30`= "New Hampshire",  `31`="New Jersey", `32`="New Medico", `33`= "New York",  `34`= "North Carolina",  `35`="North Dakota",  `36`="Ohio", `37`="Oklahoma",  `38`="Oregon", `39`="Pennsylvania", `40`= "Rhode Island",  `41`="South Carolina", `42`="South Dakota",`43`= "Tennessee",  `44`= "Texas",  `45`="Utah",  `46`="Vermont", `47`="Virginia",  `48`="Washington", `49`="West Virginia", `50`= "Wisconsin", `51`="Wyoming",`52`="Puerto Rico", `53`= "Virgin Islands", `54`= "Guam",  `55`="Canada",  `56`="Cuba", `57`="Mexico", `59`="Remainder of World",`99`="Unknown", "NA"="Unknown"))

#Recode NDI Match symbols with matching terms
currated.cause = currated.cause %>%
  mutate(across(
    `NDI:First.Name`:`NDI:Birth.State`,
    ~ recode(
      .x,
      "X" = "Exact Match",
      "NA" = "Does Not Match",
      'NA' = "Does Not Match",
      " " = "Does Not Match",
      "?" = "Missing in NDI",
      "-" = "Missing from User",
      "N" = "Names match only NYSIIS",
      "I" = "Only First Initial Match",
      "B" = "Missing in NDI and User",
      "A" = "NDI Record is Alias",
      "IN" = "Only First Initial & Only NYSIIS"
    )
  ))

currated.cause$`NDI:First.Name`[is.na(currated.cause$`NDI:First.Name`)] <- "Does Not Match"
currated.cause$`NDI:Middle.Initial`[is.na(currated.cause$`NDI:Middle.Initial`)] <- "Does Not Match"
currated.cause$`NDI:Last.Name`[is.na(currated.cause$`NDI:Last.Name`)] <- "Does Not Match"
currated.cause$`NDI:Father.Surname`[is.na(currated.cause$`NDI:Father.Surname`)] <- "Does Not Match"
currated.cause$`NDI:Surname`[is.na(currated.cause$`NDI:Surname`)] <- "Does Not Match"
currated.cause$`NDI:SSN`[is.na(currated.cause$`NDI:SSN`)] <- "Does Not Match"
currated.cause$`NDI:DOB.Month`[is.na(currated.cause$`NDI:DOB.Month`)] <- "Does Not Match"
currated.cause$`NDI:DOB.Day`[is.na(currated.cause$`NDI:DOB.Day`)] <- "Does Not Match"
currated.cause$`NDI:DOB.Year`[is.na(currated.cause$`NDI:DOB.Year`)] <- "Does Not Match"
currated.cause$`NDI:Age.Death`[is.na(currated.cause$`NDI:Age.Death`)] <- "Does Not Match"
currated.cause$`NDI:Sex`[is.na(currated.cause$`NDI:Sex`)] <- "Does Not Match"
currated.cause$`NDI:Race`[is.na(currated.cause$`NDI:Race`)] <- "Does Not Match"
currated.cause$`NDI:Marital.Status`[is.na(currated.cause$`NDI:Marital.Status`)] <- "Does Not Match"
currated.cause$`NDI:Res.State`[is.na(currated.cause$`NDI:Res.State`)] <- "Does Not Match"
currated.cause$`NDI:Birth.State`[is.na(currated.cause$`NDI:Birth.State`)] <- "Does Not Match"

#Recode Status Code match designation with appropriate phrase
currated.cause = currated.cause %>% mutate(Status.Code=recode(Status.Code, 
                         `0`="FALSE Match: Assumed Alive",
                         `1`="TRUE Match: Assumed Dead"))

Produce dataset organized for SSS use

## Produce dataset organized for SSS use
#Reorganize columns to be in order of our submitted information followed by NDI match criteria
keep=c("Last.First", "NDI:First.Name", "NDI:Last.Name", 'DOB', "NDI:DOB.Month", "NDI:DOB.Day", "NDI:DOB.Year", 'Sex', "NDI:Sex", 'Race', "NDI:Race", 'Mar.Status',"NDI:Marital.Status",  'Res.State', "NDI:Res.State", 'Case.Num/Risk.Level', 'Case.Created', 'State.Death', 'DOD', "Death.Cert.Num", "Exact.Match.Indicator", "Status.Code", "ICD.Code", "Record.Axis.Cond")

cause.final = currated.cause[keep]

Creation of Match Files

Isolate Potential Matches

#Subset data to only include individuals with a status code of "1", which indicates a death.
death.matches=subset(cause.final, Status.Code == "TRUE Match: Assumed Dead")

Isolate Non-Matches with Death by Suicide

#Look in the status code "0" 
#status code = 0 ; ICD code for Suicide/intentional self harm resulting in death (X60-X84)
death.nomat=subset(cause.final, Status.Code == "FALSE Match: Assumed Alive")

Subset data to include only those that are True Matches, and False Matches that are death by suicide or self-harm

#Isolate records with an Record Axis Code indicating "Suicide" or "Intentional Self Harm" a a secondary ICD Code

death.nomatch2=subset(death.nomat, 
      grepl("*X6.*", Record.Axis.Cond) |
      grepl("*X7.*", Record.Axis.Cond) |
      grepl("*X8[0-4].*", Record.Axis.Cond) |
      grepl("*U03.*", Record.Axis.Cond) |
      grepl("*Y87.*", Record.Axis.Cond))
                      
check= rbind(death.matches,death.nomatch2)

### Seperate the Record axis into individual columns
### replace codes with phrases

library(splitstackshape)
check=cSplit(check, "Record.Axis.Cond", " ")

Reformating ICD Codes

Import CDC ICD codes from 2023 coding book

**Downloaded from CDC webpage*

https://ftp.cdc.gov/pub/Health_Statistics/NCHS/Publications/ICD10CM/April-1-2023-Update/

https://www.cdc.gov/nchs/icd/Comprehensive-Listing-of-ICD-10-CM-Files.htm

ICD Codes: Suicide or Intentional self-harm (X60-X84, Y)

ICD.codes=read.csv(
  "icd10cm-codes- April 1 2023.csv", header=FALSE)

ICD.codes=rename(ICD.codes, ICD.Code = V1)
ICD.codes=rename(ICD.codes, Description = V2)

ICD.codes18=read.csv("2018_ICDcodes.csv", header=FALSE)
ICD.codes18=rename(ICD.codes18, ICD.Code = V1)
ICD.codes18=rename(ICD.codes18, Description = V2)

REPLACE ALL RECORD CODES WITH TERMS

idx <- match(check$Record.Axis.Cond_1, ICD.codes$ICD.Code)
idxn <- which(!is.na(idx))
#idxn <- !is.na(idx) #Alternative
check$Record.Axis.Cond_1[idxn] <- ICD.codes$Description[idx[idxn]]
idx <- match(check$Record.Axis.Cond_1, ICD.codes18$ICD.Code)
idxn <- which(!is.na(idx))
#idxn <- !is.na(idx) #Alternative
check$Record.Axis.Cond_1[idxn] <- ICD.codes18$Description[idx[idxn]]

idx <- match(check$Record.Axis.Cond_2, ICD.codes$ICD.Code)
idxn <- which(!is.na(idx))
#idxn <- !is.na(idx) #Alternative
check$Record.Axis.Cond_2[idxn] <- ICD.codes$Description[idx[idxn]]
idx <- match(check$Record.Axis.Cond_2, ICD.codes18$ICD.Code)
idxn <- which(!is.na(idx))
#idxn <- !is.na(idx) #Alternative
check$Record.Axis.Cond_2[idxn] <- ICD.codes18$Description[idx[idxn]]

idx <- match(check$Record.Axis.Cond_3, ICD.codes$ICD.Code)
idxn <- which(!is.na(idx))
#idxn <- !is.na(idx) #Alternative
check$Record.Axis.Cond_3[idxn] <- ICD.codes$Description[idx[idxn]]
idx <- match(check$Record.Axis.Cond_3, ICD.codes18$ICD.Code)
idxn <- which(!is.na(idx))
#idxn <- !is.na(idx) #Alternative
check$Record.Axis.Cond_3[idxn] <- ICD.codes18$Description[idx[idxn]]

idx <- match(check$Record.Axis.Cond_4, ICD.codes$ICD.Code)
idxn <- which(!is.na(idx))
#idxn <- !is.na(idx) #Alternative
check$Record.Axis.Cond_4[idxn] <- ICD.codes$Description[idx[idxn]]
idx <- match(check$Record.Axis.Cond_4, ICD.codes18$ICD.Code)
idxn <- which(!is.na(idx))
#idxn <- !is.na(idx) #Alternative
check$Record.Axis.Cond_4[idxn] <- ICD.codes18$Description[idx[idxn]]

idx <- match(check$Record.Axis.Cond_5, ICD.codes$ICD.Code)
idxn <- which(!is.na(idx))
#idxn <- !is.na(idx) #Alternative
check$Record.Axis.Cond_5[idxn] <- ICD.codes$Description[idx[idxn]]
idx <- match(check$Record.Axis.Cond_5, ICD.codes18$ICD.Code)
idxn <- which(!is.na(idx))
#idxn <- !is.na(idx) #Alternative
check$Record.Axis.Cond_5[idxn] <- ICD.codes18$Description[idx[idxn]]

idx <- match(check$Record.Axis.Cond_6, ICD.codes$ICD.Code)
idxn <- which(!is.na(idx))
#idxn <- !is.na(idx) #Alternative
check$Record.Axis.Cond_6[idxn] <- ICD.codes$Description[idx[idxn]]
idx <- match(check$Record.Axis.Cond_6, ICD.codes18$ICD.Code)
idxn <- which(!is.na(idx))
#idxn <- !is.na(idx) #Alternative
check$Record.Axis.Cond_6[idxn] <- ICD.codes18$Description[idx[idxn]]

idx <- match(check$Record.Axis.Cond_7, ICD.codes$ICD.Code)
idxn <- which(!is.na(idx))
#idxn <- !is.na(idx) #Alternative
check$Record.Axis.Cond_7[idxn] <- ICD.codes$Description[idx[idxn]]
idx <- match(check$Record.Axis.Cond_7, ICD.codes18$ICD.Code)
idxn <- which(!is.na(idx))
#idxn <- !is.na(idx) #Alternative
check$Record.Axis.Cond_7[idxn] <- ICD.codes18$Description[idx[idxn]]

idx <- match(check$Record.Axis.Cond_8, ICD.codes$ICD.Code)
idxn <- which(!is.na(idx))
#idxn <- !is.na(idx) #Alternative
check$Record.Axis.Cond_8[idxn] <- ICD.codes$Description[idx[idxn]]
idx <- match(check$Record.Axis.Cond_8, ICD.codes18$ICD.Code)
idxn <- which(!is.na(idx))
#idxn <- !is.na(idx) #Alternative
check$Record.Axis.Cond_8[idxn] <- ICD.codes18$Description[idx[idxn]]

Combine Potential Matches into Excel File for Verification by SSS

check$`Confirmed Client Suicide` = NA
check$`SSS Justification` = NA
write_xlsx(check,"NDI_DetailedCheck.xlsx")

Interactive Data Table

This table will consist of all “True Matches” in which the NDI search produced a probable match to an SSS client, that is those that are classified as “True Match: Assumed Dead”. This includes all NDI hits in which a deceased individual in their database was a strong enough match to our client cohort be considered a deceased client.