Hello everyone,

I have a panel with occupation shares (already in percentages) across 9 occupation different (ISCO-88) categories for a set of countries over time, for the period 1993-2010.
For a cross-sectional regression I want to calculate the average annual percentage change for each country for the 9 occupation categories.
The problem is now that the data series often show gaps. The countries vary significantly in the number of observations.
So my idea was to use the observations I have, calculate the change rate and divide this by the number of periods between the two observations.
If the time series is complete, there is no problem, as the change rates can be easily calculated, as in the case of for example Canada (see tabe below).
I do that with the following code

Code:
tsset c_id year 
foreach x in occ1 occ2 occ3 occ4 occ5 occ6 occ7 occ8 occ9 occ_total occ_x_not_elsewhere { 
    gen `x'_gr = D.`x' 
}
As my data is already in percentages, the simple difference between two values yields changes in percentage points.

The challenge is now to deal with gaps in the data. As Stata skips gaps in the time series, so for example in the case of Cambodia, I only get changes for 2000-2001, but not for 2001-2004, due to missing observations. I would like to generate a code that also includes the latter change, and divide it (in this case) by 3, or whatever the number of respective periods, to later calculate average annual changes, taking several change rates together.

c_id country year occ1 occ2 occ3 occ4 occ5 occ6 occ7 occ8 occ9 occ1_gr occ2_gr
13 Cambodia 1993
13 Cambodia 1994
13 Cambodia 1995
13 Cambodia 1996
13 Cambodia 1997
13 Cambodia 1998
13 Cambodia 1999
13 Cambodia 2000 .5 1.3 2 .6 8.9 72.1 6.6 2.6 4.4
13 Cambodia 2001 .7 1.2 2 .4 10.5 65.6 8.2 3.5 7.3 .2 -.0999999
13 Cambodia 2002
13 Cambodia 2003
13 Cambodia 2004 .3 .1 2.1 .8 6.7 70.4 5.5 1.2 5.4
13 Cambodia 2005
13 Cambodia 2006
13 Cambodia 2007
13 Cambodia 2008
13 Cambodia 2009
13 Cambodia 2010
14 Canada 1993 10.6 14.8 12.5 14.8 14.2 3.7 9.3 10.2 9.8
14 Canada 1994 10.4 15.4 12.4 14.2 14.1 3.5 9.4 10.6 10 -.2000008 .5999994
14 Canada 1995 11 15.2 12.5 13.7 14.1 3.5 9.4 10.9 9.7 .6000004 -.1999998
14 Canada 1996 11 14.8 12.7 13.4 14.4 3.5 9.4 10.3 9.8 0 -.3999996
14 Canada 1997 10.1 15.8 13.1 13.8 14 3.4 10.4 10.5 8.9 -.8999996 1
14 Canada 1998 9.8 15.8 13.6 13.6 14.1 3.3 10.3 10.5 8.9 -.3000002 0
14 Canada 1999 9.7 16 13.6 13.8 14.2 3.2 10.5 10.8 8.2 -.1000004 .1999998
14 Canada 2000 9.8 15.8 13.9 13.8 14.2 2.9 10.3 11 8.2 .1000004 -.1999998
14 Canada 2001 9.1 16.2 14.2 14.1 14.5 2.6 10.3 10.8 8 -.6999998 .4000006
14 Canada 2002 9 16.2 14.2 13.8 14.7 2.5 10.7 10.8 8.1 -.1000004 0
14 Canada 2003 8.9 15.7 14.6 13.9 14.7 2.5 10.6 10.8 8.2 -.1000004 -.500001
14 Canada 2004 9.2 15.8 14.3 14.1 14.7 2.4 10.5 10.6 8.3 .3000002 .1000004
14 Canada 2005 9.2 16.9 14.8 13.8 14.1 2.4 10.3 10 8.3 0 1.099999
14 Canada 2006 9.3 17.1 15 13.8 14.2 2.4 10.1 9.7 8.4 .1000004 .2000008
14 Canada 2007 9.1 17.4 15.2 13.5 14.6 2.3 10.2 9.3 8.3 -.1999998 .2999992
14 Canada 2008 9.3 17.6 15.5 13.4 14.5 2.1 10.5 8.9 8 .1999998 .2000008
14 Canada 2009 9.3 18 16 13.2 15.1 2.2 10.2 8.4 7.6 0 .3999996
14 Canada 2010 9.2 18.2 16.4 13 15 2.1 10.1 8.4 7.6 -.1000004 .2000008

I hope I explained the problem well. Any help is appreciated! :-)

Regards,
Jonas