In this assignment, you will be working with a data set on patients with CHF. This data has been pulled from a MedPAR dataset. MedPAR is a file that is distributed by CMS (the entity that administers the Medicare program). It contains detailed information on inpatient hospital and SNF stays for Medicare beneficiaries, providing a valuable resource for understanding use and costs in the Medicare system.
Data Dictionary Directions:
- First, download the CHF Database excel file Download CHF Database excel file. After opening the file, you will see there are two sheets titled Data and Data Dictionary.
- Open the Data Dictionary sheet (second tab). You will use this sheet as your template to complete the first part of your assignment (Data Dictionary).
- Use the MedPAR data dictionary file Download MedPAR data dictionary fileto find and complete the definitions for the coded variables on your template. All cells with an asterisk (*) and a yellow highlighted background need to be filled out (column D, rows 3-62)
- For cells labeled Description*, you need to write a data definition. It should be similar in format to the definitions provided in the MedPAR file, but it must be in your own words – you cannot copy and paste these definitions. You will not receive any points for definitions that are copied/pasted.
- Example: I need to write my definition for Length of Stay. From the MedPAR file, the definition is, "This field specifies the total length of a patient's hospital stay from the date of admission to the date of discharge." I may write something like, "This field specifies the patient's length of stay from admission to discharge" or "This field specifies the total number of days that the patient was admitted to the hospital." Similar phrasing is fine, it just cannot be an exact copy/paste.
- For cells labeled with only an asterick *, you need to use the MedPAR file to locate the definition for the specified values.
- Example: I need to find the definition for one of the values for Age. First, I need to find the data element "age" on the MedPar file (page 11 of the pdf) and then identify what an age value of "2" represents.
- For the DRG definition (cell D62), you need to come up with your own definition. It should be similar in format to the definitions provided in the file.
- Once you have completed your data dictionary, save and rename the Excel file in accordance with the format outlined in the assignment directions. You will use this file again in the second part of the assignment.
Data Analysis Directions:
- Imagine you are explaining a data dictionary to a new intern at your hospital who will be assisting with a research project involving patients with a diagnosis of CHF. In a word document, write a paragraph (150-200 words) explaining what a data dictionary is, its importance, and how it might be used in this upcoming research project.
- Next, open the CHF Data file and navigate to the first sheet titled Data. Select three patients with the SAME LENGTH OF STAY (LOS). In your word document, describe each patient in a narrative format. When writing your narrative, you need to use your data dictionary to find the actual values for your three patients. (Example: If the age value for the patient is 3, then the patient's actual age is between 45-64. If the patient has a sex value of 1, they should be described as male. So you would write, "Patient XX is a male between the ages of 45 and 64, with XX diagnoses. His total charges were XX, and the amount reimbursed was XX. He was admitted from…" etc.). Your description should include the following information:
- Patient ID (column A)
- Age (column C)
- Sex (column D)
- Total charges (column H)
- Amount reimbursed (column E)
- Number of diagnoses (column I)
- Admit source (column J)
- Discharge destination (column K)
- Calculate the difference between the total charges and the amount reimbursed for each patient. Briefly compare the results (2-3 sentences). Are the amounts the same or different? Speculate on the reasons behind similarities and discrepancies.
Data
PatientID | LOS | Age | Sex | AmtReim | TotAccomChg | TotDeptChg | TotChg | NumDxCodes | AdmSrc | DischDest | DrgCode |
1 | 8 | 4 | 1 | 10372 | 19632 | 8144 | 27776 | 9 | 4 | 06 | 291 |
2 | 5 | 4 | 2 | 10306 | 12270 | 23259 | 35529 | 9 | 1 | 01 | 291 |
3 | 2 | 4 | 2 | 10306 | 4908 | 12834 | 17742 | 9 | 1 | 01 | 291 |
4 | 2 | 5 | 2 | 10372 | 4908 | 5459 | 10367 | 9 | 7 | 01 | 291 |
5 | 2 | 3 | 2 | 6200 | 4908 | 8893 | 13801 | 7 | 1 | 01 | 293 |
6 | 5 | 4 | 1 | 6186 | 12270 | 25758 | 38028 | 6 | 7 | 01 | 293 |
7 | 7 | 6 | 2 | 10372 | 18816 | 14516 | 33332 | 9 | 7 | 06 | 291 |
8 | 6 | 7 | 2 | 7224 | 14724 | 8105 | 22829 | 4 | 1 | 01 | 293 |
9 | 4 | 7 | 2 | 6200 | 9816 | 22244 | 32060 | 9 | 1 | 06 | 293 |
10 | 8 | 7 | 1 | 6200 | 19632 | 6506 | 26138 | 8 | 4 | 01 | 293 |
11 | 6 | 9 | 1 | 7328 | 14724 | 9108 | 23832 | 9 | 1 | 06 | 292 |
12 | 7 | 3 | 2 | 7307 | 18816 | 17622 | 36438 | 9 | 1 | 62 | 292 |
13 | 8 | 3 | 1 | 10372 | 21816 | 15152 | 36968 | 9 | 7 | 06 | 291 |
14 | 17 | 4 | 2 | 10306 | 59718 | 39275 | 98993 | 9 | 4 | 06 | 291 |
15 | 3 | 7 | 1 | 6200 | 7362 | 23293 | 30655 | 9 | 7 | 06 | 293 |
16 | 6 | 8 | 2 | 7328 | 14724 | 11008 | 25732 | 9 | 1 | 06 | 292 |
17 | 5 | 8 | 2 | 7328 | 12270 | 7951 | 20221 | 9 | 1 | 06 | 292 |
18 | 3 | 5 | 1 | 9604 | 8181 | 6400 | 14581 | 9 | 1 | 01 | 291 |
19 | 3 | 3 | 1 | 7307 | 7362 | 15891 | 23253 | 9 | 1 | 01 | 292 |
20 | 18 | 8 | 2 | 9348 | 44172 | 44636 | 88808 | 9 | 1 | 62 | 291 |
21 | 8 | 4 | 1 | 10306 | 22632 | 16528 | 39160 | 9 | 4 | 62 | 291 |
22 | 3 | 9 | 2 | 7111 | 7362 | 7642 | 15004 | 9 | 6 | 03 | 291 |
23 | 3 | 5 | 1 | 8352 | 7362 | 21648 | 29010 | 9 | 4 | 01 | 292 |
24 | 6 | 5 | 2 | 7224 | 14724 | 13808 | 28532 | 7 | 1 | 06 | 293 |
25 | 3 | 3 | 1 | 7125 | 7362 | 7051 | 14413 | 9 | 7 | 06 | 292 |
26 | 3 | 3 | 1 | 7307 | 7362 | 13726 | 21088 | 9 | 7 | 01 | 292 |
27 | 7 | 9 | 2 | 7328 | 17178 | 11598 | 28776 | 9 | 5 | 01 | 292 |
28 | 6 | 9 | 2 | 10372 | 16362 | 9502 | 25864 | 9 | 7 | 03 | 291 |
29 | 6 | 6 | 1 | 7224 | 14724 | 3841 | 18565 | 9 | 4 | 01 | 293 |
30 | 7 | 9 | 2 | 10372 | 17178 | 11772 | 28950 | 9 | 1 | 20 | 291 |
31 | 3 | 9 | 2 | 8096 | 8181 | 12812 | 20993 | 9 | 5 | 03 | 292 |
32 | 3 | 9 | 2 | 7224 | 7362 | 18325 | 25687 | 9 | 7 | 62 | 293 |
33 | 7 | 4 | 2 | 10372 | 17178 | 25524 | 42702 | 9 | 7 | 01 | 291 |
34 | 2 | 3 | 2 | 6186 | 4908 | 20305 | 25213 | 8 | 7 | 01 | 293 |
35 | 3 | 6 | 1 | 7156 | 8181 | 7851 | 16032 | 9 | 7 | 06 | 292 |
36 | 13 | 7 | 1 | 10372 | 31902 | 38027 | 69929 | 9 | 4 | 62 | 291 |
37 | 7 | 6 | 2 | 10306 | 17178 | 21988 | 39166 | 9 | 4 | 01 | 291 |
38 | 12 | 6 | 1 | 7307 | 29448 | 33959 | 63407 | 9 | 7 | 06 | 292 |
39 | 7 | 8 | 2 | 10306 | 17178 | 7653 | 24831 | 9 | 7 | 03 | 291 |
40 | 10 | 8 | 2 | 9348 | 27270 | 12287 | 39557 | 9 | 5 | 03 | 291 |
41 | 4 | 7 | 2 | 7224 | 9816 | 6545 | 16361 | 6 | 4 | 62 | 293 |
42 | 10 | 3 | 1 | 7328 | 27270 | 51831 | 79101 | 9 | 1 | 06 | 292 |
43 | 4 | 8 | 1 | 7328 | 10089 | 8197 | 18286 | 9 | 1 | 06 | 292 |
44 | 14 | 7 | 2 | 9314 | 36290 | 26885 | 63175 | 9 | 1 | 63 | 291 |
45 | 5 | 7 | 2 | 7328 | 12270 | 7381 | 19651 | 9 | 1 | 01 | 292 |
46 | 11 | 6 | 1 | 10372 | 27813 | 29640 | 57453 | 9 | 6 | 20 | 291 |
47 | 3 | 4 | 1 | 10306 | 7362 | 16110 | 23472 | 9 | 1 | 01 | 291 |
48 | 4 | 8 | 1 | 8352 | 10635 | 16165 | 26800 | 9 | 1 | 06 | 292 |
49 | 4 | 8 | 2 | 8352 | 9816 | 9050 | 18866 | 9 | 5 | 03 | 292 |
50 | 14 | 6 | 1 | 10372 | 52356 | 54000 | 106356 | 9 | 4 | 63 | 291 |
51 | 4 | 6 | 2 | 7307 | 9816 | 19578 | 29394 | 9 | 4 | 01 | 292 |
52 | 2 | 7 | 2 | 5077 | 5454 | 7349 | 12803 | 9 | 7 | 06 | 291 |
53 | 6 | 7 | 2 | 10372 | 16089 | 14907 | 30996 | 9 | 2 | 03 | 291 |
54 | 6 | 7 | 1 | 6200 | 14724 | 7592 | 22316 | 9 | 1 | 01 | 293 |
55 | 1 | 8 | 1 | 6200 | 2454 | 12934 | 15388 | 9 | 7 | 01 | 293 |
56 | 7 | 8 | 1 | 8352 | 17178 | 20868 | 38046 | 5 | 7 | 06 | 292 |
57 | 2 | 8 | 2 | 6946 | 4908 | 5275 | 10183 | 7 | 1 | 06 | 293 |
58 | 2 | 8 | 2 | 8299 | 4908 | 6669 | 11577 | 7 | 1 | 01 | 292 |
59 | 3 | 7 | 2 | 7091 | 7362 | 16887 | 24249 | 9 | 7 | 06 | 291 |
60 | 4 | 8 | 2 | 8352 | 9816 | 19391 | 29207 | 9 | 7 | 01 | 292 |
61 | 8 | 4 | 1 | 10306 | 21543 | 34499 | 56042 | 9 | 1 | 06 | 291 |
62 | 12 | 3 | 1 | 10372 | 29721 | 36984 | 66705 | 9 | 7 | 06 | 291 |
63 | 5 | 9 | 2 | 9348 | 12270 | 14455 | 26725 | 9 | 7 | 01 | 291 |
64 | 3 | 4 | 2 | 8135 | 7362 | 21724 | 29086 | 9 | 4 | 62 |