Create a dataset at the state, age group, and gender level. Count the total number of lab claims and the total cost of those lab claims.

INSTRUCTIONS TO CANDIDATES

ANSWER ALL QUESTIONS

Total Medicare Lab Cost Report

Create a dataset at the state, age group, and gender level. Count the total number of lab claims and the total cost of those lab claims.

Methodology

1.       Using the NTL_ADDR file, identify the state that a beneficiary resided in for each month of 2019

a.       Variables to use: bene_id, bene_state_cd, efctv_dt, end_dt

b.      We’re interested in Maryland beneficiaries (state code = “21”), so categorize the states as ‘Maryland’ vs “everything else”

2.       Using the MBSF_ABCD file, determine a beneficiary’s age and gender

a.       Variables to use: bene_id, AGE_AT_END_REF_YR, SEX_IDENT_CD

b.      Age groups: <65, 65-74, 75-84, 85+

c.       Gender: 1 = Male, 2 = Female, everything else = Other

3.       Again, using the MBSF_ABCD file, determine the following for each month of 2019:

a.       If the beneficiary is part A enrolled, part B enrolled, both A and B enrolled, or part C enrolled

     i.      Use the MDCR_ENTLMT_BUYIN_IND for Part A/B enrollment for each month:

1.       3, C = A and B enrolled

2.       1, A = A enrolled

3.       2, B = B enrolled

   ii.      Use HMO_IND monthly var for part C enrollment:

1.       0, 4 = not Part C

2.       Everything else = part C enrolled

4.       Merge your output together to create a single file that contains the bene_id, gender, age group, state for each month, and enrollment for each month

5.       Using the Outpatient_Claims data, identify the lab claims:

a.       Variables to use: bene_id, CLM_PMT_AMT, CLM_FAC_TYPE_CD, CLM_SRVC_CLSFCTN_TYPE_CD

b.      Create a ‘bill type’ variable by concatenating the CLM_FAC_TYPE_CD and CLM_SRVC_CLSFCTN_TYPE_CD variables

c.       If the bill type is 13 or 14, then the claim is a lab claim

d.      Count the total number of lab claims and sum the lab cost for each beneficiary

6.       Merge your beneficiary file with lab claim and lab costs with the file created in step 4

7.       Summarize the step 6 dataset at the state, age group, and gender level to create final dataset and export to Excel

8.       Use a pivot table on your final dataset to display your results in a useful way. We want this table to be flexible so, if the client wants to see lab cost for particular age group of gender, they can easily do so. However, at the same time, we don’t want the table to be too big with too much information.

Attachments:

Related Questions

. The fundamental operations of create, read, update, and delete (CRUD) in either Python or Java

CS 340 Milestone One Guidelines and Rubric  Overview: For this assignment, you will implement the fundamental operations of create, read, update,

. Develop a program to emulate a purchase transaction at a retail store. This  program will have two classes, a LineItem class and a Transaction class

Retail Transaction Programming Project  Project Requirements:  Develop a program to emulate a purchase transaction at a retail store. This

. The following program contains five errors. Identify the errors and fix them

7COM1028   Secure Systems Programming   Referral Coursework: Secure

. Accepts the following from a user: Item Name Item Quantity Item Price Allows the user to create a file to store the sales receipt contents

Create a GUI program that:Accepts the following from a user:Item NameItem QuantityItem PriceAllows the user to create a file to store the sales receip

. The final project will encompass developing a web service using a software stack and implementing an industry-standard interface. Regardless of whether you choose to pursue application development goals as a pure developer or as a software engineer

CS 340 Final Project Guidelines and Rubric  Overview The final project will encompass developing a web service using a software stack and impleme