Generate variable

[Jade Li]

Dear All,

I have the following data example:

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte company str4 dep int time str4(subsection section) long prod
1 "A1"   2015 "3312" "." 164331
1 "A1"   2016 "3312" "." 164440
1 "A1"   2017 "3312" "." 174718
1 "A1"   2018 "3312" "." 174759
1 "A1"   2019 "3312" "." 174800
1 "A1"   2020 "3312" "." 174841
1 "A1"   2021 "3312" "." 174882
1 "A1"   2022 "3312" "." 174923
1 "A11"  2022 "3312" "." 174964
1 "A111" 2022 "5222" "." 175005
2 "A2"   2015 "4977" "." 174964
2 "A2"   2016 "4977" "." 175005
2 "A2"   2017 "4977" "." 175046
2 "A2"   2018 "4977" "." 175087
2 "A2"   2019 "4977" "." 175128
2 "A2"   2020 "4977" "." 175169
2 "A2"   2021 "4977" "." 175210
2 "A2"   2022 "4977" "." 175251
2 "A22"  2015 "4977" "." 175292
2 "A22"  2016 "4977" "." 175333
2 "A22"  2017 "4977" "." 175374
2 "A22"  2018 "4977" "." 175415
2 "A22"  2019 "4977" "." 175456
2 "A22"  2020 "4977" "." 175497
2 "A22"  2021 "4977" "." 175538
2 "A22"  2022 "4977" "." 175579
3 "A3"   2015 "5101" "." 175620
3 "A33"  2015 "2411" "." 175661
3 "A3"   2016 "5101" "." 175702
3 "A3"   2017 "5101" "." 175743
3 "A3"   2018 "5101" "." 175800
3 "A33"  2018 "2411" "." 175132
end

I would like to replace missings in the "section" variable with values from the "subsection" var as follow:

• section is equal to the maximum frequent values of the subsection in a year (A1, A11, A111 in 2022)
• if there are only two "subsection" values in a year, I can't choose the maximum repeated value in that year, so I choose the value of the "subsection" that has higher "prod".

I would like to have th data at the end like this:

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte company str4 dep int time str4(subsection section) long prod
1 "A1"   2015 "3312" "3312" 164331
1 "A1"   2016 "3312" "3312" 164440
1 "A1"   2017 "3312" "3312" 174718
1 "A1"   2018 "3312" "3312" 174759
1 "A1"   2019 "3312" "3312" 174800
1 "A1"   2020 "3312" "3312" 174841
1 "A1"   2021 "3312" "3312" 174882
1 "A1"   2022 "3312" "3312" 174923
1 "A11"  2022 "3312" "3312" 174964
1 "A111" 2022 "5222" "3312" 175005
2 "A2"   2015 "4977" "4977" 174964
2 "A2"   2016 "4977" "4977" 175005
2 "A2"   2017 "4977" "4977" 175046
2 "A2"   2018 "4977" "4977" 175087
2 "A2"   2019 "4977" "4977" 175128
2 "A2"   2020 "4977" "4977" 175169
2 "A2"   2021 "4977" "4977" 175210
2 "A2"   2022 "4977" "4977" 175251
2 "A22"  2015 "4977" "4977" 175292
2 "A22"  2016 "4977" "4977" 175333
2 "A22"  2017 "4977" "4977" 175374
2 "A22"  2018 "4977" "4977" 175415
2 "A22"  2019 "4977" "4977" 175456
2 "A22"  2020 "4977" "4977" 175497
2 "A22"  2021 "4977" "4977" 175538
2 "A22"  2022 "4977" "4977" 175579
3 "A3"   2015 "5101" "2411" 175620
3 "A33"  2015 "2411" "2411" 175661
3 "A3"   2016 "5101" "5101" 175702
3 "A3"   2017 "5101" "5101" 175743
3 "A3"   2018 "5101" "5101" 175800
3 "A33"  2018 "2411" "5101" 175132
end

Thank you,