To tabulate a quantitative variable across nested categorical variables

[Kehinde Atoloye]

I have tried to tabulate the cost variable over site_name_corrected, activity_group, input_cat and modality variables which are categorical but to no avail. These categorical variables are nested in the order they are listed here. My aim is to have a sort of table that will enable me to give the cost accrued according to the categorical variables in a nested order.

I will appreciate your assistance in this regard.

Thanks.

* Example generated by -dataex-. For more info, type help dataex
clear
input str54 site_name_corrected str1 activity_group long input_cat str5 modality float cost
"Benue (Wailomayo and Wadata)Makurdi LGA"          "a" 3 "mob"  534.18494
"Benue (Wailomayo and Wadata)Makurdi LGA"          "a" 5 "mob"  30740.084
"Benue (Wailomayo and Wadata)Makurdi LGA"          "b" 4 "mob"  226.36095
"Benue (Wailomayo and Wadata)Makurdi LGA"          "b" 4 "mob"   7195.392
"Benue (Wailomayo and Wadata)Makurdi LGA"          "b" 4 "mob"   684.6987
"Benue (Wailomayo and Wadata)Makurdi LGA"          "b" 5 "mob"   148.9194
"Benue (Wailomayo and Wadata)Makurdi LGA"          "b" 5 "mob"  116.94418
"Benue (Wailomayo and Wadata)Makurdi LGA"          "b" 5 "mob"   62.62973
"Benue (Wailomayo and Wadata)Makurdi LGA"          "b" 5 "mob"   617.4673
"Benue (Wailomayo and Wadata)Makurdi LGA"          "c" 5 "mob"  211.65536
"Benue (Wailomayo and Wadata)Makurdi LGA"          "c" 5 "mob"  151.69586
"Benue (Wailomayo and Wadata)Makurdi LGA"          "c" 5 "mob"   163.0415
"Benue (Wailomayo and Wadata)Makurdi LGA"          "c" 5 "mob"  31.035225
"Benue (Wailomayo and Wadata)Makurdi LGA"          "d" 5 "mob"   7569.428
"Benue (Wailomayo and Wadata)Makurdi LGA"          "f" 2 "mob"   401.5962
"Benue (Wailomayo and Wadata)Makurdi LGA"          "f" 2 "mob"  1008.4618
"Benue (Wailomayo and Wadata)Makurdi LGA"          "f" 2 "mob"   419.7809
"Benue (Wailomayo and Wadata)Makurdi LGA"          "f" 3 "mob"   665.0686
"Benue (Wailomayo and Wadata)Makurdi LGA"          "f" 3 "mob"  1068.7242
"Benue (Wailomayo and Wadata)Makurdi LGA"          "f" 5 "mob"   97.20691
"Benue (Wailomayo and Wadata)Makurdi LGA"          "f" 5 "mob"   96.86185
"Benue (Wailomayo and Wadata)Makurdi LGA"          "f" 5 "mob"   342.0797
"Christian Center for Community Development (3CD)" "a" 3 "mob"  2.4610364
"Christian Center for Community Development (3CD)" "b" 4 "mob"  229.57846
"Christian Center for Community Development (3CD)" "b" 4 "mob"   987.4188
"Christian Center for Community Development (3CD)" "c" 4 "mob"   27.64631
"Christian Center for Community Development (3CD)" "d" 4 "mob"  180.85056
"Christian Center for Community Development (3CD)" "f" 3 "mob"   29.07365
"Christian Center for Community Development (3CD)" "f" 3 "mob"   36.96226
"Christian Center for Community Development (3CD)" "f" 3 "mob"  11.666605
"Christian Center for Community Development (3CD)" "f" 3 "mob"  20.820665
"Christian Center for Community Development (3CD)" "f" 3 "mob"  34.163006
"Christian Center for Community Development (3CD)" "f" 5 "mob"   .7203329
"Christian Center for Community Development (3CD)" "f" 6 "mob"   24.38849
"Christian Center for Community Development (3CD)" "f" 8 "mob"  23.215944
"Christian Center for Community Development (3CD)" "f" 8 "mob"    36.9674
"Christian Center for Community Development (3CD)" "f" 8 "mob"   40.47759
"Christian Center for Community Development (3CD)" "g" 6 "mob"   5183.601
"Gboko KP One Stop Shop"                           "a" 2 "mob"   24.62743
"Gboko KP One Stop Shop"                           "b" 3 "mob"    44.8106
"Gboko KP One Stop Shop"                           "b" 6 "mob"    91.3336
"Gboko KP One Stop Shop"                           "c" 4 "mob"  13.794602
"Gboko KP One Stop Shop"                           "c" 4 "mob"  33.522423
"Gboko KP One Stop Shop"                           "c" 4 "mob"   54.86737
"Gboko KP One Stop Shop"                           "c" 4 "mob"   67.21999
"Gboko KP One Stop Shop"                           "c" 4 "mob"   93.87276
"Gboko KP One Stop Shop"                           "d" 6 "mob"   27.47283
"Gboko KP One Stop Shop"                           "e" 6 "mob"   97.95804
"Gboko KP One Stop Shop"                           "e" 6 "mob"   71.16052
"Gboko KP One Stop Shop"                           "g" 3 "mob"   39.56013
"Gboko KP One Stop Shop"                           "g" 4 "mob"   97.11669
"Gboko KP One Stop Shop"                           "g" 5 "mob"   59.73158
"Gboko KP One Stop Shop"                           "g" 5 "mob"   81.14655
"Gboko KP One Stop Shop"                           "g" 5 "mob"  12.396338
"Gboko KP One Stop Shop"                           "g" 5 "mob"  32.847313
"Gombe is Bakassi in Katungo LGA"                  "a" 4 "idx"   8.668825
"Gombe is Bakassi in Katungo LGA"                  "a" 4 "idx"   8.127492
"Gombe is Bakassi in Katungo LGA"                  "b" 4 "idx"  1194.3363
"Gombe is Bakassi in Katungo LGA"                  "b" 4 "idx"   2.989621
"Gombe is Bakassi in Katungo LGA"                  "b" 4 "idx"   .4216937
"Gombe is Bakassi in Katungo LGA"                  "b" 4 "idx"   1515.767
"Gombe is Bakassi in Katungo LGA"                  "b" 4 "idx"  .20389897
"Gombe is Bakassi in Katungo LGA"                  "e" 4 "idx"   64.56348
"Gombe is Bakassi in Katungo LGA"                  "e" 4 "idx"  21.898457
"Gombe is Bakassi in Katungo LGA"                  "e" 6 "idx"  21.517635
"Gombe is Bakassi in Katungo LGA"                  "f" 3 "idx"   29.03677
"Gombe is Bakassi in Katungo LGA"                  "f" 3 "idx"   6.296571
"Gwagwalada KP One Stop Shop"                      "b" 3 "link" 12.942368
"Gwagwalada KP One Stop Shop"                      "b" 4 "link"  343.5709
"Gwagwalada KP One Stop Shop"                      "b" 4 "link"  13.82327
"Gwagwalada KP One Stop Shop"                      "b" 4 "link" 3.6813405
"Gwagwalada KP One Stop Shop"                      "b" 4 "link"  21.84421
"Gwagwalada KP One Stop Shop"                      "c" 4 "link"  .7983846
"Gwagwalada KP One Stop Shop"                      "c" 4 "link" 10.090512
"Gwagwalada KP One Stop Shop"                      "c" 5 "link"  59.57713
"Gwagwalada KP One Stop Shop"                      "e" 3 "link" 18.758984
"Gwagwalada KP One Stop Shop"                      "e" 4 "link"  49.15485
"Gwagwalada KP One Stop Shop"                      "e" 5 "link"  47.08419
"Gwagwalada KP One Stop Shop"                      "e" 5 "link"  10.93856
"Gwagwalada KP One Stop Shop"                      "e" 6 "link"  2.330999
"Gwagwalada KP One Stop Shop"                      "e" 6 "link" 12.867028
"Gwagwalada KP One Stop Shop"                      "f" 6 "link" 198.54863
"Gwagwalada KP One Stop Shop"                      "g" 3 "link" 226.14565
"Gwagwalada KP One Stop Shop"                      "g" 3 "link"  82.99586
"Gwagwalada KP One Stop Shop"                      "g" 3 "link" 28.313665
"Gwagwalada KP One Stop Shop"                      "g" 3 "link"   2.68613
"Gwagwalada KP One Stop Shop"                      "g" 4 "link" 13.254156
"Gwagwalada KP One Stop Shop"                      "g" 6 "link" 14.761385
"Initiative for Advancement of Humanity (IAH)"     "b" 4 "link" 28775.047
"Initiative for Advancement of Humanity (IAH)"     "b" 4 "link"  486.5101
"Initiative for Advancement of Humanity (IAH)"     "g" 4 "link"  54.32331
"Lagos Community"                                  "a" 2 "mob"   226.3217
"Lagos Community"                                  "a" 2 "mob"   3978.153
"Lagos Community"                                  "a" 2 "mob"   295.1708
"Lagos Community"                                  "a" 8 "mob"   3941.369
"Lagos Community"                                  "a" 8 "mob"   2436.417
"Lagos Community"                                  "b" 4 "mob"   88.56134
"Lagos Community"                                  "b" 4 "mob"   3.193607
"Lagos Community"                                  "b" 4 "mob"   42.46684
"Lagos Community"                                  "b" 4 "mob"   41.52208
end
label values input_cat input_cat
label def input_cat 2 "contracted_services", modify
label def input_cat 3 "equipment", modify
label def input_cat 4 "hts_supplies", modify
label def input_cat 5 "other_supplies", modify
label def input_cat 6 "personnel", modify
label def input_cat 8 "transportation_not_training", modify

