Multiplying observations within the same column/variable

[Tess Lankhorst]

Hi,

I wish to generate a variable which contains the multiplication of the observations in bhar_r by firm_id over the range of the event window.

* Example generated by -dataex-. To install: ssc install dataex
clear
input int firm_id float(bhar_r evt_window)
1         . .
1  .9700905 .
1 1.0042186 .
1  .9722555 .
1  .9875283 .
1  .9942064 .
1  .9973919 .
1 1.0145581 .
1  .9994059 .
1  .9944378 .
1   .966991 .
1  .9875697 .
1 1.0184855 .
1  .9521099 .
1 1.0115262 .
1 1.0175655 .
1  .9871982 .
1 1.0022153 .
1 1.0117328 .
1  .9948838 .
1   1.03021 .
1  .9838227 .
1  .9647022 .
1  .9604114 .
1 1.0221143 .
1  .9964027 .
1  .9567041 .
1  .9863365 .
1 1.0203692 .
1  .9506603 .
1 1.0470716 .
1  1.013532 .
1 1.0040241 .
1 1.0193344 .
1  .9821148 .
1 1.0022254 .
1  .9911805 .
1  .9896302 .
1  .9828587 .
1 1.0339937 .
1 1.0405011 .
1  .9833869 .
1 1.0028243 .
1 1.0228416 .
1  .9542269 1
1 1.0067475 1
1  .9929196 1
1  .9965523 1
1 1.0032257 1
1  .9893942 1
1 1.0130458 1
1 1.0026551 1
1 1.0008835 1
1 1.0093392 1
1  .9823473 .
1  .9991091 .
1 1.0004455 .
1  .9975471 .
1 1.0004464 .
1  .9778872 .
1         1 .
1         1 .
1  .9866787 .
1 1.0104653 .
1  .9927665 .
1  .9956115 .
1  .9969862 .
1  .9688727 .
1  1.005732 .
1  .9910292 .
1 1.0044357 .
1  1.000598 .
1 1.0044135 .
1 1.0645237 .
1 1.0064509 .
1  .9988908 .
1  .9951046 .
1 1.0127447 .
1 1.0246899 .
1 1.0138699 .
1  .9884909 .
1  .9849897 .
1 1.0212076 .
1  .9940171 .
1 1.0016061 .
1  .9677093 .
1         1 .
1  .9833989 .
1  .9809453 .
1  .9997709 .
1  .9889438 .
1  .9955896 .
1 1.0073014 .
1  .9957183 .
1  .9822155 .
1 1.0151136 .
1  .9972054 .
1 1.0134355 .
1 1.0020688 .
1  .9792348 .
end

firm_id ranges from 1 to 952, and evt_window is a dummy variable that takes the value 1 for the observations I wish to multiply.

I tried

by firm_id: gen bh_r=product bhar_r if evt_window==1

but it showed "product not found" so I am not sure which code to use.
If anyone could help out that would be great!

[Ariel Karlinsky]

I'm not sure I understand what you want your output to be, can you give an example?
anyway, in stata, "product" is not an expression, but could be a variable name - which is not (in your case). so stata doesn't understand what your'e trying to tell it to do.

Maybe you want something like this:
https://www.statalist.org/forums/for...iable-in-stata

[Tess Lankhorst]

I'm trying to calculate the buy-and-hold abnormal return, for which I need to calculate the product of (1+r) where r is the return.
So basically, for each firm, if the variable evt_window==1, I want stata to multiply all observations and put them in a new variable. Taking the first 4 observations for which the evt_window==1, I'd get something like:
.9542269 * 1.0067475 * .9929196 * .9965523 = .9505750
I think that forum post is something similar but over two variables, and replacing values. I couldn't completely figure it out, sorry

[Carlo Lazzaro]

Tess:
I do share Ariel's concerns about the intelligibility of what you're after.
Anyway, a temptative answer could be:

bysort firm_id: g target= bhar_r[_n]*bhar_r[_n+1] if evt_window==1

[Tess Lankhorst]

Carlo Lazzaro, I think our posts just crossed each other.
I tried your solution but it's not exactly what I'm looking for. Your solution multiplies the current observation with the next observation.
My goal is similar to a sum() function, but instead of summing all the observations I want to multiply them.
I hope this makes it clearer.

[Tess Lankhorst]

Ariel Karlinsky You are right, after fiddling around with the answers there it worked, thank you!

[Ariel Karlinsky]

Great! can you share the code that works for you? I looked around for a product function similar to total/sum and couldn't find any.

[Nick Cox]

It seems that you want cumulative products. So long as the original data are positive, a good method is exp(sum(log()) but here if all numbers are close to 1, double precision should be enough.

In the simplest case I can think of you also have a time variable and cumulation is separate for each event window.

gen t = _n 

bysort firm_id (t) : gen double product = bhar_r if evt == 1 & missing(evt[_n-1]) 
by firm_id : replace product = bhar_r * product[_n-1] if evt == 1 & evt[_n-1] == 1 

list if inlist(1, evt, evt[_n+1], evt[_n-1])

     +------------------------------------------------+
     | firm_id     bhar_r   evt_wi~w    t     product |
     |------------------------------------------------|
 44. |       1   1.022842          .   44           . |
 45. |       1   .9542269          1   45   .95422691 |
 46. |       1   1.006747          1   46   .96066554 |
 47. |       1   .9929196          1   47   .95386367 |
 48. |       1   .9965523          1   48   .95057502 |
     |------------------------------------------------|
 49. |       1   1.003226          1   49   .95364128 |
 50. |       1   .9893942          1   50   .94352714 |
 51. |       1   1.013046          1   51   .95583619 |
 52. |       1   1.002655          1   52   .95837408 |
 53. |       1   1.000883          1   53   .95922076 |
     |------------------------------------------------|
 54. |       1   1.009339          1   54   .96817913 |
 55. |       1   .9823473          .   55           . |
     +------------------------------------------------+

[Carlo Lazzaro]

Tess:
no, I did not crossed with Ariel's reply: I simply posted 1 minute later than he did and I actually had the chance to read his message.
Anyway, I'm glad with reading that you found out what you were looking for and share Ariel's recommendation to share your code. Thanks.

[Tess Lankhorst]

Carlo Lazzaro sorry, I misinterpreted then.
Ariel Karlinsky here you go:

sort firm_id date
by firm_id: egen bh_r=total(ln(bhar_r)) if evt_window==1
replace bh_r=exp(bh_r)

It is quite similar as to what Nick Cox posted in the other post.

[Ariel Karlinsky]

I'm happy this works for you. I find it odd that stata(or egen in particular) doesn't have a product function. seems very peculiar.

[Nick Cox]

Ariel: Yes and no.

Puzzling perhaps, but

How often is a product wanted compared with a sum and how often does exp(sum(log())) suffice because terms are all positive?

Users filled the gap long ago:

STB-51  dm71  . . . . . . . . . . . .  Calculating the product of observations
        (help prod if installed)  . . . . . . . . . . . . . . . . . .  P. Ryan
        9/99    pp.3--4; STB Reprints Vol 9, pp.45--48
        extension to egen for producing the product of observations