Custom table generation

[Kehinde Atoloye]

Please how can I generate this type of table with the table command.

* Example generated by -dataex-. For more info, type help dataex
clear
input byte(c1 c4) str5 agerange
2  0 "45-59"
2  5 "18-29"
1  0 "30-44"
2 77 "18-29"
2 77 "30-44"
2  0 "18-29"
1 77 "60-69"
2 12 "30-44"
2 15 "30-44"
2  7 "45-59"
2 77 "60-69"
2  5 "45-59"
1 15 "30-44"
1 15 "30-44"
2 77 "30-44"
2 12 "18-29"
2 19 "30-44"
1 12 "30-44"
2 12 "18-29"
2 12 "30-44"
1  9 "18-29"
2 10 "45-59"
2 12 "18-29"
1 16 "45-59"
2 77 "60-69"
1 16 "18-29"
1 12 "45-59"
2 15 "30-44"
2 19 "30-44"
1 15 "18-29"
1 17 "30-44"
1 15 "30-44"
1 14 "18-29"
2 77 "30-44"
1 11 "18-29"
2 15 "60-69"
2 17 "30-44"
2  6 "30-44"
2 19 "30-44"
2 12 "30-44"
1 21 "30-44"
2 12 "30-44"
1 25 "30-44"
1 12 "30-44"
2 12 "18-29"
2 12 "18-29"
1 14 "60-69"
1  1 "18-29"
1 77 ""     
2 19 "18-29"
1 77 "60-69"
1 77 "60-69"
1 12 "18-29"
1 14 "45-59"
1 13 "18-29"
2  0 "45-59"
1 12 "18-29"
2  0 "18-29"
2  0 "30-44"
1 12 "30-44"
1 77 "45-59"
2  7 "45-59"
1 12 "30-44"
1 12 "18-29"
2 15 "30-44"
1  8 "18-29"
1 14 "18-29"
2  6 "30-44"
2 18 "18-29"
2 77 "45-59"
2 18 "18-29"
2 12 "30-44"
2  6 "30-44"
1  6 "60-69"
2  0 "45-59"
2 77 "18-29"
2  0 "30-44"
1 18 "30-44"
2 16 "30-44"
1 15 "18-29"
1 12 "30-44"
1 12 "18-29"
2 12 "30-44"
1 18 "45-59"
2 14 "45-59"
2 12 "30-44"
1 14 "30-44"
1 17 "30-44"
2 11 "30-44"
1 16 "30-44"
2 12 "18-29"
1 23 "18-29"
2 17 "30-44"
2 17 "30-44"
1  0 "30-44"
2  9 "18-29"
2 20 "45-59"
1 18 "30-44"
2 14 "30-44"
1 15 "45-59"
end
label values c1 labels2
label def labels2 1 "Male", modify
label def labels2 2 "Female", modify

being trying to work out the code to no avail. thanks.

local c c1 c4
table (agerange) (), statistic(freq) statistic(percent, across(agerange)) nformat(%5.0f freq) nformat(%5.1f freq freq) sformat("(%s%%)" freq percent)
foreach v of local c{
table (agerange) (`v'), statistic(freq) statistic(percent, across(agerange)) nformat(%5.0f freq) nformat(%5.1f freq freq) sformat("(%s%%)" freq percent) append
collect continuous(c4, statistics(n mean))
collect composite define freqperc`v' = frequency percent
collect label levels result freqperc`v' "n (%)"
collect layout (agerange) (`v'#result[freqperc`v'])
}

[Jeff Pitblado (StataCorp)]

Thank you for the data and example code.

Here is what I came up with, see the comments for a brief description of each command's action.

* assuming your data is already in memory

* call -table- once to compute all the relevant statistics
table (agerange) (c1), ///
    statistic(freq) ///
    statistic(percent, across(c1)) ///
    statistic(mean c4) ///
    nformat(%5.0f freq) ///
    nformat(%5.1f percent) ///
    nformat(%5.2f mean) ///
    sformat("(%s%%)" percent) ///
    showcounts

* report the layout information to see the dimensions used to arrange the
* computed items into cells
collect layout

* see the levels of 'var' 
collect label list var, all
* recode var[_hide] to something we can attach a label to
collect recode var _hide = summary
* label the levels of 'var'
collect label levels var ///
    summary "Age group and sex of respondents" ///
    c4 "Mean number of years of education", modify

* hide all dimension labels
collect style header, title(hide)
* except we want to see the label for the 'agerange' dimension
collect style header agerange, title(label)
* but we want to use a custom label
collect label dim agerange "Age group (years)", modify

* fix the default order of the results
collect style autolevels result count frequency mean percent, clear
* fix the result labels
collect label levels result frequency "n" count "n" percent "%", modify
* fix the label for the margin of 'c1', default was "Total"
collect label levels c1 .m "Both Sexes", modify
* fix the label for the margin of 'agerange', default was "Total"
collect label levels agerange .m "18-69", modify
* bold the margin of 'agerange', it is shown in the row headers
collect style cell cell_type[row-header]#agerange[.m], font(, bold)

* bold the levels of 'c1'
collect style cell cell_type[column-header]#c1, font(, bold)
* but not the result levels
collect style cell cell_type[column-header]#result, font(, nobold)

* bold the table headers
collect style title, font(, bold)

* remove the vertical border
collect style cell border_block[corner row-header], ///
    border(right, pattern(none))

* remove the extra space between columns
collect style column, extraspace(0)

* arrange the items according to the given picture
collect layout (agerange) (c1#result) (var)

* add border under levels of 'c1'
collect style cell cell_type[column-header]#c1, border(bottom)

* add result space (unicode U+2800) to be used between levels of c1
collect get _hide = "⠀", tag(agerange[18-29] var[summary])
collect get _hide = "⠀", tag(agerange[18-29] var[c4])

* add border above the 'agerange' margins
collect style cell agerange[.m], border(top)
* but not for the space result
collect style cell agerange[.m]#result[_hide], border(top, pattern(none))

* add new space result to the layout
collect layout ///
    (agerange) ///
    ( ///
        c1[1]#result ///
        result[_hide] ///
        c1[2]#result ///
        result[_hide] ///
        c1[.m]#result ///
    ) ///
    (var)

The target document type was not mentioned, so here is a screen-shot of the resulting tables rendered in PDF and taken from the Preview app on my mac.

[Kehinde Atoloye]

thank you for your response. It's mind soothing. Grateful. Never thought the code will be this long. Wish the manuals can be more detailed. Thanks a million. However, I would like to have it in docx document, excel will not be appropriate for its format. I appreciate if you can add that part to it. Thanks a million.

[Jeff Pitblado (StataCorp)]

In Stata type help collect export.
There are some putdocx specific style choices you can control too, see help collect style putdocx.

[Kehinde Atoloye]

In Stata type help collect export.
There are some putdocx specific style choices you can control too, see help collect style putdocx.

Thanks.