I have a dataset with firm level data.
I have a variable employees (an integer) and a variable nace2 (an integer indicating what industry or service sector the company is related to)
I have created a third variable for grouping employees:
gen employees_cat = .
replace employees_cat = 1 if employees >=0 & employees<10
replace employees_cat = 2 if employees >=10 & employees<20
replace employees_cat = 3 if employees >=20 & employees<49
replace employees_cat = 4 if employees >=49 & employees<249
replace employees_cat = 5 if employees >=249
I would like to create a frequency table showing how many employees work in every nace2 sector per employees_cat.
As a reproducible example take
sysuse auto.dta
Let's try to get a frequency table showing the overall mileage (mpg) of all domestic / foreign cars that have a trunk space of 11, 12, 16, etc.
The starting point for frequency tabulations in Stata is tabulate which can show one- and two-way breakdowns. Used with by: multi-way breakdowns can be produced as a series of two-way tables. See also table.
With the variables you mention in the auto data there are 21 distinct values for mpg and 18 for trunk, so a two-way table would be 21 x 18 or 18 x 21 with many empty cells, as the number of observations at 74 is much less than the product 378. (Here to count distinct values the command distinct is installed: search distinct in Stata for literature references and latest code version to download.)
. sysuse auto, clear
(1978 Automobile Data)
. distinct mpg trunk
------------------------------
| total distinct
-------+----------------------
mpg | 74 21
trunk | 74 18
------------------------------
One way around this problem is to collapse the tabulation into a list with typical entry {row variable, column variable, frequency information}. This is offered by the program groups, which must be installed first, as here:
. ssc inst groups
. groups trunk mpg
+-------------------------------+
| trunk mpg Freq. Percent |
|-------------------------------|
| 5 28 1 1.35 |
| 6 23 1 1.35 |
| 7 18 1 1.35 |
| 7 24 2 2.70 |
| 8 21 1 1.35 |
|-------------------------------|
| 8 24 1 1.35 |
| 8 26 1 1.35 |
| 8 30 1 1.35 |
| 8 35 1 1.35 |
| 9 22 1 1.35 |
|-------------------------------|
| 9 28 1 1.35 |
| 9 29 1 1.35 |
| 9 31 1 1.35 |
| 10 21 1 1.35 |
| 10 24 1 1.35 |
|-------------------------------|
| 10 25 1 1.35 |
| 10 26 2 2.70 |
| 11 17 1 1.35 |
| 11 18 1 1.35 |
| 11 22 1 1.35 |
|-------------------------------|
| 11 23 1 1.35 |
| 11 28 1 1.35 |
| 11 30 1 1.35 |
| 11 34 1 1.35 |
| 11 35 1 1.35 |
|-------------------------------|
| 12 22 1 1.35 |
| 12 23 1 1.35 |
| 12 25 1 1.35 |
| 13 19 3 4.05 |
| 13 21 1 1.35 |
|-------------------------------|
| 14 14 1 1.35 |
| 14 17 1 1.35 |
| 14 18 1 1.35 |
| 14 19 1 1.35 |
| 15 14 1 1.35 |
|-------------------------------|
| 15 17 1 1.35 |
| 15 18 1 1.35 |
| 15 25 1 1.35 |
| 15 41 1 1.35 |
| 16 14 3 4.05 |
|-------------------------------|
| 16 18 1 1.35 |
| 16 19 3 4.05 |
| 16 20 2 2.70 |
| 16 21 1 1.35 |
| 16 22 1 1.35 |
|-------------------------------|
| 16 25 1 1.35 |
| 17 16 3 4.05 |
| 17 18 1 1.35 |
| 17 19 1 1.35 |
| 17 20 1 1.35 |
|-------------------------------|
| 17 22 1 1.35 |
| 17 25 1 1.35 |
| 18 12 1 1.35 |
| 20 14 1 1.35 |
| 20 15 1 1.35 |
|-------------------------------|
| 20 16 1 1.35 |
| 20 18 2 2.70 |
| 20 21 1 1.35 |
| 21 17 1 1.35 |
| 21 18 1 1.35 |
|-------------------------------|
| 22 12 1 1.35 |
| 23 15 1 1.35 |
+-------------------------------+
groups has many more options, which are documented in its help. But it extends easily to multi-way tables also collapsed to lists, as here with a third grouping variable:
. groups foreign trunk mpg, sepby(foreign trunk)
+------------------------------------------+
| foreign trunk mpg Freq. Percent |
|------------------------------------------|
| Domestic 7 18 1 1.35 |
| Domestic 7 24 2 2.70 |
|------------------------------------------|
| Domestic 8 26 1 1.35 |
| Domestic 8 30 1 1.35 |
|------------------------------------------|
| Domestic 9 22 1 1.35 |
| Domestic 9 28 1 1.35 |
| Domestic 9 29 1 1.35 |
|------------------------------------------|
| Domestic 10 21 1 1.35 |
| Domestic 10 24 1 1.35 |
| Domestic 10 26 1 1.35 |
|------------------------------------------|
| Domestic 11 17 1 1.35 |
| Domestic 11 22 1 1.35 |
| Domestic 11 28 1 1.35 |
| Domestic 11 34 1 1.35 |
|------------------------------------------|
| Domestic 12 22 1 1.35 |
|------------------------------------------|
| Domestic 13 19 3 4.05 |
| Domestic 13 21 1 1.35 |
|------------------------------------------|
| Domestic 14 19 1 1.35 |
|------------------------------------------|
| Domestic 15 14 1 1.35 |
| Domestic 15 18 1 1.35 |
|------------------------------------------|
| Domestic 16 14 3 4.05 |
| Domestic 16 18 1 1.35 |
| Domestic 16 19 3 4.05 |
| Domestic 16 20 2 2.70 |
| Domestic 16 22 1 1.35 |
|------------------------------------------|
| Domestic 17 16 3 4.05 |
| Domestic 17 18 1 1.35 |
| Domestic 17 19 1 1.35 |
| Domestic 17 20 1 1.35 |
| Domestic 17 22 1 1.35 |
| Domestic 17 25 1 1.35 |
|------------------------------------------|
| Domestic 18 12 1 1.35 |
|------------------------------------------|
| Domestic 20 14 1 1.35 |
| Domestic 20 15 1 1.35 |
| Domestic 20 16 1 1.35 |
| Domestic 20 18 2 2.70 |
| Domestic 20 21 1 1.35 |
|------------------------------------------|
| Domestic 21 17 1 1.35 |
| Domestic 21 18 1 1.35 |
|------------------------------------------|
| Domestic 22 12 1 1.35 |
|------------------------------------------|
| Domestic 23 15 1 1.35 |
|------------------------------------------|
| Foreign 5 28 1 1.35 |
|------------------------------------------|
| Foreign 6 23 1 1.35 |
|------------------------------------------|
| Foreign 8 21 1 1.35 |
| Foreign 8 24 1 1.35 |
| Foreign 8 35 1 1.35 |
|------------------------------------------|
| Foreign 9 31 1 1.35 |
|------------------------------------------|
| Foreign 10 25 1 1.35 |
| Foreign 10 26 1 1.35 |
|------------------------------------------|
| Foreign 11 18 1 1.35 |
| Foreign 11 23 1 1.35 |
| Foreign 11 30 1 1.35 |
| Foreign 11 35 1 1.35 |
|------------------------------------------|
| Foreign 12 23 1 1.35 |
| Foreign 12 25 1 1.35 |
|------------------------------------------|
| Foreign 14 14 1 1.35 |
| Foreign 14 17 1 1.35 |
| Foreign 14 18 1 1.35 |
|------------------------------------------|
| Foreign 15 17 1 1.35 |
| Foreign 15 25 1 1.35 |
| Foreign 15 41 1 1.35 |
|------------------------------------------|
| Foreign 16 21 1 1.35 |
| Foreign 16 25 1 1.35 |
+------------------------------------------+