1) In CLAIMS table, by selecting those MemberIDs with year=Y2 , the members are those filed claims in Y2.

(also see Year definition).

2) DaysInHospital_Y2 contains only those eligible members in Y2.

If the above understanding is correct, then the MemberID in 1) should match with those in 2). But this is not true.

Any explanation?

Tim

One could be eligible, but without filing a claim?

One could indeed.

We were given 113,000 members, per the Member master file.

There are 76,038 member records in DaysInHospital_Y2,

               71,435 for DaysInHospital_Y3,

       and  70,942 for DaysInHospital_Y4 (aka Target).

Which is to say, in any given year, 30+% of the members leave no footprint in the DaysInHospital summary files  that were prepared for us.

What to make of, or do with, that nugget of information, if anything, is part of the homework assignment.

HTH

You will find that the MemberIDs in (Claims where year='y1') match the MemberIDs in DIH_Y2. Also, the MemberIDs in (Claims where year='y2') match the MemberIDs in DIH_Y3. The MemberIDs in (Claims where year='y3') match the MemberIDs in Target.

Thanks Mark

The fact [ The MemberIDs in (Claims where year='y3') match the MemberIDs in Target] actually threw me, because in real life we'd have folks who'd just joined us without a prior claims history with us. That's churn and that's a fact of life.

It'd be neat for HPN to take its secure data after the competition and see if it can use demographic and payor data to extend the winning algorithm to new patients without prior claims variables.

Another observation that is related to this topic: There are some MemberIDs having claims in Year 1 (therefore, they are in DaysInHospital Year 2 table), However, they do not have any claims in Year 2. They are not in DaysINHospital Year 3. They do have claims in Year 3 and they are in the target table. If they are not the eligible member in Year 3, how come they have claims in Year 3? It seems a data preparation issue here.

Can a member file claims in a year but is not eligible for the same year? There are many of these cases 

Not likely. The most likely reason a member would have claims in year 1 and 3, be in DaysInHospital for year 2 and no claims in year 2 is that they had no claims in year 2. In other words, I'm guessing, that they all stayed zero days in hospital in year 2.

According to http://www.heritageprovidernetwork.com/?p=enrollment, HPN had nearly 500,000 members in 2010. Unless year 1 is 1995, majority of these members are not included in Members table because they made no claims in year 1, 2 and 3.

BTW are there any Gplusers here. I have 150 invites left and I'll be happy to send you one. You can contact me by vising http://gplus.to/dallakyans; click on Send an email on the left. Cheers!

" In other words, I'm guessing, that they all stayed zero days in hospital in year 2."

This is just a guess. Can we get confirmed? For example, memeber ID 10045694. It has claims in Year 1 and Year 3, therefore, we can find this memberID in DIHY2 and Target. But we can not find it in DIHY3 file. Can we assum DIH in Year 3 is zero?

You can assume anything if it improves your score.  :-)  This competition is about predicting, not explaining.