Hi everyone.
I hope you are all doing fine.
I have a large data set which requires calculations. For a small data set (probably less than 30), I can patiently use MS Excel.
So, anyone's help would greatly be appreciated.
Thank you in advance.
And keep safe!
------------------------------------
I have a reference data (Table 1) and two tables (Tables 2 and 3) to produce.
Table 1 (reference data) contains two variables - Age (0 to 100) and Age specific death rates. I use this to populate Tables 2 and 3.
Below is a subset.
Age
Age_spec_dth_rates
71
0.023
72
0.023
73
0.023
74
0.023
75
0.037
76
0.037
77
0.037
78
0.037
79
0.037
80
0.063
81
0.063
82
0.063
83
0.063
84
0.063
85
0.147
86
0.147
87
0.147
88
0.147
89
0.147
90
0.147
Table 2 has three variables - age of the patients, age of the patients at last follow up and cumulative death rate.
I need to calculate the cumulative death rates for each patient using the reference data.
Table 2.
Patient_age
Patient_age_last_follow_up
Cum_dth_rate
74
80
78
84
80
83
Patient 1 joined the study at 74 years old and lived until 80 years old.
Patient 2 joined the study at 78 years old and lived until 84 years old.
Patient 2 joined the study at 80 years old and lived until 83 years old.
The cumulative death rate (cum_dth_rate) is obtained by adding the death rates at each year to the cumulative death rate for the previous year.
Table 2a shows the results. Table 2b shows how the values are generated.
Table 2a.
Patient_age
Patient_age_last_follow_up
Cum_dth_rate
74
80
0.208
78
84
0.326
80
83
0.189
Table 2b.
Age
Age_spec_dth_rates
Cum_dth_rate_pt1
Cum_dth_rate_pt2
Cum_dth_rate_pt3
71
0.023
72
0.023
73
0.023
74
0.023
75
0.037
0.023
76
0.037
0.06
77
0.037
0.097
78
0.037
0.134
79
0.037
0.171
0.037
80
0.063
0.208
0.074
81
0.063
0.137
0.063
82
0.063
0.2
0.126
83
0.063
0.263
0.189
84
0.063
0.326
85
0.147
86
0.147
87
0.147
88
0.147
89
0.147
90
0.147
Table 3 is different. Using again the reference data, I need to populate each patient's cumulative death rate until the end of the longest follow - up time of the study. For example, if the study is conducted for 10 years, the table will look like this:
Table 3.
Study Year
Cum_dth_rate_pt1
Cum_dth_rate_pt2
Cum_dth_rate_pt3
1
2
3
4
5
6
7
8
9
10
Table 3a shows the results. Table 3b shows how the values are generated.
Table 3a.
Study Year
Cum_dth_rate_pt1
Cum_dth_rate_pt2
Cum_dth_rate_pt3
1
0.023
0.037
0.063
2
0.06
0.074
0.126
3
0.097
0.137
0.189
4
0.134
0.2
0.252
5
0.171
0.263
0.315
6
0.208
0.326
0.462
7
0.271
0.389
0.609
8
0.334
0.536
0.756
9
0.397
0.683
0.903
10
0.46
0.83
1.05
Table 3b.
Age
Age_spec_dth_rates
Cum_dth_rate_pt1
Cum_dth_rate_pt2
Cum_dth_rate_pt3
71
0.023
72
0.023
73
0.023
74
0.023
75
0.037
0.023
76
0.037
0.06
77
0.037
0.097
78
0.037
0.134
79
0.037
0.171
0.037
80
0.063
0.208
0.074
81
0.063
0.271
0.137
0.063
82
0.063
0.334
0.2
0.126
83
0.063
0.397
0.263
0.189
84
0.063
0.46
0.326
0.252
85
0.147
0.389
0.315
86
0.147
0.536
0.462
87
0.147
0.683
0.609
88
0.147
0.83
0.756
89
0.147
0.903
90
0.147
1.05
The generated values are an extension of Table 2b where the cumulative death rates are calculated until the end of the entire study period (in this case, 10th year).
-----
Again, thank you very much for your help!
Yoyong.
... View more
