3.3 ICD 9 & 10

Obtain relevant fields.

ICD9_codes <- ICD %>% select(f.eid,starts_with('f.41271')) %>% arrange(f.eid) %>% data.frame()
ICD9_dates <- ICD %>% select(f.eid,starts_with('f.41281.')) %>% arrange(f.eid) %>% data.frame()

ICD10_codes <- ICD %>% select(f.eid,starts_with('f.41270')) %>% arrange(f.eid) %>% data.frame()
ICD10_dates <- ICD %>% select(f.eid,starts_with('f.41280.')) %>% arrange(f.eid) %>% data.frame() 

ICD10_codes_primary_death <- ICD %>% select(f.eid,starts_with('f.40001')) %>% arrange(f.eid) %>% data.frame()
ICD10_codes_secondary_death <- ICD %>% select(f.eid,starts_with('f.40002')) %>% arrange(f.eid) %>% data.frame()
ICD10_death_date <- ICD %>% select(f.eid, f.40000.0.0) %>% arrange(f.eid) %>% data.frame()

Merge ICD10 primary death and death date tables into a long format.

ICD10_codes_primary_death_long <-
  left_join(ICD10_codes_primary_death %>% pivot_longer(cols=-f.eid),ICD10_death_date,by="f.eid") %>% 
  select(-name) %>% rename(code=value,event_dt=`f.40000.0.0`) %>% mutate(type="ICD10_death_primary") %>% distinct()

Merge ICD10 secondary death and death date tables into a long format.

ICD10_codes_secondary_death_long <-
  left_join(ICD10_codes_secondary_death %>% pivot_longer(cols=-f.eid),ICD10_death_date,by="f.eid") %>% 
  select(-name) %>% rename(code=value,event_dt=`f.40000.0.0`) %>% mutate(type="ICD10_death_secondary") %>% distinct()

Merge ICD10 codes and their event dates tables into a long format.

ICD10_codes_long <- merge_long("f.41270","f.41280",ICD10_codes,ICD10_dates,"ICD10") %>% distinct()

Merge ICD9 codes and their event dates tables into a long format.

ICD9_codes <- ICD9_codes %>% mutate_at(vars(f.41271.0.0:f.41271.0.46), as.character)
ICD9_codes_long <- merge_long("f.41271","f.41281",ICD9_codes,ICD9_dates,"ICD9") %>% distinct()

Merge all of the ICD event tables with dates, and then remove observations with no codes (code == NA).

ICD_codes_full <- do.call(rbind,list(ICD10_codes_long,ICD9_codes_long,ICD10_codes_primary_death_long,ICD10_codes_secondary_death_long)) %>% filter(!is.na(code))