command to find birth order

[Esta Tapsir]

Dear stata experts,
I have difficulties finding syntax for birth order.
I am using data from IFLS, which consists of the variables of households id (hhid97), households order (pid97), individual id (pidlink), individual's status in the household (ar02), gender (ar07), age (ar09) and birth order. The birth order only applies to the children based on ar02 and ar09 to get their birth sequence. However, the variable has too many missing values that might be completed manually. The data looks like this,

* Example generated by -dataex-. To install: ssc install dataex
clear
input str7 hhid97 byte pid97 str9 pidlink byte(ar02 ar07) int ar09 byte birth_order
"0010600"  1 "001060001"  1 1 37 .
"0010600"  2 "001060002"  2 0  . .
"0010600"  3 "001060003"  3 0 19 .
"0010600"  4 "001060004"  3 0 11 .
"0010600"  5 "001060005"  3 1  9 .
"0010600"  6 "001060006"  3 0  . .
"0010800"  1 "001080001"  1 1 44 .
"0010800"  2 "001080002"  2 0 42 .
"0010800"  3 "001080003"  3 1 19 .
"0010800"  4 "001080004"  3 0 17 .
"0010800"  5 "001080005"  3 1 15 .
"0010800"  6 "001080006"  3 1 13 .
"0010800"  7 "001080007"  3 0 11 .
"0010800"  8 "001080008"  3 0  9 .
"0010800"  9 "001080009"  3 0  7 .
"0010800" 10 "001080010"  3 0  4 .
"0010800" 11 "001080011"  6 0  . .
"0010800" 12 "001080012"  . 0 23 .
"0012200"  1 "001220001"  1 1 41 .
"0012200"  2 "001220002"  2 0 32 .
"0012200"  3 "001220003"  3 1 17 1
"0012200"  4 "001220004"  3 0 15 2
"0012200"  5 "001220005"  3 0 13 3
"0012200"  6 "001220006"  3 1 11 4
"0012200"  7 "001220007"  3 0 10 5
"0012200"  8 "001220008"  3 1  6 6
"0012200"  9 "001220009"  3 1  5 7
"0012200" 10 "001220010"  3 0  4 8
"0012200" 11 "001220011"  . 0 15 .
"0012400"  1 "001240001"  1 1 46 .
"0012400"  2 "001240002"  2 0 44 .
"0012400"  3 "001240003"  3 1 28 .
"0012400"  4 "001240004"  3 1 21 .
"0012400"  5 "001240005"  3 0 20 .
"0012400"  6 "001240006"  3 0 17 .
"0012400"  7 "001240007"  3 0 15 .
"0012400"  8 "001240008"  3 0 12 .
"0012400"  9 "001240009"  3 0  8 .
"0012400" 10 "001240010"  3 0  4 .
"0012400" 11 "001240011"  5 0 26 .
"0012400" 12 "001240012" 10 1  6 .
"0012400" 13 "001240013" 10 1  4 .
"0012400" 14 "001240014"  . 0  0 .
"0012400" 15 "001240015"  . 0  2 .
"0012400" 16 "001240016"  . 1  0 .
"0012500"  1 "001250001"  1 1 49 .
"0012500"  2 "001250002"  2 0 40 .
"0012500"  3 "001250003"  3 1 16 .
"0012500"  4 "001250004"  3 1  . .
"0012500"  5 "001250005"  3 0 12 .
"0012500"  6 "001250006"  3 0 10 .
"0012500"  7 "001250007"  3 1  7 .
end
label values ar02 ar02b
label def ar02b 1 "01. Head", modify
label def ar02b 2 "02. Spouse", modify
label def ar02b 3 "03. Child", modify
label def ar02b 5 "05. S/D-in-law", modify
label def ar02b 6 "06. Parent", modify
label def ar02b 10 "10. Grandchild", modify
label values ar07 sex
label def sex 1 "1. M", modify

Could anybody help me with the command to complete the birth order data?
Thank's before
Esta

[Carlo Lazzaro]

Esta:
you may want to try:

. bysort hhid97: egen wanted=rank( -ar09) if ar02==3

[Esta Tapsir]

Dear Carlo,
the command worked perfectly.
Thank you so much for your help
Best,
esta