how to multiply observations within a variable in stata

[Lander Seyma]

I want to multiply observations within a variable in stata. Here's how my data looks like: ID Return 1 1.1 1 1.01 1 0.99 2 0.99 2 0.87 2 1.1 2 1.004 2 1.202 3 1.02 3 1.09 3 0.98 3 0.98 ..... ............ For each ID, I would like to multiply all the numbers under the column "return" for that ID together. At the end of the program, I would like to output a column with the product for each ID. Since each of my IDs contain a different number of observations (or returns), I'mwondering if there's a command that can do that in Stata? Kind regards, Lan

[Nick Cox]

It's best to use Stata terminology in a Stata forum: "columns" can only be columns of a matrix; variables should be so called.

Your products are just

bysort id : gen double product = return[1]
by id: replace product = product[_n-1] * return if _n > 1
by id: replace product = product{_N]

with the warning that zero or missing will send products to zero or missing. Another trick sometimes worth using if and only if all values are positive is that the product can be calculated via the sum of the logarithms.

Please note the request in the FAQ to use full real names, e.g. given name and family name.

[Richard Williams]

Are all values positive and nonzero and nonmissing? Then how about something like

use http://www.stata-press.com/data/r13/nlswork.dta, clear
gen lngrade = ln(grade)
egen lntotal = total(lngrade), by(idcode)
gen double gradeproduct = exp(lntotal)
bysort idcode: gen recnum = _n
list idcode lntotal gradeproduct if  recnum == 1

[Nick Cox]

This crossed with my post. Note that you can simplify

  
use http://www.stata-press.com/data/r13/nlswork.dta, clear
egen double gradeproduct = total(ln(grade)), by(idcode)
replace gradeproduct = exp(gradeproduct)

Also, as above, it's best to keep double precision throughout. You can't put back what you lost on the first calculation.

[Richard Williams]

Nick's code would be better than mine if some values can go negative or zero. I think my code would be better if some values are missing and you want them excluded from the calculation rather than have the product be missing. If zero, negative, or missing values are possible, you can probably tweak either set of codes to deal with those conditions as you see fit.

Nick's recoding of my code is garbled on my screen; here is what I think it should look like.

use http://www.stata-press.com/data/r13/nlswork.dta, clear 
egen double gradeproduct = total(ln(grade)), by(idcode) 
​replace gradeproduct = exp(gradeproduct)

[Nick Cox]

Again, Richard's post crossed with my edit. The more important detail is that he guessed right at my code.

[Lander Seyma]

Hello, sorry for the messy representation of the example above, my bad.

The codes work precisely. Very grateful for your responses. Thank you!

Please note the request in the FAQ to use full real names, e.g. given name and family name.

I have sent my name to the administrator.

[Rico Nederveen]

I have a similar issue. Slightly more complex though. What I need is a new variable "numberofstockowned" with the sum of the observations of variable "shares" for all "mgrname" with the same name per period "fdate". I hope you can help.

Kind regards,


Rico

[Awais Ahmed]

Hi all. I have one question : does any body know what could be commands of following formulas. May I request you Tarun Choudhary


R = τ(IT) /(W + PRI/2 + CI) and τ c = T/ C − T c − T c.

Please let me know if you need more explanations. Thank you

[Awais Ahmed]

I have variable var_description and it has two observations Excise taxes and Sales taxes and their value is 5685 and 2511 respectively. The value of observations is under new variable growth in new column. I wanted to substract observation Excise taxes - Sales taxes . Is there any command or suggestions? Thank you and appreciate help well ahead of time.

[Bandaar Dincka]

Hello everyone,
I also have almost similar question with a slight difference.
Suppose I have the following dataset and want to multiply trade with exchange rate, however I considering industry and trade partner both. Such as electronics industry has three trade partners and I want to multiply trade of electronics industry and trade partner of Japan with exchange rate. It means I want: 45*Exchange rate. I doing same for each industry and country.

ID Industry TradePartner Trade Exchange rate
1 electronics Jap 45 0.455
2 electronics US 33 0.253
3 electronics Germany 44 0.766
4 Leathers Afghanistan 23 0.235
5 Leathers India 35 0.780
6 Leathers US 33 0.34
7 Textile UK 43 0.65
8 Textile China 33 0.34
9 Textile US 5634 0.234


Thanks in advance!