[Maarten Buis]

table (site_name_corrected input_cat modality ) (cost )

[Kehinde Atoloye]

table (site_name_corrected input_cat modality ) (cost )

Thank you.

Unfortunately, some of the variables are string and the table command is rejecting string variables. Is there a way to automatically convert the string variables into categorical numeric variables with value labels. site_name_corrected and modality are string variables and they need to be converted.

Moreso, how can I get the table into a file instead.

[Rich Goldstein]

the code in #2 works fine on my machine - are you using version 17? if not, you must tell us what version you are using as requested by the FAQ

[Kehinde Atoloye]

the code in #2 works fine on my machine - are you using version 17? if not, you must tell us what version you are using as requested by the FAQ

Yes, I am using version 17.

[Nick Cox]

I have to ask: How is such a table going to be easier to read than a listing of the data?

[Kehinde Atoloye]

I have to ask: How is such a table going to be easier to read than a listing of the data?

My interest is to have a sum of the values of cost for the categories instead of a frequency table. The suggestions so far are producing frequencies.

I tried the below

 
 table (site_name_corrected input_cat modality ) (cost )

but it is producing frequency instead of sum. I have been trying to figure a way around it but yet to no avail. Thanks.

[Nick Cox]

Indeed: if you want sums you need to ask for them. Here is a silly example to get you started:

