How to calculate the elder generation’ maximum education year in one family

[Xinjun Lyu]

Suppose I have dataframe like this:

  family relationship meanings              edu  
1      1 A            respondent                   12  
2      1 B            respondent's spouse     18  
3      1 C            A's father                      10  
4      1 D            A's mother                    9  
5      1 E1           A's first son                 15  
6      1 F1           E1's spouse                 14  
7      1 G11          E1's first son               3  
8      1 G12          E1's second son          1  
9      1 E2           A's second son             13
10      2 A            respondent                  21
11      2 B            respondent's spouse    6
12      2 C            A's father                    12
13      2 D            A's mother                 16
14      2 E1           A's first son               18
15      2 F1           E1's spouse               15
16      2 E2           A's second son           17
17      2 E3           A's third son               16

relationship: relationship in one family.

meanings: the meanings of second column "relationship".

I want to calculate the father generation’ maximum education year in one family. We do not need spouse's information. The expected results are as follows:

 family id      edu fedu  
1      1 A        12 10    
2      1 C        10 NA    
3      1 E1       15 18    
4      1 E2       13 18    
5      1 G11       3 15    
6      1 G12       1 15    
7      2 A        21 16    
8      2 C        12 NA    
9      2 E1       18 21    
10      2 E2       17 21    
11      2 E3       16 21

Thanks!

[Nick Cox]

Please use dataex to give a data example in the recommended form. https://www.statalist.org/forums/help#stata explains. I can't tell from your example what is a string variable and what is a numeric variable with value labels, or indeed whether you are showing the data in some non-Stata form. (Stata doesn't use NA for missing.)

[Xinjun Lyu]

clear
input byte(obs family) str3 relationship str19 meanings byte edu
1 1 "A" "respondent" 12
2 1 "B" "respondent's spouse" 18
3 1 "C" "A's father" 10
4 1 "D" "A's mother" 9
5 1 "E1" "A's first son" 15
6 1 "F1" "E1's spouse" 14
7 1 "G11" "E1's first son" 3
8 1 "G12" "E1's second son" 1
9 1 "E2" "A's second son" 13
10 2 "A" "respondent" 21
11 2 "B" "respondent's spouse" 16
12 2 "C" "A's father" 12
13 2 "D" "A's mother" 16
14 2 "E1" "A's first son" 18
15 2 "F1" "E1's spouse" 15
16 2 "E2" "A's second son" 17
17 2 "E3" "A's third son" 16
end