PERMIT SIR updated add conditional variables

memory.size(70000)
#READ CONDITION FILES- SUFFIX ALL NAMES WITH A 1 THEN YOU CAN IMPORT THEM ALL TOGETHER

library(zoo)
library(plyr)
library(tidyverse)
library(zoo)
library(data.table)
library(survival)
library(lubridate)

#The processed sir.data file will be used from this point onwards, not the long format.
####################################################

#######################################################
#######################################################
load("crea.rephf2tests.Rdata")
load("sirdatahfonly.rda")
sir.data<-sir.data[sir.data$PatientID %in% crea.rep$PatientID,]
sir.data$CodeValue<-as.numeric(as.character(sir.data$CodeValue))
sir.data<-sir.data[!is.na(sir.data$CodeValue),]
#head(sir.data[sir.data$EntryDate<19800101,]) #Historical xray data  etc has been retrospectively added 
sir.data$EntryDate<-ifelse(sir.data$EntryDate>20170801|sir.data$EntryDate<19000101,NA,sir.data$EntryDate)

#######################################################
allfiles = list.files(pattern="*1.csv")
for (i in 1:length(allfiles)) assign(allfiles[i], read.csv(allfiles[i]))
#######################################################

#CODE CONDITIONS REQUIRING VALUES TO BE ADDED, NEAREST IF WITHIN X TIME PERIOD
#NOTE THAT COERCION OF TIBBLES TO DATAFRAME FORMAT MAY NOT BE MAINTAINED IF YOU SAVE R OBJECTS
#TIBBLE FORMATTING CAUSING ISSUES CAN ALSO BE CLEARED BY SAVING MIDWAY POINTS AS CSV FILES


#NEAREST PRIOR MEAN DAILY SERUM SODIUM #DATE FLAG 30 DAYS
unique(sir.data$CodeUnits[sir.data$ReadCode %in% SerumSodium1.csv$ReadCode])
sir.data$temp<-ifelse(sir.data$ReadCode %in% SerumSodium1.csv$ReadCode & !sir.data$CodeUnits=="%" & sir.data$CodeValue>=20 & sir.data$CodeValue<=3000, paste(sir.data$CodeValue),NA)
smalltab<-sir.data[!is.na(sir.data$temp),c("PatientID","CodeValue","EntryDate")]
columns=names(smalltab[c(1,3)])
dots<-lapply(columns, as.symbol)
first <-smalltab %>% 
group_by_(.dots=dots) %>%
summarise(SerumSodium=mean(CodeValue)) %>%
as.data.frame

indx1 <-neardate(crea.rep$PatientID, first$PatientID, crea.rep$EntryDate, first$EntryDate, 
                   best="prior")
crea.rep$SerumSodium<-first[indx1, "SerumSodium"]
crea.rep$SerSodDateFlag<- ifelse(as.numeric(abs(crea.rep$EntryDate - first$EntryDate[indx1])) >30, 1, 0)
crea.rep$SerumSodium<-unlist(crea.rep$SerumSodium)


#CLOSEST DAILY BMI MEAN #1YR DATE FLAG
sir.data$temp<-as.numeric(ifelse(sir.data$ReadCode %in% BMI1.csv$ReadCode & sir.data$CodeValue>=10 & sir.data$CodeValue<=70, as.numeric(paste(sir.data$CodeValue)),NA))
sir.data$height<-as.numeric(ifelse(sir.data$ReadCode %in% Height1.csv$ReadCode,as.numeric(paste(sir.data$CodeValue)),NA))
unique(sir.data$CodeUnits[sir.data$ReadCode %in% Height1.csv$ReadCode])
sir.data$height<-ifelse(!is.na(sir.data$height)&sir.data$CodeUnits=="cm",sir.data$height/100,sir.data$height)
unique(sir.data$CodeUnits[sir.data$ReadCode %in% Weight1.csv$ReadCode])
sir.data$temp<-ifelse(is.na(sir.data$temp)&sir.data$ReadCode %in% Weight1.csv$ReadCode & sir.data$CodeUnits=="kg/m2",sir.data$CodeValue,sir.data$temp)
#Apply max height across dataset

htab<-sir.data[!is.na(sir.data$height),c("PatientID","CodeValue","EntryDate")]
columns1=names(htab[c(1,3)])
dots<-lapply(columns1, as.symbol)
firstH <-htab %>% 
group_by_(.dots=dots) %>%
summarize(Height=max(CodeValue)) %>%
as.data.frame
sir.data<-merge(sir.data,firstH,all.x=TRUE)

sir.data$weight<-as.numeric(ifelse(sir.data$ReadCode %in% Weight1.csv$ReadCode & !sir.data$CodeUnits=="kg/m2",as.numeric(paste(sir.data$CodeValue)),NA))
sir.data$temp<-ifelse(is.na(sir.data$temp),(as.numeric(sir.data$weight)/(as.numeric(sir.data$height))^2),sir.data$temp)

BMItab<-sir.data[sir.data$temp>=10 & sir.data$temp<=70,c("PatientID","temp","EntryDate")]
columns1=names(BMItab[c(1,3)])
dots<-lapply(columns1, as.symbol)
firstA <-BMItab %>% 
group_by_(.dots=dots) %>%
summarize(BMI=mean(temp)) %>%
as.data.frame

indx1 <- neardate(crea.rep$PatientID, firstA$PatientID, crea.rep$EntryDate, firstA$EntryDate, 
                   best="prior")
