Row percentages of weighted N

[Demetriusz Rominski]

Hi,
Is there any function in Stata, in dtable, that allows calculating row percentages of weighted N?
For example: var1: female 65.1%, male 34.9%, total 100%.

 
 input byte(Sex edu_lvl var1 var2) 1 2 1 1  2 1 1 0  1 3 0 1 2 4 0 0  2 1 0 1  2 2 . 0  1 4 . 1 1 3 . 1 1 2 1 1 2 2 0 0 2 1 1 0 2 3 . 1  1 4 1 0  1 2 . 1 2 1 . 1 2 4 1 1 end   dtable, by(Sex) ///     cont(var1 var2, stat(total mean )) ///     nosample ///     nformat(%2.0f total) ///     name(a1)  collect composite define column1 = total collect composite define column2 = mean collect label levels result column1  "N" column2 "Row % of weighted N", modify  collect layout (var) (Sex#result)

[Demetriusz Rominski]

correct code:

input byte(Sex edu_lvl var1 var2)
1 2 1 1 
2 1 1 0 
1 3 0 1
2 4 0 0 
2 1 0 1 
2 2 . 0 
1 4 . 1
1 3 . 1
1 2 1 1
2 2 0 0
2 1 1 0
2 3 . 1 
1 4 1 0 
1 2 . 1
2 1 . 1
2 4 1 1
end


dtable, by(Sex) ///
    cont(var1 var2, stat(total mean )) ///
    nosample ///
    nformat(%2.0f total) ///
    name(a1)

collect composite define column1 = total
collect composite define column2 = mean
collect label levels result column1  "N" column2 "Row % of weighted N", modify

