Multiplying values within same variable given certain conditions

[Patrick Johnson]

Hello everyone,

Below, you will find a structure of a similar dataset that i am using. The data consists of all companies present in a given year for the period 1996-2015. As that dataset is fairly large, i have provided you a small sample. Also the company's industry code is known, as well as the city's yearly growth. I am trying to generate the total growth of a city between 1996 - 1999 by multiplying the yearly growth numbers from - GrowthYear -. The variable that has to be created will thus be the same all observations that have the same city where the years fall between 1996-1999. Obviously, i can not multiply every value found in the -GrowthYear - variable, as this will lead to an overestimation of the growth between 1996 and 1999. Therefore, i want to multiply the values from - Growthyear - where the number of companies within the same industry, in the same city and year to be smaller than 2 & whereas the variable - year - is smaller than 2000. The yearly growth number of the city is taken into the calculation only once. Finally, i want the value for the variable that has to be created to be equal for all companies across years and all industries that are located in the city for the period 1996-1999.

I have found the code suggested in a similar, but easier example (see link below). Unfortunately, this did not work for my database.
HTML Code:

https://www.statalist.org/forums/forum/general-stata-discussion/general/75096-how-to-multiply-observations-within-a-variable-in-stata

* Example generated by -dataex-. To install: ssc install dataex
clear
input str26 city int year str2 Industry_code float(duplicate GrowthYear)
"A" 1997 "1"  0 1.0365331
"A" 1997 "2"  1 1.0365331
"A" 1997 "2"  2 1.0365331
"A" 1997 "3"  0 1.0365331
"A" 1997 "4"  0 1.0365331
"A" 1997 "5"  1 1.0365331
"A" 1997 "5"  2 1.0365331
"A" 1998 "1"  0  .9852498
"A" 1998 "2"  1  .9852498
"A" 1998 "2"  2  .9852498
"A" 1998 "3"  0  .9852498
"A" 1998 "4"  0  .9852498
"A" 1999 "1"  0 1.0644425
"A" 1999 "2"  1 1.0644425
"A" 1999 "2"  2 1.0644425
"A" 1999 "3"  0 1.0644425
"A" 1999 "4"  0 1.0644425
"A" 1999 "5"  1 1.0644425
"A" 1999 "5"  2 1.0644425
"A" 1999 "6"  1 1.0644425
end

I hope that someone can help me out. Feel free to ask any question.

Kind regards,

Patrick

[Carole J. Wilson]

There are several parts of your request that I don't understand. But, using the example you gave, it seems like you want the growth rates for 1996* 1997 * 1998 * 1999 for each city. In the city A data provided, this would be 1.036533 * 0.9852498 * 1.064443= 1.087056.

The following code creates a new variable x that gives you that:

*"tag" a single observation for each city/year
egen tag=tag(city year)

*"untag" any years <1996 or >1999                               
replace tag=0 if !inrange(year, 1996, 1999)

*get the product of the tagged observations with each city
bysort city: gen x= sum(ln( GrowthYear )) if tag==1
bysort city (tag year): replace x=exp(x[_N])

[Patrick Johnson]

Dear Carole,

Thank you so much. This is exactly the code i was looking for. My apologies for not being clear.

Kind regards,

Patrick