crea.rep$BMI<-firstA[indx1, "BMI"]
crea.rep$BMIDateFlag<- ifelse(as.numeric(abs(crea.rep$EntryDate - firstA$EntryDate[indx1])) >365, 1, 0)
crea.rep$BMI<-unlist(crea.rep$BMI)


#MEAN DAILY SERUM URIC ACID #1 YEAR DATE FLAG
sir.data$temp<-ifelse(sir.data$ReadCode %in% UricAcid1.csv$ReadCode, as.numeric(paste(sir.data$CodeValue)),NA)
UA<-sir.data[!is.na(sir.data$temp) & !is.na(sir.data$PatientID),]
unique(UA$CodeUnits)
sir.data$temp<-ifelse((sir.data$CodeUnits=="umol/L"|sir.data$CodeUnits=="umol/l") & sir.data$ReadCode %in% UricAcid1.csv$ReadCode,sir.data$temp/1000,sir.data$temp)
sir.data$temp<-ifelse((sir.data$CodeUnits=="iu/L"|sir.data$CodeUnits=="IU/L"|sir.data$CodeUnits=="None") & sir.data$ReadCode %in% UricAcid1.csv$ReadCode,NA,sir.data$temp)
sir.data$temp<-ifelse(sir.data$CodeValue>=0.001 & sir.data$CodeValue<=1, sir.data$temp,NA)

smalltab<-sir.data[!is.na(sir.data$temp),c("PatientID","CodeValue","EntryDate")]
columns=names(smalltab[c(1,3)])
dots<-lapply(columns, as.symbol)
firstU <-smalltab %>% 
group_by_(.dots=dots) %>%
summarise(UricAcid=mean(CodeValue)) %>%
as.data.frame

indx1 <-neardate(crea.rep$PatientID, firstU$PatientID, crea.rep$EntryDate, firstU$EntryDate, 
                   best="prior")
crea.rep$UricAcid<-firstU[indx1, "UricAcid"]
crea.rep$UricAcidDateFlag<- ifelse(as.numeric(abs(crea.rep$EntryDate - firstU$EntryDate[indx1])) >365, 1, 0)
crea.rep$UricAcid<-unlist(crea.rep$UricAcid)


#MEAN DAILY BLOOD UREA NITROGEN #30 DAY DATE FLAG
table(droplevels(sir.data$CodeUnits[sir.data$ReadCode %in% BUN1.csv$ReadCode]))
sir.data$temp<-ifelse(sir.data$ReadCode %in% BUN1.csv$ReadCode,as.numeric(paste(sir.data$CodeValue)),NA)
sir.data$temp<-ifelse(sir.data$ReadCode %in% BUN1.csv$ReadCode & as.numeric(sir.data$CodeUnits)>=1,as.numeric(sir.data$CodeUnits),sir.data$temp)
smalltab<-sir.data[!is.na(sir.data$temp)&sir.data$CodeValue>=1&as.numeric(sir.data$CodeValue)<=50,c("PatientID","CodeValue","EntryDate")]
summary(smalltab$CodeValue)

columns=names(smalltab[c(1,3)])
dots<-lapply(columns, as.symbol)
first <-smalltab %>% 
group_by_(.dots=dots) %>%
summarise(BUN=mean(CodeValue)) %>%
as.data.frame

indx1 <-neardate(crea.rep$PatientID, first$PatientID, crea.rep$EntryDate, first$EntryDate, 
                   best="prior")
crea.rep$BUN<-first[indx1, "BUN"]
crea.rep$BUNDateFlag<- ifelse(as.numeric(abs(crea.rep$EntryDate - first$EntryDate[indx1])) >30, 1, 0)

crea.rep$BUN<-unlist(crea.rep$BUN)
crea.rep$BUNDateFlag<-ifelse(is.na(crea.rep$BUNDateFlag),0,crea.rep$BUNDateFlag)
crea.rep$BUN_DF<-ifelse(crea.rep$BUNDateFlag==0,crea.rep$BUN,NA)


#MEAN DAILY SERUM POTASSIUM #1 MONTH DATE FLAG
sir.data$temp<-ifelse(sir.data$ReadCode %in% SerumPotassium1.csv$ReadCode,as.numeric(paste(sir.data$CodeValue)),NA)
unique(sir.data$CodeUnits[sir.data$ReadCode %in% SerumPotassium1.csv$ReadCode])
sir.data$temp<-ifelse(is.na(sir.data$ReadCode)&sir.data$ReadCode %in% SerumPotassium1.csv$ReadCode & as.numeric(sir.data$CodeUnits)>0,as.numeric(sir.data$CodeUnits),sir.data$temp)
sir.data$temp<-ifelse(sir.data$ReadCode %in% SerumPotassium1.csv$ReadCode & (sir.data$CodeUnits=="mL/min"),NA,sir.data$temp)

smalltab<-sir.data[!is.na(sir.data$temp)&sir.data$temp>=2&sir.data$temp<=10,c("PatientID","CodeValue","EntryDate")]
columns=names(smalltab[c(1,3)])
dots<-lapply(columns, as.symbol)
first <-smalltab %>% 
group_by_(.dots=dots) %>%
summarise(SerumPotassium=mean(CodeValue)) %>%
as.data.frame

indx1 <-neardate(crea.rep$PatientID, first$PatientID, crea.rep$EntryDate, first$EntryDate, 
                   best="prior")
crea.rep$SerPotassium<-first[indx1, "SerumPotassium"]
crea.rep$SerPotDateFlag<- ifelse(as.numeric(abs(crea.rep$EntryDate - first$EntryDate[indx1])) >30, 1, 0)
crea.rep$SerPotassium<-unlist(crea.rep$SerPotassium)


