Generate variable

[Jasper Scherpenisse]

Hi i'm new here. I have to make a Replication Study and I need to make a variable: UTB ADDS
UTB ADDS = the five-year sum of additions to UTB from current-year positions (TXTUBPOSINC) divided by the five-year sum of sales (SALE). We multiply UTB ADDS by 100.

I created this but I'm not sure if this is right, because i get the same answer for the company and not per year:

. by gvkey: egen txtubposinc_sum = total(txtubposinc) if inrange(year, year[_n]-4, year[_n])

. by gvkey: egen sale_sum = total(sale) if inrange(year, year[_n]-4, year[_n])

. gen UTB_ADDS = (txtubposinc_sum / sale_sum) * 100

[Nick Cox]

We can't see the data you use, but I guess that is wrong if only because the syntax does not make egen work in windows. The current value of year is always within the range [current value - 4, current value] for precisely the reason that 42 is in the interval [38, 42].

There are various ways to get what you want. I note that the ratio of 5 year sums is the ratio of 5 year running means, because a factor of 5 in both cancels.

Here is an analogue to your problem. I am not an economist and don't claim that the calculation would be interesting or useful. Note that moving averages are filled in for incomplete windows.

If you have values for one variable that are missing when the other variable isn't, you may need trickier code.

webuse grunfeld, clear 

tsset company year

tssmooth ma numer=invest, w(4 1)
tssmooth ma denom=mvalue, w(4 1)

gen wanted = 100 * numer/denom 

list invest mvalue numer denom wanted if company == 1