. sysuse auto, clear
(1978 automobile data)

. table foreign rep78

------------------------------------------
           |       Repair record 1978     
           |  1   2    3    4    5   Total
-----------+------------------------------
Car origin |                              
  Domestic |  2   8   27    9    2      48
  Foreign  |           3    9    9      21
  Total    |  2   8   30   18   11      69
------------------------------------------

. table foreign rep78, stat(sum weight)

-----------------------------------------------------------------
           |                   Repair record 1978                
           |      1        2        3        4        5     Total
-----------+-----------------------------------------------------
Car origin |                                                     
  Domestic |  6,200   26,830   92,940   31,790    3,920   161,680
  Foreign  |                    6,030   19,870   21,630    47,530
  Total    |  6,200   26,830   98,970   51,660   25,550   209,210
-----------------------------------------------------------------

[Kehinde Atoloye]

Indeed: if you want sums you need to ask for them. Here is a silly example to get you started:

. sysuse auto, clear
(1978 automobile data)

. table foreign rep78

------------------------------------------
| Repair record 1978
| 1 2 3 4 5 Total
-----------+------------------------------
Car origin |
Domestic | 2 8 27 9 2 48
Foreign | 3 9 9 21
Total | 2 8 30 18 11 69
------------------------------------------

. table foreign rep78, stat(sum weight)

