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()

Example: Business cards typeset with LaTeX

So you enjoy the quality of a professional typesetting system? You got Avery labels, a working MikTeX and the ticket package installed...
You might find some assistance from a half criminal paranoid zealot system administrator, willing to guide you through a dinosaur kingdom of TeX ... but that kind of assistance might also just leave you with nothing.

It was easy to get the layout of the labels with the option zw32010, but how about page margins? I tried to set things straight with the layouts package (\usepackage{layouts}\currentpage \pagedesign), but then there was still some unwanted white space and margins...

To make things less complicated I decided to make a single card. The solution is a hack because it needs customization (with voffset and hoffset as you see n the TeX code below) but the adjustment is more straightforward, especially if you use the boxed option with ticket.

The card was converted to png with Ghostscript and I could easily print the business cards with Averys …