Skip to main content

Comorbidity indexes in SQL

Generating Elixhauser comorbidity index from Danish National Health Register as relational database. (ICD 10 Coding in SAS)

A lookup-table based version of Charlson comorbidity index I made in SQL. A similar approach can be applied to Elixhauser.

SELECT V_CPR,
MAX(EI1)+MAX(EI2)+MAX(EI3)+MAX(EI4)+MAX(EI5)+
MAX(EI6)+MAX(EI7)+MAX(EI8)+MAX(EI9)+MAX(EI10)+
MAX(EI11)+MAX(EI12)+MAX(EI13)+MAX(EI14)+MAX(EI15)+
MAX(EI16)+MAX(EI17)+MAX(EI18)+MAX(EI19)+MAX(EI20)+
MAX(EI21)+MAX(EI22)+MAX(EI23)+MAX(EI24)+MAX(EI25)+
MAX(EI26)+MAX(EI27)+MAX(EI28)+MAX(EI29)+MAX(EI30)+MAX(EI31) AS Elixhauser
FROM
(SELECT V_CPR,
-- Congestive Heart Failure
CASE WHEN DIAG LIKE 'DI099%'
OR DIAG LIKE 'DI110%'
OR DIAG LIKE 'DI130%'
OR DIAG LIKE 'DI132%'
OR DIAG LIKE 'DI255%'
OR DIAG LIKE 'DI420%'
OR DIAG LIKE 'DI425%'
OR DIAG LIKE 'DI426%'
OR DIAG LIKE 'DI427%'
OR DIAG LIKE 'DI428%'
OR DIAG LIKE 'DI429%'
OR DIAG LIKE 'DI43%'
OR DIAG LIKE 'DI50%'
OR DIAG LIKE 'DP290%' THEN 1 ELSE 0 END EI1
,
--Caridiac Arrhythmia
CASE WHEN DIAG LIKE 'DI441%'
OR DIAG LIKE 'DI442%'
OR DIAG LIKE 'DI443%'
OR DIAG LIKE 'DI456%'
OR DIAG LIKE 'DI459%'
OR DIAG LIKE 'DI47%'
OR DIAG LIKE 'DI48%'
OR DIAG LIKE 'DI49%'
OR DIAG LIKE 'DR000%'
OR DIAG LIKE 'DR001%'
OR DIAG LIKE 'DR008%'
OR DIAG LIKE 'DT821%'
OR DIAG LIKE 'DZ450%'
OR DIAG LIKE 'DZ950%' THEN 1 ELSE 0 END EI2
,
--Valvular Disease
CASE WHEN DIAG LIKE 'DA520%'
OR DIAG LIKE 'DI05%'
OR DIAG LIKE 'DI06%'
OR DIAG LIKE 'DI07%'
OR DIAG LIKE 'DI08%'
OR DIAG LIKE 'DI091%'
OR DIAG LIKE 'DI098%'
OR DIAG LIKE 'DI34%'
OR DIAG LIKE 'DI35%'
OR DIAG LIKE 'DI36%'
OR DIAG LIKE 'DI37%'
OR DIAG LIKE 'DI38%'
OR DIAG LIKE 'DI39%'
OR DIAG LIKE 'DQ230%'
OR DIAG LIKE 'DQ231%'
OR DIAG LIKE 'DQ232%'
OR DIAG LIKE 'DQ233%'
OR DIAG LIKE 'DZ952%'
OR DIAG LIKE 'DZ953%'
OR DIAG LIKE 'DZ954%' THEN 1 ELSE 0 END EI3
,
--Pulmonary Circulation Disorders
CASE WHEN DIAG LIKE 'DI26%'
OR DIAG LIKE 'DI27%'
OR DIAG LIKE 'DI280%'
OR DIAG LIKE 'DI288%'
OR DIAG LIKE 'DI289%' THEN 1 ELSE 0 END EI4
,
--Peripheral Vascular Disorders
CASE WHEN DIAG LIKE 'DI70%'
OR DIAG LIKE 'DI71%'
OR DIAG LIKE 'DI731%'
OR DIAG LIKE 'DI738%'
OR DIAG LIKE 'DI739%'
OR DIAG LIKE 'DI771%'
OR DIAG LIKE 'DI790%'
OR DIAG LIKE 'DI792%'
OR DIAG LIKE 'DK551%'
OR DIAG LIKE 'DK558%'
OR DIAG LIKE 'DK559%'
OR DIAG LIKE 'DZ958%'
OR DIAG LIKE 'DZ959%' THEN 1 ELSE 0 END EI5
,
--Hypertension Uncomlicated
CASE WHEN DIAG LIKE 'DI10%' THEN 1 ELSE 0 END EI6
,
--Hypertension comlicated
CASE WHEN DIAG LIKE 'DI11%'
OR DIAG LIKE 'DI12%'
OR DIAG LIKE 'DI13%'
OR DIAG LIKE 'DI15%' THEN 1 ELSE 0 END EI7
,
--Paralysis
CASE WHEN DIAG LIKE 'DG041%'
OR DIAG LIKE 'DG114%'
OR DIAG LIKE 'DG801%'
OR DIAG LIKE 'DG802%'
OR DIAG LIKE 'DG81%'
OR DIAG LIKE 'DG82%'
OR DIAG LIKE 'DG830%'
OR DIAG LIKE 'DG831%'
OR DIAG LIKE 'DG832%'
OR DIAG LIKE 'DG833%'
OR DIAG LIKE 'DG834%' THEN 1 ELSE 0 END EI8
,
-- Other Neurological Disorders
CASE WHEN DIAG LIKE 'DG10%'
OR DIAG LIKE 'DG11%'
OR DIAG LIKE 'DG12%'
OR DIAG LIKE 'DG13%'
OR DIAG LIKE 'DG20%'
OR DIAG LIKE 'DG21%'
OR DIAG LIKE 'DG22%'
OR DIAG LIKE 'DG254%'
OR DIAG LIKE 'DG255%'
OR DIAG LIKE 'DG312%'
OR DIAG LIKE 'DG318%'
OR DIAG LIKE 'DG319%'
OR DIAG LIKE 'DG32%'
OR DIAG LIKE 'DG35%'
OR DIAG LIKE 'DG36%'
OR DIAG LIKE 'DG37%'
OR DIAG LIKE 'DG40%'
OR DIAG LIKE 'DG41%'
OR DIAG LIKE 'DG931%'
OR DIAG LIKE 'DG934%'
OR DIAG LIKE 'DR470%'
OR DIAG LIKE 'DR56%' THEN 1 ELSE 0 END EI9
,
--Chronic Pulmonary Disease
CASE WHEN DIAG LIKE 'DI278%'
OR DIAG LIKE 'DI279%'
OR DIAG LIKE 'DJ40%'
OR DIAG LIKE 'DJ41%'
OR DIAG LIKE 'DJ42%'
OR DIAG LIKE 'DJ43%'
OR DIAG LIKE 'DJ44%'
OR DIAG LIKE 'DJ45%'
OR DIAG LIKE 'DJ46%'
OR DIAG LIKE 'DJ47%'
OR DIAG LIKE 'DJ60%'
OR DIAG LIKE 'DJ61%'
OR DIAG LIKE 'DJ62%'
OR DIAG LIKE 'DJ63%'
OR DIAG LIKE 'DJ64%'
OR DIAG LIKE 'DJ65%'
OR DIAG LIKE 'DJ66%'
OR DIAG LIKE 'DJ67%'
OR DIAG LIKE 'DJ684%'
OR DIAG LIKE 'DJ701%'
OR DIAG LIKE 'DJ703%' THEN 1 ELSE 0 END EI10
,
--Diabetes Uncomplicated
CASE WHEN DIAG LIKE 'DE100%'
OR DIAG LIKE 'DE101%'
OR DIAG LIKE 'DE109%'
OR DIAG LIKE 'DE110%'
OR DIAG LIKE 'DE111%'
OR DIAG LIKE 'DE119%'
OR DIAG LIKE 'DE120%'
OR DIAG LIKE 'DE121%'
OR DIAG LIKE 'DE129%'
OR DIAG LIKE 'DE130%'
OR DIAG LIKE 'DE131%'
OR DIAG LIKE 'DE139%'
OR DIAG LIKE 'DE140%'
OR DIAG LIKE 'DE141%'
OR DIAG LIKE 'DE149%' THEN 1 ELSE 0 END EI11
,
--Diabetes Complicated
CASE WHEN DIAG LIKE 'DE102%'
OR DIAG LIKE 'DE103%'
OR DIAG LIKE 'DE104%'
OR DIAG LIKE 'DE105%'
OR DIAG LIKE 'DE106%'
OR DIAG LIKE 'DE107%'
OR DIAG LIKE 'DE108%'
OR DIAG LIKE 'DE112%'
OR DIAG LIKE 'DE113%'
OR DIAG LIKE 'DE114%'
OR DIAG LIKE 'DE115%'
OR DIAG LIKE 'DE116%'
OR DIAG LIKE 'DE117%'
OR DIAG LIKE 'DE118%'
OR DIAG LIKE 'DE122%'
OR DIAG LIKE 'DE123%'
OR DIAG LIKE 'DE124%'
OR DIAG LIKE 'DE125%'
OR DIAG LIKE 'DE126%'
OR DIAG LIKE 'DE127%'
OR DIAG LIKE 'DE128%'
OR DIAG LIKE 'DE132%'
OR DIAG LIKE 'DE133%'
OR DIAG LIKE 'DE134%'
OR DIAG LIKE 'DE135%'
OR DIAG LIKE 'DE136%'
OR DIAG LIKE 'DE137%'
OR DIAG LIKE 'DE138%'
OR DIAG LIKE 'DE142%'
OR DIAG LIKE 'DE143%'
OR DIAG LIKE 'DE144%'
OR DIAG LIKE 'DE145%'
OR DIAG LIKE 'DE146%'
OR DIAG LIKE 'DE147%'
OR DIAG LIKE 'DE148%' THEN 1 ELSE 0 END EI12
,
--Hypothyroidism
CASE WHEN DIAG LIKE 'DE00%'
OR DIAG LIKE 'DE01%'
OR DIAG LIKE 'DE02%'
OR DIAG LIKE 'DE03%'
OR DIAG LIKE 'DE890%' THEN 1 ELSE 0 END EI13
,
--Renal Failure
CASE WHEN DIAG LIKE 'DI120%'
OR DIAG LIKE 'DI131%'
OR DIAG LIKE 'DN18%'
OR DIAG LIKE 'DN19%'
OR DIAG LIKE 'DN250%'
OR DIAG LIKE 'DZ490%'
OR DIAG LIKE 'DZ491%'
OR DIAG LIKE 'DZ492%'
OR DIAG LIKE 'DZ940%'
OR DIAG LIKE 'DZ992%' THEN 1 ELSE 0 END EI14
,
--Liver Disease
CASE WHEN DIAG LIKE 'DB18%'
OR DIAG LIKE 'DI85%'
OR DIAG LIKE 'DI864%'
OR DIAG LIKE 'DI982%'
OR DIAG LIKE 'DK70%'
OR DIAG LIKE 'DK711%'
OR DIAG LIKE 'DK713%'
OR DIAG LIKE 'DK714%'
OR DIAG LIKE 'DK715%'
OR DIAG LIKE 'DK717%'
OR DIAG LIKE 'DK72%'
OR DIAG LIKE 'DK73%'
OR DIAG LIKE 'DK74%'
OR DIAG LIKE 'DK760%'
OR DIAG LIKE 'DK762%'
OR DIAG LIKE 'DK763%'
OR DIAG LIKE 'DK764%'
OR DIAG LIKE 'DK765%'
OR DIAG LIKE 'DK766%'
OR DIAG LIKE 'DK767%'
OR DIAG LIKE 'DK768%'
OR DIAG LIKE 'DK769%'
OR DIAG LIKE 'DZ944%' THEN 1 ELSE 0 END EI15
,
--Peptic Ulcer Disease excluding bleeding
CASE WHEN DIAG LIKE 'DK257%'
OR DIAG LIKE 'DK259%'
OR DIAG LIKE 'DK267%'
OR DIAG LIKE 'DK269%'
OR DIAG LIKE 'DK277%'
OR DIAG LIKE 'DK279%'
OR DIAG LIKE 'DK287%'
OR DIAG LIKE 'DK289%' THEN 1 ELSE 0 END EI16
,
--AIDS/HIV
CASE WHEN DIAG LIKE 'DB20%'
OR DIAG LIKE 'DB21%'
OR DIAG LIKE 'DB22%'
OR DIAG LIKE 'DB24%' THEN 1 ELSE 0 END EI17
,
--Lymphoma
CASE WHEN DIAG LIKE 'DC81%'
OR DIAG LIKE 'DC82%'
OR DIAG LIKE 'DC83%'
OR DIAG LIKE 'DC84%'
OR DIAG LIKE 'DC85%'
OR DIAG LIKE 'DC88%'
OR DIAG LIKE 'DC96%'
OR DIAG LIKE 'DC900%'
OR DIAG LIKE 'DC902%' THEN 1 ELSE 0 END EI18
,
--Metastatic Cancer
CASE WHEN DIAG LIKE 'DC77%'
OR DIAG LIKE 'DC78%'
OR DIAG LIKE 'DC79%'
OR DIAG LIKE 'DC80%' THEN 1 ELSE 0 END EI19
,
--Solid Tumor without Metastasis
CASE WHEN DIAG LIKE 'DC00%'
OR DIAG LIKE 'DC01%'
OR DIAG LIKE 'DC02%'
OR DIAG LIKE 'DC03%'
OR DIAG LIKE 'DC04%'
OR DIAG LIKE 'DC05%'
OR DIAG LIKE 'DC06%'
OR DIAG LIKE 'DC07%'
OR DIAG LIKE 'DC08%'
OR DIAG LIKE 'DC09%'
OR DIAG LIKE 'DC10%'
OR DIAG LIKE 'DC11%'
OR DIAG LIKE 'DC12%'
OR DIAG LIKE 'DC13%'
OR DIAG LIKE 'DC14%'
OR DIAG LIKE 'DC15%'
OR DIAG LIKE 'DC16%'
OR DIAG LIKE 'DC17%'
OR DIAG LIKE 'DC18%'
OR DIAG LIKE 'DC19%'
OR DIAG LIKE 'DC20%'
OR DIAG LIKE 'DC21%'
OR DIAG LIKE 'DC22%'
OR DIAG LIKE 'DC23%'
OR DIAG LIKE 'DC24%'
OR DIAG LIKE 'DC25%'
OR DIAG LIKE 'DC26%'
OR DIAG LIKE 'DC30%'
OR DIAG LIKE 'DC31%'
OR DIAG LIKE 'DC32%'
OR DIAG LIKE 'DC33%'
OR DIAG LIKE 'DC34%'
OR DIAG LIKE 'DC37%'
OR DIAG LIKE 'DC38%'
OR DIAG LIKE 'DC39%'
OR DIAG LIKE 'DC40%'
OR DIAG LIKE 'DC41%'
OR DIAG LIKE 'DC43%'
OR DIAG LIKE 'DC45%'
OR DIAG LIKE 'DC46%'
OR DIAG LIKE 'DC47%'
OR DIAG LIKE 'DC48%'
OR DIAG LIKE 'DC49%'
OR DIAG LIKE 'DC50%'
OR DIAG LIKE 'DC51%'
OR DIAG LIKE 'DC52%'
OR DIAG LIKE 'DC53%'
OR DIAG LIKE 'DC54%'
OR DIAG LIKE 'DC55%'
OR DIAG LIKE 'DC56%'
OR DIAG LIKE 'DC57%'
OR DIAG LIKE 'DC58%'
OR DIAG LIKE 'DC60%'
OR DIAG LIKE 'DC61%'
OR DIAG LIKE 'DC62%'
OR DIAG LIKE 'DC63%'
OR DIAG LIKE 'DC64%'
OR DIAG LIKE 'DC65%'
OR DIAG LIKE 'DC66%'
OR DIAG LIKE 'DC67%'
OR DIAG LIKE 'DC68%'
OR DIAG LIKE 'DC69%'
OR DIAG LIKE 'DC70%'
OR DIAG LIKE 'DC71%'
OR DIAG LIKE 'DC72%'
OR DIAG LIKE 'DC73%'
OR DIAG LIKE 'DC74%'
OR DIAG LIKE 'DC75%'
OR DIAG LIKE 'DC76%'
OR DIAG LIKE 'DC97%' THEN 1 ELSE 0 END EI20
,
--Rheumatoid Arthritis/collagen
CASE WHEN DIAG LIKE 'DL940%'
OR DIAG LIKE 'DL941%'
OR DIAG LIKE 'DL943%'
OR DIAG LIKE 'DM05%'
OR DIAG LIKE 'DM06%'
OR DIAG LIKE 'DM08%'
OR DIAG LIKE 'DM120%'
OR DIAG LIKE 'DM123%'
OR DIAG LIKE 'DM30%'
OR DIAG LIKE 'DM310%'
OR DIAG LIKE 'DM311%'
OR DIAG LIKE 'DM312%'
OR DIAG LIKE 'DM313%'
OR DIAG LIKE 'DM32%'
OR DIAG LIKE 'DM33%'
OR DIAG LIKE 'DM34%'
OR DIAG LIKE 'DM35%'
OR DIAG LIKE 'DM45%'
OR DIAG LIKE 'DM461%'
OR DIAG LIKE 'DM468%'
OR DIAG LIKE 'DM469%' THEN 1 ELSE 0 END EI21
,
--Coagulopathy
CASE WHEN DIAG LIKE 'DD65%'
OR DIAG LIKE 'DD66%'
OR DIAG LIKE 'DD67%'
OR DIAG LIKE 'DD68%'
OR DIAG LIKE 'DD691%'
OR DIAG LIKE 'DD693%'
OR DIAG LIKE 'DD694%'
OR DIAG LIKE 'DD695%'
OR DIAG LIKE 'DD696%' THEN 1 ELSE 0 END EI22
,
--Obesity
CASE WHEN DIAG LIKE 'DE66%' THEN 1 ELSE 0 END EI23
,
--Weight Loss
CASE WHEN DIAG LIKE 'DE40%'
OR DIAG LIKE 'DE41%'
OR DIAG LIKE 'DE42%'
OR DIAG LIKE 'DE43%'
OR DIAG LIKE 'DE44%'
OR DIAG LIKE 'DE45%'
OR DIAG LIKE 'DE46%'
OR DIAG LIKE 'DR634%'
OR DIAG LIKE 'DR64%' THEN 1 ELSE 0 END EI24
,
--Fluid and Electrolyte Disorders
CASE WHEN DIAG LIKE 'DE222%'
OR DIAG LIKE 'DE86%'
OR DIAG LIKE 'DE87%' THEN 1 ELSE 0 END EI25

,
--Blood Loss Anemia
CASE WHEN DIAG LIKE 'DD500%' THEN 1 ELSE 0 END EI26
,
--Deficiency Anemia
CASE WHEN DIAG LIKE 'DD508%'
OR DIAG LIKE 'DD509%'
OR DIAG LIKE 'DD51%'
OR DIAG LIKE 'DD52%'
OR DIAG LIKE 'DD53%' THEN 1 ELSE 0 END EI27
,
--Alcohol Abuse
CASE WHEN DIAG LIKE 'DF10%'
OR DIAG LIKE 'DE52%'
OR DIAG LIKE 'DG621%'
OR DIAG LIKE 'DI426%'
OR DIAG LIKE 'DK292%'
OR DIAG LIKE 'DK700%'
OR DIAG LIKE 'DK703%'
OR DIAG LIKE 'DK709%'
OR DIAG LIKE 'DT51%'
OR DIAG LIKE 'DZ502%'
OR DIAG LIKE 'DZ714%'
OR DIAG LIKE 'DZ721%' THEN 1 ELSE 0 END EI28
,
--Drug Abuse
CASE WHEN DIAG LIKE 'DF11%'
OR DIAG LIKE 'DF12%'
OR DIAG LIKE 'DF13%'
OR DIAG LIKE 'DF14%'
OR DIAG LIKE 'DF15%'
OR DIAG LIKE 'DF16%'
OR DIAG LIKE 'DF18%'
OR DIAG LIKE 'DF19%'
OR DIAG LIKE 'DZ715%'
OR DIAG LIKE 'DZ722%' THEN 1 ELSE 0 END EI29
,
--Psychoses
CASE WHEN DIAG LIKE 'DF20%'
OR DIAG LIKE 'DF22%'
OR DIAG LIKE 'DF23%'
OR DIAG LIKE 'DF24%'
OR DIAG LIKE 'DF25%'
OR DIAG LIKE 'DF28%'
OR DIAG LIKE 'DF29%'
OR DIAG LIKE 'DF302%'
OR DIAG LIKE 'DF312%'
OR DIAG LIKE 'DF315%' THEN 1 ELSE 0 END EI30
,
--Depression
CASE WHEN DIAG LIKE 'DF204%'
OR DIAG LIKE 'DF313%'
OR DIAG LIKE 'DF314%'
OR DIAG LIKE 'DF315%'
OR DIAG LIKE 'DF32%'
OR DIAG LIKE 'DF33%'
OR DIAG LIKE 'DF341%'
OR DIAG LIKE 'DF412%'
OR DIAG LIKE 'DF432%' THEN 1 ELSE 0 END EI31
FROM
(
SELECT V_CPR, C_ADIAG AS DIAG, D_INDDTO AS DTO
FROM [Dmart].[lpr_pd].[V_LPR_PSYK_ADMIN]
UNION
SELECT V_CPR, C_DIAG AS DIAG, D_INDDTO AS DTO
FROM [Dmart].[lpr_pd].[V_LPR_PSYK_DIAG] AS P,[Dmart].[lpr_pd].[V_LPR_PSYK_ADMIN] AS A
WHERE P.V_RECNUM=A.K_RECNUM
UNION
SELECT V_CPR, C_ADIAG AS DIAG, D_INDDTO AS DTO
FROM [Dmart].[lpr_pd].[V_LPR_ADMIN] AS A
UNION
SELECT V_CPR, C_DIAG AS DIAG, D_INDDTO AS DTO
FROM [Dmart].[lpr_pd].[V_LPR_DIAG] AS P,[Dmart].[lpr_pd].[V_LPR_ADMIN] AS A
WHERE P.V_RECNUM=A.K_RECNUM
) AS K
WHERE DTO>=DATEADD(year,-2,CONVERT (date, GETDATE()))
) AS D
GROUP BY V_CPR
ORDER BY Elixhauser DESC

