1
$\begingroup$

I need Stata commands or Excel function to calculate the average over 5 years groups of the values in a panel dataset. In particular, this procedure as to take into account the presence of possible missing values (empty cells in Excel) and thus adjust the computation accordingly to the actual number of non-missing in the period.

Improve this question
$\endgroup$
3
  • $\begingroup$ Not 100% sure.it is something like this: You need to use the command tabulate. If you need to do average only on the last 5 years and not on whole period of the panel, use: tabulate "name of the variables" if "name of the time variable"=2016,2015 etc. (I don't remember the exact syntax for "if", so i just put a comma, but it could be a parenthesis) $\endgroup$
    – Lex
    Commented Oct 19, 2016 at 13:22
  • $\begingroup$ Excel's AVERAGE() function will ignore empty or non-numeric cells (though not error cells) $\endgroup$
    – Henry
    Commented Oct 19, 2016 at 20:27
  • $\begingroup$ Perhaps you can share your data. From your written description alone it's not really clear what you want to do. $\endgroup$
    – user18
    Commented Oct 20, 2016 at 14:16

2 Answers 2

Reset to default
1
$\begingroup$

Is this what you want (in Stata)?

 clear all
 * Generate Data (n=5, T=20)
 set obs 100
 gen id = floor((_n-1)/20)+1
 by id, sort: gen year = 1990+_n
 gen x = rnormal()
 xtset id year
 * Convert
 gen t5 = floor((year-1991)/5)+1
 gen idt5 = id*10+t5 /* 10 can be 100, 1000, etc., depending on T */
 by idt5, sort: egen xbar5 = mean(x)
 *drop t5 idt5  /* drop if you want; you can also use tempvar */

If not, please clarify your question using an example.

Improve this answer
$\endgroup$
0
$\begingroup$

Use the tssmooth ma command. Use the window() option to control time periods to be included in the average.

. use http://www.stata-press.com/data/r14/abdata, clear
. keep id year wage
. quietly keep if id == 1 | id == 2
. quietly xtset id year
. 
. ** moving average of 2 years before, 2 years after, and the current year
. tssmooth ma wage_moving_avg_5yr = wage, window(2 1 2)
The smoother applied was
     by id : (1/5)*[x(t-2) + x(t-1) + 1*x(t) + x(t+1) + x(t+2)]; x(t)= wage

. list

     +--------------------------------+
     | year      wage   id   wage_m~r |
     |--------------------------------|
  1. | 1977   13.1516    1    12.7643 |
  2. | 1978   12.3018    1    13.0242 |
  3. | 1979   12.8395    1    13.2773 |
  4. | 1980   13.8039    1    13.6206 |
  5. | 1981   14.2897    1   13.91592 |
     |--------------------------------|
  6. | 1982   14.8681    1   14.18503 |
  7. | 1983   13.7784    1   14.31207 |
  8. | 1977   14.7909    2   14.61597 |
  9. | 1978   14.1036    2   14.83472 |
 10. | 1979   14.9534    2   15.10716 |
     |--------------------------------|
 11. | 1980    15.491    2   15.37526 |
 12. | 1981   16.1969    2   15.81556 |
 13. | 1982   16.1314    2    16.0311 |
 14. | 1983   16.3051    2   16.21113 |
     +--------------------------------+

Note that this command takes what's available in the specified time period. For example, for year 1977, since two previous years are not available, it simply calculate the average of current year and the next two years.

Improve this answer
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.