-----------------------------------------------------------------
| Repair record 1978
| 1 2 3 4 5 Total
-----------+-----------------------------------------------------
Car origin |
Domestic | 6,200 26,830 92,940 31,790 3,920 161,680
Foreign | 6,030 19,870 21,630 47,530
Total | 6,200 26,830 98,970 51,660 25,550 209,210
-----------------------------------------------------------------

Thanks very much. Though I did not quickly understand the code, I eventually understood it after I took my time to delve through Stata help. Am grateful. At least I have an idea on how to get the sum of the continuous variable which is the cost over a categorical variable. I can now focus on converting the string variables into numeric variables. I wish there is a way I can code the conversion in a simpler form. Thanks.

[Nick Cox]

As earlier in the thread, I don't find that there is any issue with string categories. Here is a demonstration.

However, table has been the subject of various fixes in the lifetime of Stata 17, so you may need to update the software.

.

    clear

. set obs 10
Number of observations (_N) was 0, now 10.

. gen which = cond(_n <= 5, "frog", "toad")

. gen what = cond(mod(_n, 2), "Gryffindor", "Slytherin")

. gen howmuch = _n

. table which what, stat(sum howmuch)

-----------------------------------------
        |               what             
        |  Gryffindor   Slytherin   Total
--------+--------------------------------
which   |                                
  frog  |           9           6      15
  toad  |          16          24      40
  Total |          25          30      55
-----------------------------------------

[Kehinde Atoloye]

As earlier in the thread, I don't find that there is any issue with string categories. Here is a demonstration.

However, table has been the subject of various fixes in the lifetime of Stata 17, so you may need to update the software.

.

 clear

. set obs 10
Number of observations (_N) was 0, now 10.

. gen which = cond(_n <= 5, "frog", "toad")

. gen what = cond(mod(_n, 2), "Gryffindor", "Slytherin")

. gen howmuch = _n

. table which what, stat(sum howmuch)

-----------------------------------------
| what
| Gryffindor Slytherin Total
--------+--------------------------------
which |
frog | 9 6 15
toad | 16 24 40
Total | 25 30 55
-----------------------------------------

Below is the result when I use the modality string variable

. table modality, statistic(sum cost)

string variables not allowed in layout; modality is a string variable

Below is the result when I use the input_cat numeric variable

​​​​​​​
. table input_cat, statistic(sum cost)

-----------------------------------------
                              |     Total
------------------------------+----------
input category                |          
  building                    |         0
  contracted_services         |  11,780.8
  equipment                   |  5,395.89
  hts_supplies                |   100,543
  other_supplies              |   181,372
  personnel                   |  8,568.54
  training                    |  2,143.08
  transportation_not_training |  52,826.2
  utilities                   |         0
  Total                       |   362,630
-----------------------------------------

I earlier stated that my Stata version is 17. Could that be the reason?

Thanks.

[Nick Cox]

The point is not whether you are using Stata 17, as the code that worked for you would not be possible otherwise. Run

update query

to see whether you need to update your Stata. Stata has a distinction: an upgrade is moving to a new version, which needs to be paid for, but an update consists of fixes and extras within the span of a given version, and is free.

[Rich Goldstein]

the following will show whether you are up-to-date (my version is)

. query born
10 Jan 2023

and the following some reproducible code that shows no problem with a string variable:

. sysuse auto
(1978 automobile data)

. table make, stat(sum price)

which works fine