#MEAN DAILY HEART RATE #30 DAYS
sir.data$temp<-ifelse(sir.data$ReadCode %in% HeartRate1.csv$ReadCode,as.numeric(paste(sir.data$CodeValue)),NA)
unique(sir.data$CodeUnits[sir.data$ReadCode %in% HeartRate1.csv$ReadCode])
smalltab<-sir.data[!is.na(sir.data$temp)&sir.data$temp>=20&sir.data$temp<=200,c("PatientID","CodeValue","EntryDate")]
columns=names(smalltab[c(1,3)])
dots<-lapply(columns, as.symbol)
first <-smalltab %>% 
group_by_(.dots=dots) %>%
summarise(HeartRate=mean(CodeValue)) %>%
as.data.frame 
indx1 <-neardate(crea.rep$PatientID, first$PatientID, crea.rep$EntryDate, first$EntryDate, 
                   best="prior")
crea.rep$HeartRate<-first[indx1, "HeartRate"]
crea.rep$HeartRateDateFlag<- ifelse(as.numeric(abs(crea.rep$EntryDate - first$EntryDate[indx1])) >30, 1, 0)
crea.rep$HeartRate<-unlist(crea.rep$HeartRate)
crea.rep$HeartRateDateFlag<-ifelse(is.na(crea.rep$HeartRateDateFlag),0,crea.rep$HeartRateDateFlag)
crea.rep$HeartRate_DF<-ifelse(crea.rep$HeartRateDateFlag==0,crea.rep$HeartRate,NA)


#MEAN BNP #1 YEAR
sir.data$temp<-ifelse(sir.data$ReadCode %in% BNP1.csv$ReadCode,as.numeric(paste(sir.data$CodeValue)),NA)
#1ng/L=1pg/ml
unique(sir.data$CodeUnits[sir.data$ReadCode %in% BNP1.csv$ReadCode])
smalltab<-sir.data[!is.na(sir.data$temp)&sir.data$temp>=1&sir.data$temp<=1000,c("PatientID","CodeValue","EntryDate")]
columns=names(smalltab[c(1,3)])
dots<-lapply(columns, as.symbol)
first <-smalltab %>% 
group_by_(.dots=dots) %>%
summarise(BNP=mean(as.numeric(CodeValue))) %>%
as.data.frame 
indx1 <-neardate(crea.rep$PatientID, first$PatientID, crea.rep$EntryDate, first$EntryDate, 
                   best="prior")
crea.rep$BNP<-first[indx1, "BNP"]
crea.rep$BNPDateFlag<- ifelse(as.numeric(abs(crea.rep$EntryDate - first$EntryDate[indx1])) >365, 1, 0)
crea.rep$BNP<-unlist(crea.rep$BNP)
crea.rep$BNPDateFlag<-ifelse(is.na(crea.rep$BNPDateFlag),0,crea.rep$BNPDateFlag)
crea.rep$BNP_DF<-ifelse(crea.rep$BNPDateFlag==0,crea.rep$BNP,NA)


#MEAN NT-PRO BNP #1 YEAR
sir.data$temp<-ifelse(sir.data$ReadCode %in% NTPROBNP1.csv$ReadCode,as.numeric(paste(sir.data$CodeValue)),NA)
unique(sir.data$CodeUnits[sir.data$ReadCode %in% NTPROBNP1.csv$ReadCode])
#1ng/L=1pg/ml
smalltab<-sir.data[!is.na(sir.data$temp)&sir.data$temp>=1&sir.data$temp<=6000,c("PatientID","CodeValue","EntryDate")]
columns=names(smalltab[c(1,3)])
dots<-lapply(columns, as.symbol)
first <-smalltab %>% 
group_by_(.dots=dots) %>%
summarise(NTPROBNP=mean(as.numeric(CodeValue))) %>%
as.data.frame 
indx1 <-neardate(crea.rep$PatientID, first$PatientID, crea.rep$EntryDate, first$EntryDate, 
                   best="prior")
crea.rep$NTPROBNP<-first[indx1, "NTPROBNP"]
crea.rep$NTPROBNPDateFlag<- ifelse(as.numeric(abs(crea.rep$EntryDate - first$EntryDate[indx1])) >365, 1, 0)
crea.rep$NTPROBNPDateFlag<-ifelse(is.na(crea.rep$NTPROBNPDateFlag),0,crea.rep$NTPROBNPDateFlag)
crea.rep$NTPROBNP<-unlist(crea.rep$NTPROBNP)
crea.rep$NTPROBNP_DF<-ifelse(crea.rep$NTPROBNPDateFlag==0,crea.rep$NTPROBNP,NA)


#MIN DAILY SYSTOLIC BP #30 DAYS
sir.data$SBP<-ifelse(sir.data$ReadCode %in% SBP1.csv$ReadCode,as.numeric(paste(sir.data$CodeValue)),NA)
sir.data$temp<-sir.data$SBP
unique(sir.data$CodeUnits[sir.data$ReadCode %in% SBP1.csv$ReadCode])
smalltab<-sir.data[!is.na(sir.data$temp)&sir.data$temp>=30&sir.data$temp<=300,c("PatientID","SBP","EntryDate")]
columns=names(smalltab[c(1,3)])
dots<-lapply(columns, as.symbol)
first <-smalltab %>% 
group_by_(.dots=dots) %>%
slice(which.min(as.numeric(SBP))) %>%
ungroup()%>%
as.data.frame

indx2 <- neardate(crea.rep$PatientID, first$PatientID, crea.rep$EntryDate, first$EntryDate, 
                   best="prior")
