Dear Statalisters,

I have a panel dataset and I'm struggling to generate the averages of the last 3 observed values of a variable due to the missing values present in the data.

Here is a simplified example data:

clear
input int(id year x_1)
1 1992 .
1 1993 120
1 1994 179
1 1995 200
1 1996 .
1 1997 210
1 1998 180
1 1999 230
1 2000 .
2 1992 .
2 1993 .
2 1994 230
2 1995 308
2 1996 80
2 1997 149
2 1998 .
2 1999 .
2 2000 .
3 1992 .
3 1993 .
3 1994 190
3 1995 .
3 1996 .
3 1997 180
3 1998 210
3 1999 .
3 2000 .
end

I'd like to generate a new column x_2 which averages the last 3 observed values for x_1 for each id. So for id 1, x_2 will have a value of 206.67 ranging from 1992-2000.

Any help is appreciated. Thank you.