Average of all prior observations with condition

[Davis Nguyen]

Hello all,

I have a data set as below. I want to generate a new variable "theta_before", which is the average of "coef_std" of all observations which appeared in years prior to the current pu_year. For example, all observations with pu_year= 1998 get a "theta_before" value equals to the average of all coef_std from 1997 or earlier.

The code that I am using is:

egen id=group(Topics order_pu)
sort id
bysort Topics: gen theta_before=sum(coef_std[_n-1])/sum(coef_std[_n-1]<.)
bysort id: replace theta_before=theta_before[1]
end

This code is fine. However, now I have to make a slight modification. I want to take the average of all coef_std without the previous year. In other words, for example, all observations with pu_year= 1998 will now get a "theta_before" value equals to the average of coef_std before 1997 (1996 or earlier, i.e not including 1997 as before). I don't know how to modify the code

I would appreciate any suggestion!

Thank you!

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte Topics int pu_year float(coef_std order_pu)
1 1995   -.2022807  5
1 1994   -.1987035  4
1 2000  -.13216728 12
1 2000  -.12715918 12
1 2000  -.12680146 12
1 2000   -.1257283 12
1 2000  -.12179336 12
1 1994  -.12000475  4
1 1992  -.10211867  2
1 2000  -.09532196 12
1 2000   -.0942488 12
1 2000  -.09246019 12
1 2000   -.0842326 12
1 2000  -.08315942 12
1 2000  -.07636271 12
1 2000  -.07564727 12
1 2000   -.0745741 12
1 2000  -.07421639 12
1 2000 -.069566004 12
1 2000  -.06920829 12
1 2000 -.067777395 12
1 2000  -.06706195 12
1 2000  -.06491563 12
1 1992  -.05561486  2
1 2000  -.05418397 12
1 1992  -.02341992  2
1 1992 -.005533839  2
1 1995   .04096997  5
1 1995   .16974975  5
2 1996   -.8832603  3
2 1999   -.7724015  6
2 1995   -.7354485  1
2 1996   -.7354485  3
2 1996   -.7077339  3
2 1995   -.6892574  1
2 1996   -.6892574  3
2 1996   -.6892574  3
2 1995   -.6892574  1
2 1995   -.6800192  1
2 1998    -.670781  4
2 1995    -.670781  1
2 1995    -.670781  1
2 1998    -.670781  4
2 1996    -.670781  3
2 1995    -.670781  1
2 1996    -.633828  3
2 1995    -.633828  1
2 1996    -.633828  3
2 1998   -.6245898  4
2 1995    -.607961  1
2 1996    -.607961  3
2 1996   -.5691604  3
2 1998  -.54144573  4
2 1996  -.54144573  3
2 1995  -.54144573  1
2 1996  -.52296925  3
2 1995  -.52296925  1
2 1995    -.513731  1
2 1996    -.513731  3
2 1996    -.513731  3
2 1995    -.513731  1
2 1995   -.5044928  1
2 1998   -.5044928  4
2 1996   -.5044928  3
2 1995   -.5044928  1
2 1995   -.4860163  1
2 1995   -.4860163  1
2 1996   -.4860163  3
2 1998   -.4675399  4
2 1998   -.4398252  4
2 1998   -.4398252  4
2 1999   -.4305869  6
2 1998   -.4222725  4
2 1998   -.4130343  4
2 1998   -.4028723  4
2 2001   -.4010246  7
2 1998  -.37423375  4
2 1999   -.3659193  6
2 1999   -.3566811  6
2 1998  -.31048995  4
2 1995  -.28277525  1
2 1999  -.28277525  6
2 1995  -.25506055  1
2 1998  -.24582233  4
2 1999  -.24582233  6
2 1995  -.24582233  1
2 1995  -.22734587  1
2 1999  -.21810764  6
2 1998   -.2079456  4
2 1995   -.1811547  1
2 1995  -.16267826  1
2 1999  -.15436384  6
2 1998  -.13311592  4
2 1999  -.12572533  6
2 1999  -.06105772  6
2 1999 -.014866563  6
2 2001  -.01117127  7
2 1999   .02208636  6
2 1998    .0959922  4
end

[Clyde Schechter]

rangestat (mean) wanted = coef_std, by(Topics) interval(pu_year . -2)

-rangestat- is written by Robert Picard, Nick Cox, and Roberto Ferrer. It is available from SSC.

[Davis Nguyen]

Awesome. Thank you!