crea.rep$SBP<-first[indx2, "SBP"]
crea.rep$SBPDateFlag<- ifelse(as.numeric(abs(crea.rep$EntryDate - first$EntryDate[indx2])) >30, 1, 0)
crea.rep$SBP<-unlist(crea.rep$SBP)


#MIN DAILY DIASTOLIC BP #30 DAYS
sir.data$DBP<-ifelse(sir.data$ReadCode %in% DBP1.csv$ReadCode,as.numeric(paste(sir.data$CodeValue)),NA)
sir.data$temp<-sir.data$DBP
smalltab<-sir.data[!is.na(sir.data$temp)&sir.data$temp>=20&sir.data$temp<=200,c("PatientID","DBP","EntryDate")]
columns=names(smalltab[c(1,3)])
dots<-lapply(columns, as.symbol)
first <-smalltab %>% 
group_by_(.dots=dots) %>%
slice(which.min(as.numeric(DBP))) %>%
ungroup()%>%
as.data.frame

indx2 <- neardate(crea.rep$PatientID, first$PatientID, crea.rep$EntryDate, first$EntryDate, 
                   best="prior")

crea.rep$DBP<-first[indx2, "DBP"]
crea.rep$DBPDateFlag<- ifelse(as.numeric(abs(crea.rep$EntryDate - first$EntryDate[indx2])) >30, 1, 0)
crea.rep$DBP<-unlist(crea.rep$DBP)


#MEAN DAILY SERUM ALBUMIN #`1 YEAR DATE FLAG
sir.data$temp<-ifelse(sir.data$ReadCode %in% SerumAlbumin1.csv$ReadCode,as.numeric(paste(sir.data$CodeValue)),NA)
unique(sir.data$CodeUnits[sir.data$ReadCode %in% SerumAlbumin1.csv$ReadCode])
smalltab<-sir.data[!is.na(sir.data$temp)&sir.data$temp>=10&sir.data$temp<=60,c("PatientID","CodeValue","EntryDate")]
columns=names(smalltab[c(1,3)])
dots<-lapply(columns, as.symbol)
first <-smalltab %>% 
group_by_(.dots=dots) %>%
summarise(SerumAlbumin=mean(CodeValue)) %>%
as.data.frame

indx1 <-neardate(crea.rep$PatientID, first$PatientID, crea.rep$EntryDate, first$EntryDate, 
                   best="prior")
crea.rep$SerumAlbumin<-first[indx1, "SerumAlbumin"]
crea.rep$SerumAlbuminDateFlag<- ifelse(as.numeric(abs(crea.rep$EntryDate - firstU$EntryDate[indx1])) >365, 1, 0)
crea.rep$SerumAlbumin<-unlist(crea.rep$SerumAlbumin)


#MAX DAILY URINE ALBUMIN 1 YEAR DATE FLAG
sir.data$temp<-ifelse(sir.data$ReadCode %in% UAlbumin1.csv$ReadCode,as.numeric(paste(sir.data$CodeValue)),NA)
unique(sir.data$CodeUnits[sir.data$ReadCode %in% UAlbumin1.csv$ReadCode])
sir.data$temp<-ifelse(sir.data$ReadCode %in% UAlbumin1.csv$ReadCode&sir.data$CodeUnits=="g/L",sir.data$temp*1000,sir.data$temp)
smalltab<-sir.data[!is.na(sir.data$temp)&sir.data$temp>=0&sir.data$temp<=1000,c("PatientID","CodeValue","EntryDate")]
columns=names(smalltab[c(1,3)])
dots<-lapply(columns, as.symbol)
first <-smalltab %>% 
group_by_(.dots=dots) %>%
summarise(UrineAlbumin=mean(CodeValue)) %>%
as.data.frame

indx1 <-neardate(crea.rep$PatientID, first$PatientID, crea.rep$EntryDate, first$EntryDate, 
                   best="prior")
crea.rep$UrineAlbumin<-first[indx1, "UrineAlbumin"]
crea.rep$UrineAlbuminDateFlag<- ifelse(as.numeric(abs(crea.rep$EntryDate - first$EntryDate[indx1])) >365, 1, 0)
crea.rep$UrineAlbumin<-unlist(crea.rep$UrineAlbumin)


#MEAN DAILY URINE ALBUMIN CREATININE RATIO #1 YEAR DATE FLAG
sir.data$temp<-ifelse(sir.data$ReadCode %in% UACR1.csv$ReadCode,as.numeric(paste(sir.data$CodeValue)),NA)
unique(sir.data$CodeUnits[sir.data$ReadCode %in% UACR1.csv$ReadCode])
sir.data$temp<-ifelse(sir.data$ReadCode %in% UACR1.csv$ReadCode&sir.data$CodeUnits=="ratio"|sir.data$CodeUnits=="None"|sir.data$CodeUnits=="U/ml"|sir.data$CodeUnits=="GPL",NA,sir.data$temp)
sir.data$temp<-ifelse(is.na(sir.data$temp)&sir.data$ReadCode %in% UACR1.csv$ReadCode & as.numeric(sir.data$CodeUnits)>0,as.numeric(sir.data$CodeUnits),sir.data$temp)

smalltab<-sir.data[!is.na(sir.data$temp)&sir.data$temp>=0&sir.data$temp<=3000,c("PatientID","CodeValue","EntryDate")]
columns=names(smalltab[c(1,3)])
dots<-lapply(columns, as.symbol)
first <-smalltab %>% 
group_by_(.dots=dots) %>%
summarise(UACratio=mean(CodeValue)) %>%
as.data.frame

