Stata: sales growth rate of multiple groups

Question

Cross-posting: german: http://www.stata-forum.de/post1716.html#p1716 english: http://www.talkstats.com/showthread.php/47299-sales-growth-rate-with-multiple-groups-conditions

I want to calculate the annual sales growth rate of different firm-groups in Stata. The firms are grouped by variables country and industry.

I summed sales for each group (called it sales_total: sales of all firms in a group with equal country, industry and year):

bysort country year industry: egen sales_total = sum(sales)

I have a much bigger sample, but I tried to calculate the growth-rate with a smaller sample.

I tried multiple combinations such as:

egen group = group(year country industry)
xtset group year, yearly
bys group: g salesgrowth = log(D.sales_total)

or

bysort group: gen salesgrowth=(sales[_n]-sales[_n-1])/sales[_n-1]*

also with tsset.

and tried everything from this answer: Generate percent change between annual observations in Stata?

but I always get error messages such as

  repeated time values within panel

or

 repeated time values within sample

due to the repetition of the number in a variable such as group.

Can you help me to find the yearly growth rate from each group (firms from same country & industry)?

update

here again an example of my observations (which normally have 10,000 firms over 10 years). There are also missing values (for sales, industry, year, country)

firms -- country -- year -- industry -- sales

-a --------usa-------1----------1----------300

-a---------usa-------2----------1--------4000

-b---------ger-------1----------1--------200

-b---------ger-------2----------1--------400

-c---------usa------1----------1----------100

-c---------usa------2----------1----------300

-d---------usa------1----------1----------400

-d---------usa------2----------1----------200

-e---------usa------1----------1----------7000

-e---------usa------2----------1----------900

-f----------ger------1----------2----------100

-f---------ger------2----------2----------700

-h---------ger------1----------2----------700

-h---------ger------2----------2----------600

-.................etc.....................................

I tried the programing you mentioned, but I got a couple of variables that need to be used in the same row and not in the same column (which I would probably need). Is there a possibility to keep the data without reshaping, keeping them in a row, for example grouping the observations:

egen group=group(industry year country)

and then try

xtset group year bysort group: sales_growth = log(D.sales) or bysort group: gen sales_growth = (sales[_n]-sales[_n-1])/sales[_n-1]

Thank you!

Running threads in parallel on different websites without informing both sites is in my view poor behaviour: leads to duplicated effort. — Nick Cox
sorry, I thought that both sites are not at all linked to each other! I thought that it would not matter as there are different users on both sites. — Franz
If there are completely different users on each site, which is not true, why should they want to duplicate effort? — Nick Cox
I posted the question on one site. As no one replied, I thought that I would not get an answer and posted the question on another site. It won't happen again. If I do, I'll post a link on all sites that are related! Best regards and sorry again, Franz — Franz

Nick Cox Nick Cox · Accepted Answer · 2013-07-19T14:32:06

The strategy here is trying to work at the wrong level of resolution. You should

collapse (sum) sales, by(country year industry)

and then work with that reduced dataset. Depending on what you want precisely, you will probably need to restructure that data with reshape so that different industries give different variables. Then

xtset country year

and growth rates will then be easier to calculate.

Stata: sales growth rate of multiple groups

1 Answers