Export Frequency table

[Nicolas Orgeira]

Hi,

I'd like to export a frequency (one-way) table using svyset. The table would include:
* the number of observations
* the proportion
* the number of weighted observation
* the proportion of weighted observation.
I'm able to generate the four matrices but am struggling to combine them into a single matrix, as well as including the region names (not the numeric value)

    webuse nhanes2
    svyset psu [pweight=finalwgt], strata(strata)
    svy: tabulate region,  count se
    
    local row=e(r)
    local tot_obs=e(N)
    mat b1=e(b)
    
    * Weighted proportions
    mat wp1=e(Prop)
    * obs
    mat obs=e(Obs)
    mata: sum(st_matrix("e(b)"))
    
    * Weighted obs
    matrix weighted_obs=J(`row', 1, 0)
    forvalues i=1/`row' {
        matrix weighted_obs[`i', 1]=b1[1, `i']
    }
    
    * Proportions
    matrix prop=J(`row', 1, 0)
    forvalues i=1/`row' {
        matrix prop[`i', 1]=round(100*obs[`i', 1]/`tot_obs', 0.01)
    }    
    
    * Weighted Proportions
    matrix weighted_prop=J(`row', 1, 0)
    forvalues i=1/`row' {
        matrix weighted_prop[`i', 1]=round(100*wp1[`i', 1], 0.01)
    }        
    
    * Obs
    mat li obs
    * Proportion
    mat li prop
    * Weighted obs
    mat li weighted_obs
    * Weighted prop
    mat li weighted_prop

Any suggestion on how to do it, either to export in Latex or Word format? Asdoc and putdocx do not allow pweights. Moreover, I would like to loop the above code over a significant number of variables so would like to automatize the process.

Thank you

[Attaullah Shah]

Hello Prof River Huang
Can you please start a new topic for this, with appropriate title (containing keywords predict and asdocx). This will be helpful for others who might be interested in this. Search engines will then directly lead them to the forum thread.

[River Huang]

Dear Attaullah, Is it possible to put the results of "predict f1-f2" (below) into a word file? Thanks.

sysuse auto, clear
pca trunk weight length headroom
predict f1-f2

[Attaullah Shah]

Perhaps, you missed this

I would like to share these first with you and wait for any bug-report. You can contact me at [email protected] for getting the compiled files and updated ados

So nothing is updated on the website, I shall share the updated files with you on email. I do no want to update the asdocx for all users at this moment. Once, I am sure that this version is stable, I shall update it on the server.

[Nicolas Orgeira]

Hi Attaullah Shah ,

Thank you so much for your help and for spending this time in reviewing asdocx and flexmat. Unfortunately, I don't believe I will be able to run the code without the updated asdocx and flexmat (I couldn't go past the "asdocx, text" line without the following error message "option text() not allowed"). I will of course happily test it once the update is available.

Looking at the attached file, I'm glad to see that the flexmat allows dashes and spaces in the labels. However, while the table names are correct, it looks like flexmat only keeps record of the very first tabulation (including the variable name in row "flexmat addrow, data(`var' [...]").

Thank you again

[Attaullah Shah]

This needed some structural changes to both asdocx and flexmat. Before I add these changes to the official update, I would like to share these first with you and wait for any bug-report. You can contact me at [email protected] for getting the compiled files and update ados. I used the following code to generate the attached file.

flexmat reset

use "sample", clear

svyset id [pweight=sampling_weight], strata(ea)

asdocx, text(Tax for Services - Tabulations) fs(20) replace
asdocx, text(\par Respondent bio) fs(16)
    
local v=1
local row=0
foreach var of varlist  occupation gender head_of_household marital_status religion ///
    attend_service schooling bank_account income {
    
    quietly tab `var'
    local row=`row'+`r(r)'
    /*if `i'>1 & `row'>10 {
        putdocx pagebreak
        local row=0
    }*/
    
    local label : variable label `var'
    
    svy: tabulate `var',  count se
    
    local row=e(r)
    local tot_obs=e(N)
    mat b1=e(b)
    
    * Weighted proportions
    mat wp1=e(Prop)
    * obs
    mat obs=e(Obs)
    mata: sum(st_matrix("e(b)"))
    
    * Weighted obs
    matrix weighted_obs=J(`row', 1, 0)
    forvalues i=1/`row' {
        matrix weighted_obs[`i', 1]=b1[1, `i']*100000
    }
    
    * Proportions
    matrix prop=J(`row', 1, 0)
    forvalues i=1/`row' {
        matrix prop[`i', 1]=round(100*obs[`i', 1]/`tot_obs', 0.01)
    }    
    
    * Weighted Proportions
    matrix weighted_prop=J(`row', 1, 0)
    forvalues i=1/`row' {
        matrix weighted_prop[`i', 1]=round(100*wp1[`i', 1], 0.01)
    }        
    
    
    
    aslev `var', nl vl sep(|)
    
    flexmat addrow, data(`var', Obs, Proportions, Weighted obs, Weighted prop) location(`v') qui
    flexmat addcol, data("`r(vLabel)'")  row(2) location(`v') parse(pipe) qui
    flexmat addmat, matname(obs) dec(3) col(2) nonames row(2) location(`v') qui
    flexmat addmat, matname(prop) dec(3) col(3) nonames row(2) location(`v') qui
    flexmat addmat, matname(weighted_obs) dec(3) col(4) nonames row(2) format(%40.0gc) location(`v') qui
    flexmat addmat, matname(weighted_prop) dec(3) col(5) nonames row(2) location(`v')
    
    local ++v
       asdocx exportflex, fs(10) table_layout(autofit) title(Table `v': `label')

    }

[Nicolas Orgeira]

Hi Attaullah Shah ,

Thank you for offering your help. Please let me know if you need any information on my end.

[Nicolas Orgeira]

Thank you Attaullah Shah , much appreciated

[Attaullah Shah]

I shall look into it over the weekend.

[Nicolas Orgeira]

Hi Attaullah Shah,

I was wondering if you would kindly be able to help me use your asdocx package for the purpose outlined above. I'm a big fan of asdoc and am looking forward to use the enhanced asdocx package for my future work, once I'm able to master its use.

Thank you

[Nicolas Orgeira]

Hi Attaullah Shah ,

Thank you again for your quick response and for your help. I was able to replicate the results using the data used in the example but unfortunately not with my data. Please find below my dofile as well as a sample of my data attached.

flexmat reset

use "sample", clear

svyset id [pweight=sampling_weight], strata(ea)

asdoc, text(Tax for Services - Tabulations) fs(20)
asdoc, text(\par Respondent bio) fs(16)
    
local v=1
local row=0
foreach var of varlist occupation gender head_of_household marital_status religion ///
    attend_service schooling bank_account income {
    
    quietly tab `var'
    local row=`row'+`r(r)'
    /*if `i'>1 & `row'>10 {
        putdocx pagebreak
        local row=0
    }*/
    
    local label : variable label `var'
    
    svy: tabulate `var',  count se
    
    local row=e(r)
    local tot_obs=e(N)
    mat b1=e(b)
    
    * Weighted proportions
    mat wp1=e(Prop)
    * obs
    mat obs=e(Obs)
    mata: sum(st_matrix("e(b)"))
    
    * Weighted obs
    matrix weighted_obs=J(`row', 1, 0)
    forvalues i=1/`row' {
        matrix weighted_obs[`i', 1]=b1[1, `i']*10000000000
    }
    
    * Proportions
    matrix prop=J(`row', 1, 0)
    forvalues i=1/`row' {
        matrix prop[`i', 1]=round(100*obs[`i', 1]/`tot_obs', 0.01)
    }    
    
    * Weighted Proportions
    matrix weighted_prop=J(`row', 1, 0)
    forvalues i=1/`row' {
        matrix weighted_prop[`i', 1]=round(100*wp1[`i', 1], 0.01)
    }        
    
    
    
    aslev `var', nl vl sep(,)
    
    flexmat addrow, data(`var', Obs, Proportions, Weighted obs, Weighted prop) location(`v') qui
    flexmat addcol, data("`r(vLabel)'")  row(2) location(`v') qui
    flexmat addmat, matname(obs) dec(3) col(2) nonames row(2) location(`v') qui
    flexmat addmat, matname(prop) dec(3) col(3) nonames row(2) location(`v') qui
    flexmat addmat, matname(weighted_obs) dec(3) col(4) nonames row(2) format(%40.0gc) location(`v') qui
    flexmat addmat, matname(weighted_prop) dec(3) col(5) nonames row(2) location(`v')
    flexmat addtitle, data(Table `v': `label') location(`v') qui
    
    local ++v
    }
    asdocx exportflex, table_layout(autofit) append
    
asdoc, text(\par Do you own any of the following goods in working order?) fs(14)

local v=1
    foreach var of varlist radio_tv generator cellphone bicycle motorbike car computer ///
    fridge washine_machine electric_fan air_conditioning telephone {
    
    local label : variable label `var'
    
    svy: tabulate `var',  count se
    
    local row=e(r)
    local tot_obs=e(N)
    mat b1=e(b)
    
    * Weighted proportions
    mat wp1=e(Prop)
    * obs
    mat obs=e(Obs)
    mata: sum(st_matrix("e(b)"))
    
    * Weighted obs
    matrix weighted_obs=J(`row', 1, 0)
    forvalues i=1/`row' {
        matrix weighted_obs[`i', 1]=b1[1, `i']*10000000000
    }
    
    * Proportions
    matrix prop=J(`row', 1, 0)
    forvalues i=1/`row' {
        matrix prop[`i', 1]=round(100*obs[`i', 1]/`tot_obs', 0.01)
    }    
    
    * Weighted Proportions
    matrix weighted_prop=J(`row', 1, 0)
    forvalues i=1/`row' {
        matrix weighted_prop[`i', 1]=round(100*wp1[`i', 1], 0.01)
    }        
    
 
    
    aslev `var', nl vl sep(,)
    
    flexmat addrow, data(`var', Obs, Proportions, Weighted obs, Weighted prop) location(`v') qui
    flexmat addcol, data("`r(vLabel)'")  row(2) location(`v') qui
    flexmat addmat, matname(obs) dec(3) col(2) nonames row(2) location(`v') qui
    flexmat addmat, matname(prop) dec(3) col(3) nonames row(2) location(`v') qui
    flexmat addmat, matname(weighted_obs) dec(3) col(4) nonames row(2) format(%40.0gc) location(`v') qui
    flexmat addmat, matname(weighted_prop) dec(3) col(5) nonames row(2) location(`v')
    flexmat addtitle, data(Table `v': `label') location(`v') qui
    
    local ++v
    }
    asdocx exportflex, table_layout(autofit) append

I am attempting to generate a table for all categorical variables in my dataset. The file would include:

• A title
• A section title
• A title for each table using the variable label
• Tables would not be split across pages (i.e a table starting at the bottom of page one with rows on page two would automatically start on page two instead)

I am experiencing the following issues:

• I was able to generate the table for variables which include parentheses, commas or spaces with the example data but not using my data, more specifically for variables occupation (possible because of the dash), income (apostrophe) and all variables with parentheses (bank_account for example). Would there be a way to avoid tailoring the sep() option by variable?
• I wasn’t able to add a title to each table using “flexmat addtitle” (the sub-command is supposedly not recognized but is used in one of the templaces on the asdocx page).
• I couldn’t use the “asdocx, text” line to add titles and section names for each file (text option is not allowed) while using titles generated using “asdoc, text” are overwritten by “asdocx exportflex”, even after adding the “append” option
• Running the entire do-file erases the tables from the first loop
• Previously, when using putdocx, to avoid having a table split into two pages when it could fit into one, I had included a number of row counter and added a “putdocx pagebreak” which would be applied when the total number of rows of tables on a given page exceeded 10. Would a similar option exist with asdocx?

Thank you again for your help and apologies for the long list of questions

[Attaullah Shah]

Hello again Nicolas Orgeira

1. Appending tables in flexmat

---

Actually, asdocx does not perform any task while appending tables to the same document. This job is done by flexmat. In your example, we are directly dealing with flexmat, therefore, we need to use the flexmat system of appending tables to the same document. flexmat uses the concept of location. Each table in a document is written to a unique location. Therefore, if we need to append, we need to write new table to a separate location. See in the following code, I have added the option location(`v'), where `v' assume the values 1, 2, 3, ... In the code. I have also added option qui to suppress the output of flexmat until the last line.

2. Lengthy lables or labels with special characters

---

The aslev program provides several ways to report value and variable labels. For lengthy labels, we can use the sep() option to separate the lables using our preferred delimiters such comma, semicolon, pipe, etc. Since the default parsing character in flexmat is comma, I have used option sep(,) in the following code. Also, please note that I have modified the value label of sex to Person gender is (male for the category 1 to have an example of a lengthy label and with special character.

3. autofit option in asdocx

---

You can use table_layout(autofit) to get tight fit for the table contents. I am using this option with the asdocx exportflex, table_layout(autofit)

flexmat reset
use flex, clear
label define sex 1 "Person gender is (male", modify
    svyset psu [pweight=finalwgt], strata(strata)
    
local v=1
    foreach var of varlist region sex {
    svy: tabulate `var',  count se
    
    local row=e(r)
    local tot_obs=e(N)
    mat b1=e(b)
    
    * Weighted proportions
    mat wp1=e(Prop)
    * obs
    mat obs=e(Obs)
    mata: sum(st_matrix("e(b)"))
    
    * Weighted obs
    matrix weighted_obs=J(`row', 1, 0)
    forvalues i=1/`row' {
        matrix weighted_obs[`i', 1]=b1[1, `i']*10000000000
    }
    
    * Proportions
    matrix prop=J(`row', 1, 0)
    forvalues i=1/`row' {
        matrix prop[`i', 1]=round(100*obs[`i', 1]/`tot_obs', 0.01)
    }    
    
    * Weighted Proportions
    matrix weighted_prop=J(`row', 1, 0)
    forvalues i=1/`row' {
        matrix weighted_prop[`i', 1]=round(100*wp1[`i', 1], 0.01)
    }        
    
    
    
    aslev `var', nl vl sep(,)
    flexmat addrow, data(`var', Obs, Proportions, Weighted obs, Weighted prop) location(`v') qui
    flexmat addcol, data("`r(vLabel)'")  row(2) location(`v') qui
    flexmat addmat, matname(obs) dec(3) col(2) nonames row(2) location(`v') qui
    flexmat addmat, matname(prop) dec(3) col(3) nonames row(2) location(`v') qui
    flexmat addmat, matname(weighted_obs) dec(3) col(4) nonames row(2) format(%40.0gc) location(`v') qui
    flexmat addmat, matname(weighted_prop) dec(3) col(5) nonames row(2) location(`v')

    local ++v
    }
    asdocx exportflex, table_layout(autofit)

If you like asdocx, please spread the word about it. More users mean more interest, and hence a vibrant community and active development of the program.

[Nicolas Orgeira]

Hi Attaullah Shah,

Thank you for the detailed response, it's very much appreciated.

I was able to run the dofile and export the table. However, I ran into a couple of issues when running the following dofile:

asdocx_update
webuse nhanes2
    svyset psu [pweight=finalwgt], strata(strata)
    
local v=1
    foreach var of varlist region sex {
    svy: tabulate `var',  count se
    
    local row=e(r)
    local tot_obs=e(N)
    mat b1=e(b)
    
    * Weighted proportions
    mat wp1=e(Prop)
    * obs
    mat obs=e(Obs)
    mata: sum(st_matrix("e(b)"))
    
    * Weighted obs
    matrix weighted_obs=J(`row', 1, 0)
    forvalues i=1/`row' {
        matrix weighted_obs[`i', 1]=b1[1, `i']*10000000000
    }
    
    * Proportions
    matrix prop=J(`row', 1, 0)
    forvalues i=1/`row' {
        matrix prop[`i', 1]=round(100*obs[`i', 1]/`tot_obs', 0.01)
    }    
    
    * Weighted Proportions
    matrix weighted_prop=J(`row', 1, 0)
    forvalues i=1/`row' {
        matrix weighted_prop[`i', 1]=round(100*wp1[`i', 1], 0.01)
    }        
    
    
flexmat reset

aslev `var', nl vl
flexmat addrow, data(`var', Obs, Proportions, Weighted obs, Weighted prop)
flexmat addcol, data(`r(vLabel)') parse(space) row(2)
flexmat addmat, matname(obs) dec(3) col(2) nonames row(2)
flexmat addmat, matname(prop) dec(3) col(3) nonames row(2)
flexmat addmat, matname(weighted_obs) dec(3) col(4) nonames row(2) format(%40.0gc)
flexmat addmat, matname(weighted_prop) dec(3) col(5) nonames row(2)

if     `v'==1 {
asdocx exportflex, replace
}
else {
asdocx exportflex, append
}
local ++v
}

1) The append function does not seem to work;
2) The label function only works when the label does not contain paranthesis. Otherwise, when the label contains dashes or spaces, a new paragraph starts for each dash/spaces (file occupation)
3) Is there any way to avoid having a new paragraph for the weighted observation? My document will have a few hundred of tables and would like to avoid manually correcting them all.

Thank you again for your help and for creating this very handy package.

[Attaullah Shah]

Hello Nicolas Orgeira
Here are my detailed answers to your questions.

1) Getting list of value labels

---

asdocx packages comes with a list of several useful programs. One of these is the aslev.ado program. It can be used as an alternative to the levelsof command of the Stata. This program can be used to find all levels of a categorical variable. It can also be used to get value labels and variable labels of a variable. It leaves behind several useful macros. When used with the nlable and vlableoptions, it returns the following macros (see example below when using aslev with the region variable).

. aslev region, vlable nlable
1 2 3 4

. ret li

macros:
            r(vartype) : "numeric"
                 r(ng) : "4"
             r(groups) : "1 2 3 4"
             r(nLabel) : "1=NE, 2=MW, 3=S, 4=W"
             r(vLabel) : "NE MW S W"

Therefore, we can use the macro r(vLabel) to get the list of variable value labels.


2. Formatting numeric values

---

I have added the format() option to the flexmat program. The option format() will accept any valid Stata numeric format. See the following table for some of the available formats and the output they generate.

Four values displayed in different numeric display formats
    +---------------------------------------------------------------------+
    |   %9.0g   %9.0gc     %9.2f     %9.2fc %-9.0g       %09.2f     %9.2e |
    |---------------------------------------------------------------------|
    |   12345   12,345  12345.00  12,345.00  12345    012345.00  1.23e+04 |
    |  37.916   37.916     37.92      37.92  37.916   000037.92  3.79e+01 |
    | 3567890  3567890  3.57e+06   3.57e+06  3567890   3.57e+06  3.57e+06 |
    |   .9165    .9165      0.92       0.92  .9165    000000.92  9.16e-01 |
    +---------------------------------------------------------------------+

If you have a very large number, you can increase the numeric part after the % sign, e.g %40.0g


3. Exporting the flexmat with asdocx

---

Once flexmat completes the required table, it can be exported with the

asdocx exportflex

The error you are showing is caused by the slow processing of files by Dropbox. Therefore, either you need to pause Dropbox while using asdocx or you need to change to a different folder, other than the one used by Dropbox, OneDrive, or Gdrive.

Here is the complete example with the above points taken care of.

asdocx_update
use flex, clear
    svyset psu [pweight=finalwgt], strata(strata)
    svy: tabulate region,  count se
    
    local row=e(r)
    local tot_obs=e(N)
    mat b1=e(b)
    
    * Weighted proportions
    mat wp1=e(Prop)
    * obs
    mat obs=e(Obs)
    mata: sum(st_matrix("e(b)"))
    
    * Weighted obs
    matrix weighted_obs=J(`row', 1, 0)
    forvalues i=1/`row' {
        matrix weighted_obs[`i', 1]=b1[1, `i']*10000000000
    }
    
    * Proportions
    matrix prop=J(`row', 1, 0)
    forvalues i=1/`row' {
        matrix prop[`i', 1]=round(100*obs[`i', 1]/`tot_obs', 0.01)
    }    
    
    * Weighted Proportions
    matrix weighted_prop=J(`row', 1, 0)
    forvalues i=1/`row' {
        matrix weighted_prop[`i', 1]=round(100*wp1[`i', 1], 0.01)
    }        
    
    
flexmat reset

aslev region, nl vl
flexmat addrow, data(Region, Obs, Proportions, Weighted obs, Weighted prop)
flexmat addcol, data(`r(vLabel)') parse(space) row(2)
flexmat addmat, matname(obs) dec(3) col(2) nonames row(2)
flexmat addmat, matname(prop) dec(3) col(3) nonames row(2)
flexmat addmat, matname(weighted_obs) dec(3) col(4) nonames row(2) format(%40.0gc)
flexmat addmat, matname(weighted_prop) dec(3) col(5) nonames row(2) 
asdocx exportflex

[Nicolas Orgeira]

Hi Attaullah,

Thank you for your help and for letting me know about flexmat. I've purchased asdocx and have used the above code to generate the matrix. However, I still have a couple of small issues:
1) Would there be a way to keep the variable labels instead of their numerical value (i.e. Region names)?
2) Weighted observations for the dataset I'm working with are quite large (12 million+) - is there a way to avoid displaying the weighted observations in exponential form?
3) Once the above matrix has been generated, how do I export it with asdocx? I've tried using "asdocx exportflexmat" but got the following message:

file
D:\Dropbox\XXXX\_asdoc/MyFile.flexmat not found
fopen(): 601 file not found
masterclass::get_stored_matrices_info(): - function returned error
masterclass_populator(): - function returned error
<istmt>: - function returned error

Thank you