Comments

Popular posts from this blog

HackRF on Windows 8

This technical note is based on an extract from thread. I have made several changes and added recommendations. I have experienced lot of latency using GnuRadio and HackRF on Pentoo Linux, so I wanted to try out GnuRadio on Windows.



HackRF One is a transceiver, so besides SDR capabilities, it can also transmit signals, inkluding sweeping a given range, uniform and Gaussian signals. Pentoo Linux provides the most direct access to HackRF and toolboxes. Install Pentoo Linux on a separate drive, then you can use osmocom_siggen from a terminal to transmit signals such as near-field GSM bursts, which will only be detectable within a meter.









Installation of MGWin and cmake: Download and install the following packages:
- MinGW Setup (Go to the Installer directory and download setup file)
- CMake (I am using CMake 3.2.2 and I installed it in C:\CMake, this path is important in the commands we must send in the MinGW shell)
Download and extract the packages respectively in the path C:\MinGW\msys\…

Example: Beeswarm plot in R

library(foreign)

data <- read.dta("C:/Users/hellmund/Documents/MyStataDataFile.dta")

names(data)

install.packages('beeswarm')

library(beeswarm)

levels(data$group)

png(file="C:/Users/hellmund/Documents/il6.png", bg="transparent")

beeswarm(data$il6~data$group,data=data, method=c("swarm"),pch=16,pwcol=data$Gender,xlab='',ylab='il6',ylim=c(0,20))

legend('topright',legend=levels(data$Gender),title='Gender',pch=16,col=2:1)

boxplot(data$il6~data$group, data=data, add = T, names = c("","",""), col="#0000ff22")

dev.off()

Real world split-plot designs

Google Earth picture from a blog on statistics. A real world example near Christchurch (NZ) of a split-plot design. Today things have completely changed on location as the forest has grown considerably. Google Earth coordinate link.