collect layout (var) (Sex#result)

[Andrew Musau]

https://www.statalist.org/forums/for...row-percentage explains how to obtain row percentages, since there is no direct option for this in dtable. I couldn't determine how you calculated the percentages you reported, but here is one approach using the method from that post and your data.

clear
input byte(Sex edu_lvl var1 var2)
1 2 1 1 
2 1 1 0 
1 3 0 1
2 4 0 0 
2 1 0 1 
2 2 . 0 
1 4 . 1
1 3 . 1
1 2 1 1
2 2 0 0
2 1 1 0
2 3 . 1 
1 4 1 0 
1 2 . 1
2 1 . 1
2 4 1 1
end
collect clear
dtable, by(Sex) name(a1)

local contvars var1 var2 
foreach var of local contvars{
    table `var' Sex if 1.`var', name(`var') ///
    statistic(freq) ///
    statistic(percent, across(Sex)) ///
    total(`var')
    collect label levels `var' 1 "`var'", modify 
}

collect combine both = a1 `contvars'
collect query composite _dtable_stats

* fix the autolevels for dimension result
collect query autolevels result
collect style autolevels result _dtable_stats, clear

collect layout (var `contvars') (Sex#result)

Res.:

. collect layout (var `contvars') (Sex#result)

Collection: both
      Rows: var var1 var2
   Columns: Sex#result
   Table 1: 3 x 3

-------------------------------------------
     |                  Sex                
     |      1           2          Total   
-----+-------------------------------------
N    |  7 (43.8%)   9 (56.2%)   16 (100.0%)
var1 |  3 (50.0%)   3 (50.0%)    6 (100.0%)
var2 |  6 (60.0%)   4 (40.0%)   10 (100.0%)
-------------------------------------------

[Demetriusz Rominski]

Can I combine the dtable and table functions to get a final table with 4 statistics, 3 from dtable and 1 from table?

clear
input byte(Sex edu_lvl var1 var2)
1 2 1 1 
2 1 1 0 
1 3 0 1
2 4 0 0 
2 1 0 1 
2 2 . 0 
1 4 . 1
1 3 . 1
1 2 1 1
2 2 0 0
2 1 1 0
2 3 . 1 
1 4 1 0 
1 2 . 1
2 1 . 1
2 4 1 1
end
collect clear

local contvars var1 var2 

dtable, by(Sex) ///
        cont(var1 var2, stat(mean total count)) ///
        nosample ///
        nformat(%2.0f total) ///
        name(a1)


foreach var of local contvars{
    table `var' Sex if 1.`var', name(`var') ///
    statistic(freq) ///
    statistic(percent, across(Sex)) ///
    total(`var')
    collect label levels `var' 1 "`var'", modify 
}

collect combine both = a1 `contvars'
collect query composite _dtable_stats

* fix the autolevels for dimension result
collect query autolevels result
collect style autolevels result _dtable_stats, clear

collect layout (var `contvars') (Sex#result)

[Jeff Pitblado (StataCorp)]

In the following I start with the code from Demetriusz, which is Andrew's example modified to add the mean, total, and count. I add some tags in the loop to get the dtable items to align with their corresponding table items. The rest of my changes involve styling an arranging the combined items into a table. All my code changes are highlighted in blue.

clear
input byte(Sex edu_lvl var1 var2)
1 2 1 1
2 1 1 0
1 3 0 1
2 4 0 0
2 1 0 1
2 2 . 0
1 4 . 1
1 3 . 1
1 2 1 1
2 2 0 0
2 1 1 0
2 3 . 1
1 4 1 0
1 2 . 1
2 1 . 1
2 4 1 1
end
collect clear

local contvars var1 var2

dtable, by(Sex) ///
        cont(var1 var2, stat(mean total count)) ///
        nosample ///
        nformat(%2.0f total) ///
        name(a1)


foreach var of local contvars {
        table `var' Sex if 1.`var', name(`var') ///
                statistic(freq) ///
                statistic(percent, across(Sex)) ///
                total(`var')
        collect label levels `var' 1 "`var'", modify

        collect addtags `var'[1], name(a1) fortags(var[`var'])
}

collect combine both = a1 `contvars'

* put results in separate columns and show their labels
collect style autolevels result mean total count percent, clear
* show result labels
collect style header result, level(label)
* customize/shorten the result labels
collect label levels result ///
        mean "Mean" ///
        total "Total" ///
        count "Count" ///
        percent "%", modify
* arrange items into a table
collect layout (`contvars') (Sex#result)

* uses composite result to compress table width
collect composite define mystats = mean total count percent, trim
collect style autolevels result mystats, clear
* modify percent's string format targetting for a nicer column header
collect style cell result[percent], sformat("%s")
collect style cell result[percent]#cell_type[item], sformat("(%s%%)")
* arrange items into a table
collect layout (`contvars') (Sex#result)

Here is the first table with each result in its own column.

------------------------------------------------------------------------------------------------------------
     |                                                   Sex
     |                 1                                 2                                Total
     |   Mean   Total   Count      %       Mean   Total   Count      %       Mean   Total   Count       %
-----+------------------------------------------------------------------------------------------------------
var1 |  0.750       3       4   (50.0%)   0.500       3       6   (50.0%)   0.600       6      10   (100.0%)
var2 |  0.857       6       7   (60.0%)   0.444       4       9   (40.0%)   0.625      10      16   (100.0%)
------------------------------------------------------------------------------------------------------------

Here is the second table using a composite result to compress the table width.

----------------------------------------------------------------------
     |                                Sex
     |           1                    2                   Total
     |  Mean Total Count %   Mean Total Count %    Mean Total Count %
-----+----------------------------------------------------------------
var1 |   0.750 3 4 (50.0%)    0.500 3 6 (50.0%)    0.600 6 10 (100.0%)
var2 |   0.857 6 7 (60.0%)    0.444 4 9 (40.0%)   0.625 10 16 (100.0%)
----------------------------------------------------------------------

[Demetriusz Rominski]

Thanks for this! I tried replacing the single variable Sex with multiple variables using a local macro, for example: local acrossvars sex edu_lvl. Is it possible to replace the variable Sex in the code with the local macro acrossvars?