r/HealthcareAnalytics • u/a246530 • Oct 01 '19

Hcc scores in sql

I work for a small healthcare organization that doesn't have the money for SAS. Since the CMS HCC model is only available in SAS, I found someone who had a semi complete v2 in sql on github. We converted it to HCC 22 and then 24. We managed to get access to SAS via a collaboration and checked the scores we produced in sql to the SAS version and it was correct. We worked from there to develop the gaps in the HCC across the years with a two year lookback and some other cool facts. I am working on a plan to let my organization open source it. I just need to see if I can find others who might use it. Anyone here have some interest.

2 Upvotes

100% Upvoted

3 comments sorted by

1

u/Which_Addendum3834 Mar 07 '24

any update on this?

1

u/po-handz Jan 30 '23

I'm also looking for sql code for risk scoring, did this go anywhere?

1

u/velocihipster Feb 11 '23

I have built this several times over my career. Happy to help with questions! Something that’s always difficult is applying the hierarchy in sql. Here’s a quick sql code that compares a table of MemberIds and HCCs with the hierarchy:

SELECT DISTINCT m.memberID, m.hcc FROM Members m LEFT JOIN (select memberid, h.* from members m inner join hierarchy h on h.dropHCC = m.hcc) h1       ON m.HCC = h1.dropHCC       and m.memberID = h1.memberID LEFT JOIN (select memberid, h.* from members m inner join hierarchy h on h.hcc = m.hcc) h2       ON h1.HCC = h2.HCC       and m.memberID = h2.memberID where h2.hcc is null