indx1 <-neardate(crea.rep$PatientID, first$PatientID, crea.rep$EntryDate, first$EntryDate, 
                   best="prior")
crea.rep$UACratio<-first[indx1, "UACratio"]
crea.rep$UACDateFlag<- ifelse(as.numeric(abs(crea.rep$EntryDate - first$EntryDate[indx1])) >365, 1, 0)
crea.rep$UACratio<-unlist(crea.rep$UACratio)


#MAX DAILY MEAN CORPUSCULAR VOLUME #120 DAY DATE FLAG
sir.data$temp<-ifelse(sir.data$ReadCode %in% MCV1.csv$ReadCode,as.numeric(paste(sir.data$CodeValue)),NA)
sir.data$temp<-ifelse(is.na(sir.data$temp)&sir.data$ReadCode %in% MCV1.csv$ReadCode & as.numeric(sir.data$CodeUnits)>0,as.numeric(sir.data$CodeUnits),sir.data$temp)
unique(sir.data$CodeUnits[sir.data$ReadCode %in% MCV1.csv$ReadCode])
#1femtoliter=1 cubic micron
smalltab<-sir.data[!is.na(sir.data$temp)&sir.data$temp>=50&sir.data$temp<=150,c("PatientID","CodeValue","EntryDate")]
columns=names(smalltab[c(1,3)])
dots<-lapply(columns, as.symbol)
first <-smalltab %>% 
group_by_(.dots=dots) %>%
summarise(MCV=mean(CodeValue)) %>%
as.data.frame

indx1 <-neardate(crea.rep$PatientID, first$PatientID, crea.rep$EntryDate, first$EntryDate, 
                   best="prior")
 crea.rep$MCV<-first[indx1, "MCV"]
crea.rep$MCVDateFlag<- ifelse(as.numeric(abs(crea.rep$EntryDate - first$EntryDate[indx1])) >120, 1, 0)
crea.rep$MCV<-unlist(crea.rep$MCV)


#MAX DAILY HAEMOGLOBIN #120 DAY DATE FLAG
sir.data$temp<-ifelse(sir.data$ReadCode %in% Haemoglobin1.csv$ReadCode,as.numeric(paste(sir.data$CodeValue)),NA)
unique(sir.data$CodeUnits[sir.data$ReadCode %in% Haemoglobin1.csv$ReadCode])
sir.data$temp<-ifelse(is.na(sir.data$temp)&sir.data$ReadCode %in% Haemoglobin1.csv$ReadCode & as.numeric(sir.data$CodeUnits)>0,as.numeric(sir.data$CodeUnits),sir.data$temp)
sir.data$temp<-ifelse(is.na(sir.data$temp)&sir.data$ReadCode %in% Haemoglobin1.csv$ReadCode&sir.data$CodeUnits=="g/dl"|sir.data$CodeUnits=="g/dL",sir.data$temp*10,sir.data$temp)
sir.data$temp<-ifelse(is.na(sir.data$temp)&sir.data$ReadCode %in% Haemoglobin1.csv$ReadCode&sir.data$CodeUnits=="g/dl"|sir.data$CodeUnits=="g/dL",sir.data$temp*10,sir.data$temp)
sir.data$temp<-ifelse(is.na(sir.data$temp)&sir.data$ReadCode %in% Haemoglobin1.csv$ReadCode&sir.data$CodeUnits=="mmol/mol"|sir.data$CodeUnits=="None",NA,sir.data$temp)

smalltab<-sir.data[!is.na(sir.data$temp)&sir.data$temp>=30&sir.data$temp<=260,c("PatientID","CodeValue","EntryDate")]
columns=names(smalltab[c(1,3)])
dots<-lapply(columns, as.symbol)
first <-smalltab %>% 
group_by_(.dots=dots) %>%
summarise(Haemoglobin=mean(CodeValue)) %>%
as.data.frame

indx1 <-neardate(crea.rep$PatientID, first$PatientID, crea.rep$EntryDate, first$EntryDate, 
                   best="prior")
crea.rep$Haemoglobin<-first[indx1, "Haemoglobin"]
crea.rep$HaemDateFlag<- ifelse(as.numeric(abs(crea.rep$EntryDate - first$EntryDate[indx1])) >120, 1, 0)
crea.rep$Haemoglobin<-unlist(crea.rep$Haemoglobin)
#save(crea.rep, file = "crea.repongoing.rda")


#########################################################
#VARIABLES WITH START AND ONGOING DATES- MATCH FIRST OCCURENCE
##########################################################


load("sirdatahfonly.rda") #Reload the full table including rows with value fields with non numeric values

#NEPHRECTOMY
sir.data$NephDate<-sir.data$EntryDate 
sir.data$NephDate<-ifelse(!sir.data$ReadCode  %in%  Nephrectomy1.csv$ReadCode,NA,sir.data$NephDate) 

smalltab<-sir.data[!is.na(sir.data$NephDate) & sir.data$PatientID %in% crea.rep$PatientID,c("PatientID","NephDate")]
first<-smalltab %>%
    group_by(PatientID) %>%
    arrange(NephDate) %>%
    slice(1L) %>%
as.data.frame
head(first)
first$NephDate<-as.Date(as.character(first$NephDate),format="%Y%m%d")
 
