Panel Data extract values from one variable

[Lea Birm]

Hello,

I am currently working with panel data where I have for every District in the years 2002, 2007, 2012 and 2017 observations on the total workforce in each district and the total workforce in agriculture (taz).
Now, I want to create a new variable, that gives the ratio of taz/total_workforce_i. However, I want for the years 2002 and 2007 to derive the ratio based on the 2002 total_workforce_i (=276) and with a taz for the respective years and to use the total_workforce of 2012 for the years 2012 and 2017 with the individual taz's for the respective years.

I already tried several options but none resulted in the desired output. Do you have any suggestions on how to approach it?

Thank you very much in advance!

clear
input str36 District float Year double(total_workforce_i taz)
"01001" 2002 276.06683349609375 31.349594116210938
"01001" 2007  380.1287536621094 43.105690002441406
"01001" 2012                340                 28
"01001" 2017                370                 20
"01002" 2002 40.023956298828125                  0
"01002" 2007 107.99324798583984  7.837398529052734
"01002" 2012                104                  0
"01002" 2017                105                  0
"01004" 2002  4549.923034667969  27.43089485168457
"01004" 2007      5502.69140625 27.089689254760742
"01004" 2012   5877.88134765625 13.972138404846191
"01004" 2017      5669.05859375 15.497458457946777
end

[Fei Wang]

Lea, the code below works for a balanced panel data.

sort District Year
gen ratio = taz/total_workforce_i if inlist(Year, 2002, 2012)
replace ratio = taz/total_workforce_i[_n-1] if inlist(Year, 2007, 2017)

[William Lisowski]

Perhaps this?

. sort District Year

. by District: generate twf = total_workforce_i if inlist(Year,2002,2012)
(6 missing values generated)

. by District: replace  twf = twf[_n-1]         if inlist(Year,2007,2017)
(6 real changes made)

. generate wanted = taz/twf

. list, clean

       District   Year   total_w~i         taz        twf     wanted  
  1.      01001   2002   276.06683   31.349594   276.0668    .113558  
  2.      01001   2007   380.12875    43.10569   276.0668   .1561422  
  3.      01001   2012         340          28        340   .0823529  
  4.      01001   2017         370          20        340   .0588235  
  5.      01002   2002   40.023956           0   40.02396          0  
  6.      01002   2007   107.99325   7.8373985   40.02396   .1958177  
  7.      01002   2012         104           0        104          0  
  8.      01002   2017         105           0        104          0  
  9.      01004   2002    4549.923   27.430895   4549.923   .0060289  
 10.      01004   2007   5502.6914   27.089689   4549.923   .0059539  
 11.      01004   2012   5877.8813   13.972138   5877.881   .0023771  
 12.      01004   2017   5669.0586   15.497458   5877.881   .0026366  

.