Search

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

0 views

Recent Posts

See All

©2020 by Danish Institute for Data Science. Proudly created with Wix.com