table/collect: do operations between columns / levels of a dimension

[patricio cuaron]

Hello. I'm trying to learn if there is a way to do operations between columns produced by a table. Starting from the following minimal code example, I'd like to get a table that shows the White/Black ratio of union membership for every age, as the second table shows. Is it possible to do something like this using table/collect commands? I've been reading the manuals, the help, and the forums, and I can't figure it out.

. sysuse nlsw88, clear
(NLSW, 1988 extract)

. table (age) (race), stat(mean union) nformat(%3.2fc)

----------------------------------------------------
                    |               Race            
                    |  White   Black   Other   Total
--------------------+-------------------------------
Age in current year |                               
  34                |   0,32    0,45    0,00    0,35
  35                |   0,22    0,22    0,00    0,21
  36                |   0,17    0,22    0,00    0,18
  37                |   0,24    0,37    0,50    0,29       *0,24/0,37=0,65
  38                |   0,21    0,39    0,00    0,26
  39                |   0,28    0,32    0,50    0,29
  40                |   0,18    0,26            0,20
  41                |   0,23    0,24    0,50    0,24
  42                |   0,19    0,47    0,50    0,25
  43                |   0,24    0,33    0,67    0,27
  44                |   0,26    0,18    0,00    0,24
  45                |   0,22    0,33            0,25
  46                |   0,50                    0,50
  Total             |   0,22    0,30    0,33    0,25
----------------------------------------------------

Age in current	White/Black
34	0.71
35	1.00
36	0.77
37	0.65
38	0.54
39	0.88
40	0.69
41	0.96
42	0.40
43	0.73
44	1.44
45	0.67
46	#DIV/0!
Total	0.73

Thanks!

[Jeff Pitblado (StataCorp)]

table and collect do not provide a way to generate new results from existing results within a collection. You will have to build results or a dataset with the values of interest before collection.

Here is one way to do this, using your working example.

clear all
sysuse nlsw88

* original table
table (age) (race), stat(mean union) nformat(%3.2fc)

* find values labeled "White" and "Black"
label list racelbl

* compute the ratio for the whole sample; to be appended to the
* collapsed dataset
sum union if race == 1
local mean_union1 = r(mean)
sum union if race == 2
local mean_union2 = r(mean)
local tot_ratio = `mean_union1'/`mean_union2'

* copy the current frame
frame copy default collapsed
frame collapsed {
    * get cell means
    collapse (mean) union if inlist(race,1,2), by(age race)
    * reshape so we can compute the ratios
    reshape wide union , i(age) j(race)
    gen ratio = union1/union2

    * keep the variables we need for our table
    keep age ratio
    * add the ratio for the whole sample; using 999 as a code to the whole
    * sample, and label it "Overall"
    expand 2 in l
    replace age = 999 in l
    replace ratio = `tot_ratio' in l
    label define agelbl 999 "Overall"
    label values age agelbl

    * build table of ratios by age
    table (age), stat(first ratio) nototal nformat(%9.2f)
}

* fix label of result
collect label levels result first "White/Black", modify
* replay table
collect preview

Here is the resulting table.

----------------------------------
                    |  White/Black
--------------------+-------------
Age in current year |
  34                |         0.71
  35                |         1.00
  36                |         0.75
  37                |         0.65
  38                |         0.53
  39                |         0.87
  40                |         0.69
  41                |         0.95
  42                |         0.40
  43                |         0.73
  44                |         1.43
  45                |         0.67
  46                |            .
  Overall           |         0.74
----------------------------------

[patricio cuaron]

Thanks, Jeff. It'd be an interesting new feature if it was capable of doing so.