I would like to have an additional table in Stata, yet maybe there is a way to avoid using it. The problem is the following: I have a table with different firms; now I want to compute for every firm, for instance, the ratio of firm income to mean income over firms in that sector. Every firm has an associated SIC code. So a brute force solution would be collapse (mean) income, by(SIC) and then divide income of every company by the corresponding mean from this summary table. Yet I believe there should be a better way.
You can use extended generate (egen) to do this:
egen double sec_avg_income = mean(firm_income), by(SIC)
gen double ratio = firm_income/sec_avg_income
The first line calculates the mean firm income in each sector. The second constructs the ratio of own income to average sector income.