Dears,

I have realized the following already in python (more familiar) but would also like to include the calculation in my Stata solution:
I have panel data (n = 15000, t = 15) with many missing due to the fact, that we use survey data with high levels of drop outs. Also, only t = 4 years have a special survey subset, that we need for our analysis. I was asked to not only treat the data as panel, but as cross sectional. My attempt to realize that, and on the same time, maximizing the observations that can be used, is to calculate (average) growth rates for the dependent variable for all observations with >= 2 valid data points.

Please see the following data example (wide format) and specific question for the part I am struggling:
ID DV_t1 DV_t2 DV_t3 DV_t4
0 10 . 20 .
1 . 20 30 40
2 20 . . 30
3 . 30 . 40
4 . . 20 30








What would be the best way to calculate the following:
  • (example based on ID 0)
    • Growth from t1 to t3 is (20-10)/10 but over the course of two time periods, so ((20-10)/10)/2
  • (example based on ID 1)
    • Growth from t2 to t3 is (30-20)/20 but over the course of one time periods, so ((30-20)/20)/1
    • Growth from t3 to t4 is (40-30)/30 but over the course of one time periods, so ((40-30)/30)/1
  • Final calculation would take the average for all observations with > 1 growth value
I have not yet found a way to loop (if that is at all the to-go solution) over the observations, calculating the growth including the information on the time gap. If this would require / make thing easier when the data is in long format, that wouldn't be a problem.

Thanks a lot and KR