crea.rep<-merge(crea.rep,first,all.x=TRUE)
crea.rep$event.date<-as.Date(as.character(crea.rep$EntryDate),format="%Y%m%d")
crea.rep$NephDate<-as.Date(as.character(crea.rep$NephDate),format="%Y-%m-%d")
crea.rep$DaysSinceNephrectomy<-difftime(strptime(crea.rep$NephDate,format="%Y-%m-%d"),strptime(crea.rep$event.date,format="%Y-%m-%d"),unit="days")
crea.rep$DaysSinceNephrectomy<-as.numeric(crea.rep$DaysSinceNephrectomy, units="days")
crea.rep$DaysSinceNephrectomy<-ifelse(crea.rep$DaysSinceNephrectomy<0,NA,crea.rep$DaysSinceNephrectomy)


#RRT
sir.data$RRTDate<-sir.data$EntryDate 
sir.data$RRTDate<-ifelse(!sir.data$ReadCode  %in%  Dialysis1.csv$ReadCode,NA,sir.data$RRTDate) 
smalltab<-sir.data[!is.na(sir.data$RRTDate),c("PatientID","RRTDate")]
first<-smalltab %>%
    group_by(PatientID) %>%
    arrange(RRTDate) %>%
    slice(which.min(as.numeric(RRTDate))) %>%
as.data.frame
    head(first)
   
first$RRTDate<-as.Date(as.character(first$RRTDate),format="%Y%m%d")
crea.rep<-merge(crea.rep,first,all.x=TRUE)
crea.rep$DaysSinceRRT<-difftime(strptime(crea.rep$event.date,format="%Y-%m-%d"),strptime(crea.rep$RRTDate,format="%Y-%m-%d"),unit="days")
crea.rep$DaysSinceRRT<-ifelse(crea.rep$DaysSinceRRT<0,NA,crea.rep$DaysSinceRRT)
crea.rep$RRT<-ifelse(is.na(crea.rep$DaysSinceRRT),0,1)


#DIABETES
sir.data$DiabDate<-sir.data$EntryDate 
sir.data$DiabDate<-ifelse(!sir.data$ReadCode  %in%  Diabetes1.csv$ReadCode,NA,sir.data$DiabDate) 
smalltab<-sir.data[!is.na(sir.data$DiabDate),c("PatientID","DiabDate")]
first<-smalltab %>%
    group_by(PatientID) %>%
    arrange(DiabDate) %>%
    slice(which.min(as.numeric(DiabDate))) %>%
as.data.frame
    head(first)
   
first$DiabDate<-as.Date(as.character(first$DiabDate),format="%Y%m%d")
crea.rep<-merge(crea.rep,first,all.x=TRUE)
crea.rep$DaysSinceDiabetic<-difftime(strptime(crea.rep$event.date,format="%Y-%m-%d"),strptime(crea.rep$DiabDate,format="%Y-%m-%d"),unit="days")
crea.rep$DaysSinceDiabetic<-ifelse(crea.rep$DaysSinceDiabetic<0,NA,crea.rep$DaysSinceDiabetic)
crea.rep$Diabetes<-ifelse(is.na(crea.rep$DaysSinceDiabetic),0,1)


#ATRIAL FIBRILLATION
sir.data$AFDate<-sir.data$EntryDate 
sir.data$AFDate<-ifelse(!sir.data$ReadCode  %in%  AF1.csv$ReadCode,NA,sir.data$AFDate) 
smalltab<-sir.data[!is.na(sir.data$AFDate),c("PatientID","AFDate")]
first<-smalltab %>%
    group_by(PatientID) %>%
    arrange(AFDate) %>%
    slice(which.min(as.numeric(AFDate))) %>%
as.data.frame
    head(first)
    
crea.rep<-merge(crea.rep,as.data.frame(first),all.x=TRUE)
crea.rep$event.date<-as.Date(as.character(crea.rep$EntryDate),format="%Y%m%d")
crea.rep$AFDate<-as.Date(as.character(crea.rep$AFDate),format="%Y%m%d")
crea.rep$DaysSinceAF<-difftime(strptime(crea.rep$event.date,format="%Y-%m-%d"),strptime(crea.rep$AFDate,format="%Y-%m-%d"),unit="days")
crea.rep$DaysSinceAF<-ifelse(crea.rep$DaysSinceAF<0,NA,crea.rep$DaysSinceAF)
crea.rep$AF<-ifelse(is.na(crea.rep$DaysSinceAF),0,1)
summary(crea.rep$DaysSinceAF,na.rm=TRUE)


#IHD
sir.data$IHDDate<-sir.data$EntryDate 
sir.data$IHDDate<-ifelse(!(sir.data$ReadCode  %in%  IHD1.csv$ReadCode)&!(sir.data$ReadCode  %in%  IHD1.csv$MedCode),NA,sir.data$IHDDate) 
smalltab<-sir.data[!is.na(sir.data$IHDDate),c("PatientID","IHDDate")]
first<-smalltab %>%
    group_by(PatientID) %>%
    arrange(IHDDate) %>%
    slice(which.min(as.numeric(IHDDate))) %>%
as.data.frame
    head(first)
   
crea.rep<-merge(crea.rep,as.data.frame(first),all.x=TRUE)
crea.rep$IHDDate<-as.Date(as.character(crea.rep$IHDDate),format="%Y%m%d")
crea.rep$DaysSinceIHD<-difftime(strptime(crea.rep$event.date,format="%Y-%m-%d"),strptime(crea.rep$IHDDate,format="%Y-%m-%d"),unit="days")
crea.rep$DaysSinceIHD<-ifelse(crea.rep$DaysSinceIHD<0,NA,crea.rep$DaysSinceIHD)
crea.rep$IHD<-ifelse(is.na(crea.rep$DaysSinceIHD),0,1)
summary(crea.rep$DaysSinceIHD,na.rm=TRUE)


