Outline totals within Tab of 2 variables

[Mav Mehmood]

Hi there,

I have a dataset where I`m looking at stores and the products in these stores.

The stores are categorised by types, for example store type 1 means supermarket, store type 2 means small shop and so on.

I`m trying to find how many stores are under each of these types, which I`m able to do, I`m trying to find an easier way to extract my result from the table that Stata gives.

For example, by doing:

tab store store_type

I get a table like below:

Store		Store	Type		Total
	1	2	3	4
1	0	62	0	0	62
2	0	89	0	0	89
3	158	0	0	0	158
4	0	0	0	94	94
5	0	83	0	0	83
6	0	0	0	71	71
7	0	9	0	0	9
8	0	75	0	0	75
9	0	111	0	0	111
10	0	120	0	0	120
11	54	0	0	0	54
12	0	30	0	0	30
13	66	0	0	0	66
14	0	93	0	0	93
15	0	0	133	0	133
16	0	132	0	0	132
17	0	93	0	0	93
18	0	20	0	0	20
19	0	0	0	79	0
20	0	0	129	0	129
21	40	0	0	0	40
22	0	44	0	0	44
23	0	94	0	0	94
24	93	0	0	0	93
25	133	0	0	0	133
26	0	70	0	0	70
Total	544	1125	262	244	2175

Now, by manually counting along the columns where I have figures and not zero, I`m able to determine that there are 6 stores of type 1, 15 of type 2 etc.

My question is, is there any summary command that gives me these totals without needing to manually count, in a clear table?

I have tried tabstat, but this only gives one number. I`ve also tried, sum and tab together but this gives me observations and not how many stores are of type 1 and so on.

Thank you in advance!

[Nick Cox]

From what I can gather your data are equivalent to this example

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte(Store Store_Type) int _freq
 1 2  62
 2 2  89
 3 1 158
 4 4  94
 5 2  83
 6 4  71
 7 2   9
 8 2  75
 9 2 111
10 2 120
11 1  54
12 2  30
13 1  66
14 2  93
15 3 133
16 2 132
17 2  93
18 2  20
19 4  79
20 3 129
21 1  40
22 2  44
23 2  94
24 1  93
25 1 133
26 2  70
end

insofar as I can re-create your table this way:

 
. tab Store Store_Type [fw=_freq]

           |                 Store_Type
     Store |         1          2          3          4 |     Total
-----------+--------------------------------------------+----------
         1 |         0         62          0          0 |        62 
         2 |         0         89          0          0 |        89 
         3 |       158          0          0          0 |       158 
         4 |         0          0          0         94 |        94 
         5 |         0         83          0          0 |        83 
         6 |         0          0          0         71 |        71 
         7 |         0          9          0          0 |         9 
         8 |         0         75          0          0 |        75 
         9 |         0        111          0          0 |       111 
        10 |         0        120          0          0 |       120 
        11 |        54          0          0          0 |        54 
        12 |         0         30          0          0 |        30 
        13 |        66          0          0          0 |        66 
        14 |         0         93          0          0 |        93 
        15 |         0          0        133          0 |       133 
        16 |         0        132          0          0 |       132 
        17 |         0         93          0          0 |        93 
        18 |         0         20          0          0 |        20 
        19 |         0          0          0         79 |        79 
        20 |         0          0        129          0 |       129 
        21 |        40          0          0          0 |        40 
        22 |         0         44          0          0 |        44 
        23 |         0         94          0          0 |        94 
        24 |        93          0          0          0 |        93 
        25 |       133          0          0          0 |       133 
        26 |         0         70          0          0 |        70 
-----------+--------------------------------------------+----------
     Total |       544      1,125        262        244 |     2,175

Now the logic
.

I`m able to determine that there are 6 stores of type 1, 15 of type 2 etc.

seems to be how many non-zero values are there in each column of the table, which I can get as follows:

expand _freq

forval j = 1/4 {
    qui tab Store if Store_Type == `j'
    di "Type `j':" %4.0f r(r)
 }


Type 1:   6
Type 2:  15
Type 3:   2
Type 4:   3

The expand bit is irrelevant to you because you have the data, but relevant to any other reader, because we don't Otherwise, you're counting rows in each of a series of tables.

[Mav Mehmood]

From what I can gather your data are equivalent to this example

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte(Store Store_Type) int _freq
1 2 62
2 2 89
3 1 158
4 4 94
5 2 83
6 4 71
7 2 9
8 2 75
9 2 111
10 2 120
11 1 54
12 2 30
13 1 66
14 2 93
15 3 133
16 2 132
17 2 93
18 2 20
19 4 79
20 3 129
21 1 40
22 2 44
23 2 94
24 1 93
25 1 133
26 2 70
end

insofar as I can re-create your table this way:

. tab Store Store_Type [fw=_freq]

| Store_Type
Store | 1 2 3 4 | Total
-----------+--------------------------------------------+----------
1 | 0 62 0 0 | 62
2 | 0 89 0 0 | 89
3 | 158 0 0 0 | 158
4 | 0 0 0 94 | 94
5 | 0 83 0 0 | 83
6 | 0 0 0 71 | 71
7 | 0 9 0 0 | 9
8 | 0 75 0 0 | 75
9 | 0 111 0 0 | 111
10 | 0 120 0 0 | 120
11 | 54 0 0 0 | 54
12 | 0 30 0 0 | 30
13 | 66 0 0 0 | 66
14 | 0 93 0 0 | 93
15 | 0 0 133 0 | 133
16 | 0 132 0 0 | 132
17 | 0 93 0 0 | 93
18 | 0 20 0 0 | 20
19 | 0 0 0 79 | 79
20 | 0 0 129 0 | 129
21 | 40 0 0 0 | 40
22 | 0 44 0 0 | 44
23 | 0 94 0 0 | 94
24 | 93 0 0 0 | 93
25 | 133 0 0 0 | 133
26 | 0 70 0 0 | 70
-----------+--------------------------------------------+----------
Total | 544 1,125 262 244 | 2,175

Now the logic
.


seems to be how many non-zero values are there in each column of the table, which I can get as follows:

expand _freq

forval j = 1/4 {
qui tab Store if Store_Type == `j'
di "Type `j':" %4.0f r(r)
}


Type 1: 6
Type 2: 15
Type 3: 2
Type 4: 3

The expand bit is irrelevant to you because you have the data, but relevant to any other reader, because we don't Otherwise, you're counting rows in each of a series of tables.

Thank you very much Nick! This is very helpful