Estimating student missing scores on a two-component paper using Criterion Mean Method (CMM)

[Simwinga Simwinga]

Hello everyone, I would like to estimate student scores based on a two-component paper (Paper 1 & Paper 2) following the steps bellow;

• Rank the scores for Paper 2 according to the school code.
• For each school (by school_Code) and for each score (y) in Paper 2, identify the scores in Paper 2 that are within +/- 5% of the score (y) in Paper 2.
• For each school (by school_Code) and for each score (y) in Paper 2, average all the scores (x) in Paper 1 that corresponds to the identified scores above (scores in Paper 2 that are within +/- 5% of the score (y) in Paper 2)

I tried modifying a code that I typically use for similar calculations, but I am not making any progress. Could you please provide me with a code that can generate the required output?

sort School_Code Paper2
by School_Code: gen Paper2_Rank = _n
local lower = 0.95 * Paper2
local upper = 1.05 * Paper2
qui summarize Paper2 if School_Code == `School_Code' & Paper2 >= `lower' & Paper2 <= `upper', meanonly
local scorelist = r(mean)

forvalues j = 1/`=_N' {
    local xi = Paper1[`j']
    qui summarize Paper1 if School_Code == `School_Code' & Paper2 >= `lower' & Paper2 <= `upper' & Paper1 == `xi', meanonly
    local ave = r(mean)
    if "`scorelist'" != "" {
        local num_scores = wordcount("`scorelist'")
        forvalues k = 1/`num_scores' {
            local score = word("`scorelist'", `k')
            if `score' != "" {
                qui replace Lower_Limit = `lower' in `i'
                qui replace Upper_Limit = `upper' in `i'
                qui replace Ave = `ave' in `j' if Paper2 == `score' & School_Code == `School_Code'
            }
        }
    }
}

My dataset looks like this

 
Id
School_Code
Subject_Code
Paper1
Paper2
Estimated Score (P1)

1.71E+09
1001
4024
13
0


1.71E+09
1001
4024
6
1


1.72E+09
1001
4024
3
5


1.61E+09
1001
4024
2
5


1.71E+09
1001
4024
3
5


1.71E+09
1001
4024
19
6


1.71E+09
1001
4024
5
8


1.71E+09
1001
4024
0
8


1.61E+09
1001
4024
2
9


1.78E+09
1001
4024
2
9


1.78E+09
1001
4024
12
9


1.77E+09
1001
4024
8
10


1.61E+09
1001
4024
0
10


1.61E+09
1001
4024
1
10


1.71E+09
1001
4024
6
12


1.71E+09
1001
4024
6
12


1.72E+09
1001
4024
7
14


1.71E+09
1001
4024
4
14


1.71E+09
1001
4024
2
14


1.71E+09
1001
4024
8
15


1.75E+09
1001
4024
11
15


1.71E+09
1002
4024
7
14


1.79E+09
1002
4024
4
15


1.71E+09
1002
4024
4
15


1.71E+09
1002
4024
17
15


1.72E+09
1002
4024
1
15


1.71E+09
1002
4024
17
16


1.71E+09
1002
4024
15
16


1.72E+09
1002
4024
4
17


1.72E+09
1002
4024
8
17


1.72E+09
1002
4024
4
18


1.79E+09
1002
4024
3
19


1.71E+09
1002
4024
5
22


1.71E+09
1002
4024
6
24


1.71E+09
1002
4024
18
24


1.71E+09
1002
4024
10
25


1.71E+09
1002
4024
17
26


1.71E+09
1002
4024
12
26


1.71E+09
1002
4024
14
28


1.71E+09
1003
4024
4
5


1.71E+09
1003
4024
11
5


1.71E+09
1003
4024
2
5


1.71E+09
1003
4024
1
5


1.71E+09
1003
4024
0
5


1.71E+09
1003
4024
2
5


1.79E+09
1003
4024
4
5


1.71E+09
1003
4024
1
6


1.71E+09
1003
4024
2
6


1.71E+09
1003
4024
3
6


1.71E+09
1003
4024
4
6


1.71E+09
1003
4024
0
7


1.71E+09
1003
4024
6
7


1.71E+09
1003
4024
18
8


1.71E+09
1003
4024
10
8


1.71E+09
1003
4024
14
8


1.71E+09
1003
4024
1
8


1.71E+09
1003
4024
2
8


1.71E+09
1003
4024
0
9


1.71E+09
1004
4024
25
23


1.71E+09
1004
4024
20
23


1.71E+09
1004
4024
16
23


1.71E+09
1004
4024
11
24


1.71E+09
1004
4024
17
24


1.79E+09
1004
4024
25
26


1.71E+09
1004
4024
21
26


1.71E+09
1004
4024
24
26


1.71E+09
1004
4024
29
28


1.71E+09
1004
4024
26
28


1.71E+09
1004
4024
30
29


1.71E+09
1004
4024
17
29


1.71E+09
1004
4024
18
29


1.71E+09
1004
4024
10
29


1.75E+09
1004
4024
16
29


1.71E+09
1004
4024
23
30


1.71E+09
1004
4024
27
30


1.71E+09
1004
4024
40
31


1.71E+09
1004
4024
11
31


1.71E+09
1004
4024
23
31


1.71E+09
1004
4024
32
31


1.71E+09
1004
4024
26
32


1.75E+09
1005
4024
3
28


1.72E+09
1005
4024
7
28


1.72E+09
1005
4024
1
28


1.71E+09
1005
4024
6
29


1.71E+09
1005
4024
13
30


1.71E+09
1005
4024
3
30


1.71E+09
1005
4024
10
31


1.71E+09
1005
4024
10
32


1.71E+09
1005
4024
10
32


1.71E+09
1005
4024
8
32


1.77E+09
1005
4024
18
32


1.71E+09
1005
4024
1
32


1.61E+09
1005
4024
8
33

[Simwinga Simwinga]

The calculations should be as illustrated in the attachment.