#PERIPHERAL VASCULAR DISEASE
sir.data$PVDDate<-sir.data$EntryDate 
sir.data$PVDDate<-ifelse(!sir.data$ReadCode  %in%  PVD1.csv$ReadCode,NA,sir.data$PVDDate) 
smalltab<-sir.data[!is.na(sir.data$PVDDate),c("PatientID","PVDDate")]
first<-smalltab %>%
    group_by(PatientID) %>%
    arrange(PVDDate) %>%
    slice(which.min(as.numeric(PVDDate))) %>%
as.data.frame
    head(first)
    
crea.rep<-merge(crea.rep,as.data.frame(first),all.x=TRUE)
crea.rep$PVDDate<-as.Date(as.character(crea.rep$PVDDate),format="%Y%m%d")
crea.rep$DaysSincePVD<-difftime(strptime(crea.rep$event.date,format="%Y-%m-%d"),strptime(crea.rep$PVDDate,format="%Y-%m-%d"),unit="days")
crea.rep$DaysSincePVD<-ifelse(crea.rep$DaysSincePVD<0,NA,crea.rep$DaysSincePVD)
crea.rep$PVD<-ifelse(is.na(crea.rep$DaysSincePVD),0,1)
summary(crea.rep$DaysSincePVD,na.rm=TRUE)


#RENAL MALIGNANCY
sir.data$RMALDate<-sir.data$EntryDate 
sir.data$RMALDate<-ifelse(!sir.data$ReadCode  %in%  RM1.csv$ReadCode,NA,sir.data$RMALDate) 
smalltab<-sir.data[!is.na(sir.data$RMALDate),c("PatientID","RMALDate")]
first<-smalltab %>%
    group_by(PatientID) %>%
    arrange(RMALDate) %>%
    slice(which.min(as.numeric(RMALDate))) %>%
as.data.frame
    head(first)
    
crea.rep<-merge(crea.rep,as.data.frame(first),all.x=TRUE)
crea.rep$event.date<-as.Date(as.character(crea.rep$EntryDate),format="%Y%m%d")
crea.rep$RMALDate<-as.Date(as.character(crea.rep$RMALDate),format="%Y%m%d")
crea.rep$DaysSinceRenMal<-difftime(strptime(crea.rep$event.date,format="%Y-%m-%d"),strptime(crea.rep$RMALDate,format="%Y-%m-%d"),unit="days")
crea.rep$DaysSinceRenMal<-ifelse(crea.rep$DaysSinceRenMal<0,NA,crea.rep$DaysSinceRenMal)
crea.rep$RenalMalignancy<-ifelse(is.na(crea.rep$DaysSinceRenMal),0,1)
summary(crea.rep$DaysSinceRenMal,na.rm=TRUE)


#RENAL TRANSPLANT
sir.data$RTDate<-sir.data$EntryDate 
sir.data$RTDate<-ifelse(!sir.data$ReadCode  %in%  transplant1.csv$ReadCode,NA,sir.data$RTDate) 
smalltab<-sir.data[!is.na(sir.data$RTDate),c("PatientID","RTDate")]
first<-smalltab %>%
   group_by(PatientID) %>%  
   arrange(RTDate) %>% 
   slice(which.min(as.numeric(RTDate))) %>%
as.data.frame    
head(first)
  
crea.rep<-merge(crea.rep,as.data.frame(first),all.x=TRUE)
crea.rep$event.date<-as.Date(as.character(crea.rep$EntryDate),format="%Y%m%d")
crea.rep$RTDate<-as.Date(as.character(crea.rep$RTDate),format="%Y%m%d")
crea.rep$DaysSinceRT<-difftime(strptime(crea.rep$event.date,format="%Y-%m-%d"),strptime(crea.rep$RTDate,format="%Y-%m-%d"),unit="days")
crea.rep$DaysSinceRT<-ifelse(crea.rep$DaysSinceRT<0,NA,crea.rep$DaysSinceRT)
crea.rep$RenalTransplant<-ifelse(is.na(crea.rep$DaysSinceRT),0,1)
summary(crea.rep$DaysSinceRT,na.rm=TRUE)

#DEATH
sir.data$Dead<-ifelse(sir.data$ReadCode  %in%  Death1.csv$ReadCode,1,0) 
smalltab<-sir.data[sir.data$Dead==1,c("PatientID","Dead")]
first<-smalltab %>%
   group_by(PatientID) %>%  
      slice(which.max(as.numeric(Dead))) %>%
as.data.frame    
head(first)
  
crea.rep<-merge(crea.rep,as.data.frame(first),all.x=TRUE)
summary(crea.rep$Dead,na.rm=TRUE)
#No death dates were associated with the extracted data for this analysis
#save(crea.rep, file = "crea.repongoing.rda")
 

#CLD
sir.data$CLDDate<-sir.data$EntryDate 
sir.data$CLDDate<-ifelse(!sir.data$ReadCode  %in%  Liver1.csv$ReadCode,NA,sir.data$CLDDate) 

smalltab<-sir.data[!is.na(sir.data$CLDDate) & sir.data$PatientID %in% crea.rep$PatientID,c("PatientID","CLDDate")]
first<-smalltab %>%
    group_by(PatientID) %>%
    arrange(CLDDate) %>%
    slice(1L) %>%
as.data.frame
first$CLDDate<-as.Date(as.character(first$CLDDate),format="%Y%m%d")
head(first)
 
crea.rep<-merge(crea.rep,first,all.x=TRUE)
crea.rep$event.date<-as.Date(as.character(crea.rep$EntryDate),format="%Y%m%d")
crea.rep$CLDDate<-as.Date(as.character(crea.rep$CLDDate),format="%Y-%m-%d")
crea.rep$DaysSinceCLD<-difftime(strptime(crea.rep$CLDDate,format="%Y-%m-%d"),strptime(crea.rep$event.date,format="%Y-%m-%d"),unit="days")
crea.rep$DaysSinceCLD<-as.numeric(crea.rep$DaysSinceCLD, units="days")
crea.rep$DaysSinceCLD<-ifelse(crea.rep$DaysSinceCLD<0,NA,crea.rep$DaysSinceCLD)
crea.rep$CLD<-ifelse(crea.rep$DaysSinceCLD>=0,1,0)


#MATERNITY 
allfiles = list.files(pattern="*1.csv")
for (i in 1:length(allfiles)) assign(allfiles[i], read.csv(allfiles[i]))

sir.data$Maternity<-ifelse(sir.data$ReadCode  %in%  Maternity1.csv$ReadCode,1,0) 
smalltab<-sir.data[sir.data$Maternity==1 & sir.data$PatientID %in% crea.rep$PatientID,c("PatientID","Maternity","EntryDate")]
names(smalltab)[3]<-"MatDate1"
smalltab<-smalltab[smalltab$MatDate1>="2007-01-01",]
first<-smalltab %>%
   group_by(PatientID) %>%  
      slice(which.min(as.numeric(MatDate1))) %>%
as.data.frame
head(first)

#MARKS  FIRST RECORDED PREGANANCY DATE FOR THE PATIENT'S FIRST POST-2007 BABY- NOT NECESSARILY THE FIRST CHILD.

names(smalltab)[3]<-"MatDate2"
smalltab<-merge(smalltab[,c(1,3)],first[,c(1,3)],all.x=TRUE)

smalltab$MatDate2<-as.Date(as.character(smalltab$MatDate2),"%Y%m%d")
smalltab$MatDate1<-as.Date(as.character(smalltab$MatDate1),"%Y%m%d")
smalltab$d<-as.numeric(smalltab$MatDate2-smalltab$MatDate1)
second<-smalltab[smalltab$d>=243,]

s<-second %>%
   group_by(PatientID) %>%  
      slice(which.min(as.numeric(MatDate2))) %>%
      as.data.frame
        head(s)
        
smalltab2<-sir.data[sir.data$Maternity==1 & sir.data$PatientID %in% crea.rep$PatientID,c("PatientID","Maternity","EntryDate")]
names(smalltab2)[3]<-"MatDate3"   
smalltab2$MatDate3<-as.Date(as.character(smalltab2$MatDate3),"%Y%m%d")
smalltab2<-smalltab2[smalltab2$MatDate3>="2007-01-01",]
smalltab2<-merge(smalltab2[,c(1,3)],s,all.x=TRUE)     
smalltab2<-unique(smalltab2)
smalltab2$d2<-as.numeric(smalltab2$MatDate3-smalltab2$MatDate2)
third<-smalltab2[smalltab2$d2>=243,]
third<-unique(third)
   
crea.rep<-merge(crea.rep,unique(smalltab2[,c(1,3,4)]),all.x=TRUE)
crea.rep$EstPregnant<-ifelse(crea.rep$event.date-crea.rep$MatDate1<243,1,NA)
crea.rep$EstPregnant<-ifelse(crea.rep$event.date-crea.rep$MatDate2<243,1,crea.rep$EstPregnant)

crea.rep$BNP_DF<-ifelse(crea.rep$BNPDateFlag==1,NA,crea.rep$BNP)
crea.rep$NTPROBNP_DF<-ifelse(crea.rep$NTPROBNPDateFlag==1,NA,crea.rep$NTPROBNP)
crea.rep$SBP_DF<-ifelse(crea.rep$SBPDateFlag==1,NA,crea.rep$SBP)
crea.rep$DBP_DF<-ifelse(crea.rep$DBPDateFlag==1,NA,crea.rep$DBP)
crea.rep$HeartRate_DF<-ifelse(crea.rep$HeartRateDateFlag==1,NA,crea.rep$HeartRate)
crea.rep$BMI_DF<-ifelse(crea.rep$BMIDateFlag==1,NA,crea.rep$BMI)
crea.rep$SerumAlbumin_DF<-ifelse(crea.rep$SerumAlbuminDateFlag==1,NA,crea.rep$SerumAlbumin)
crea.rep$UrineAlbumin_DF<-ifelse(crea.rep$UrineAlbuminDateFlag==1,NA,crea.rep$UrineAlbumin)
crea.rep$UACratio_DF<-ifelse(crea.rep$UACDateFlag==1,NA,crea.rep$UACratio)
crea.rep$Haemoglobin_DF<-ifelse(crea.rep$HaemDateFlag==1,NA,crea.rep$Haemoglobin)
crea.rep$MCV_DF<-ifelse(crea.rep$MCVDateFlag==1,NA,crea.rep$MCV)
crea.rep$SerPotassium_DF<-ifelse(crea.rep$SerPotDateFlag==1,NA,crea.rep$SerPotassium)
crea.rep$SerumSodium_DF<-ifelse(crea.rep$SerSodDateFlag==1,NA,crea.rep$SerumSodium)
crea.rep$BUN_DF<-ifelse(crea.rep$BUNDateFlag==1,NA,crea.rep$BUN)
crea.rep$UricAcid_DF<-ifelse(crea.rep$UricAcidDateFlag==1,NA,crea.rep$UricAcid)

save(crea.rep, file = "crea.repongoing.rda")