Factor variable notation and exporting to excel table

[Zhanneta Begesheva]

Hey everyone, I have a question on whether it is at all possible to export summary statistics to an excel/word table so that all levels are shown. Is it feasible to make an excel/word table with the prefix "ibn-". It seems estpost does not allow for factor variable notation, I tried asdoc, putexcel commands without much success. All vars of interest are encoded categorical variables

local ibn_controls "ibn.gender ibn.age_cat ibn.edu_cat"
asdoc summ`ibn_controls' if sample==1

This returns a 3301 subscript error, everything works properly once ibn is removed. Is it possible to make a table with ibn- notation? Or any other way to show variables levels in an excel/word table would be appreciated. Thanks!

[Andrew Musau]

estpost is from SSC (FAQ Advice # 12). You can try

sysuse auto, clear
xi, noomit: estpost sum mpg i.rep78 weight i.foreign

Res.:

. xi, noomit: estpost sum mpg i.rep78 weight i.foreign

             |  e(count)   e(sum_w)    e(mean)     e(Var)      e(sd)     e(min)     e(max)     e(sum) 
-------------+----------------------------------------------------------------------------------------
         mpg |        74         74    21.2973   33.47205   5.785503         12         41       1576 
   _Irep78_1 |        69         69   .0289855   .0285592   .1689948          0          1          2 
   _Irep78_2 |        69         69    .115942   .1040068   .3225009          0          1          8 
   _Irep78_3 |        69         69   .4347826   .2493606   .4993602          0          1         30 
   _Irep78_4 |        69         69   .2608696   .1956522   .4423259          0          1         18 
   _Irep78_5 |        69         69   .1594203   .1359761   .3687494          0          1         11 
      weight |        74         74   3019.459   604029.8   777.1936       1760       4840     223440 
 _Iforeign_0 |        74         74   .7027027   .2117734   .4601885          0          1         52 
 _Iforeign_1 |        74         74   .2972973   .2117734   .4601885          0          1         22

[Zhanneta Begesheva]

Andrew Musau, thanks you so much!!! very very helpful!

[Zhanneta Begesheva]

Is there a way to keep labels of categories like ibn- does? when using sum ibn.var, the output table lists labels of categories. meanwhile, i.var displays values of categories. E.g "(male =1, male = 2" instead of "male female".

[Andrew Musau]

If you like the look of the Stata table, then just log the session, extract it and export it to Excel.

sysuse auto, clear
label define rep78 1 "Very Poor" 2 "Poor"  3 "Average"  4 "Good" 5 "Very Good"
label values rep78 rep78
preserve
qui log using firstfile, name(log1) text replace
sum ibn.rep78 ibn.foreign
log close log1
insheet using "firstfile.log", clear
gen obs=_n
levelsof obs if regexm(v1, "sum"), local(start)
levelsof obs if regexm(v1, "log close log1"), local(end)
keep if inrange(obs, `start'+1, `=`end'-1')
drop obs
drop if !regexm(v1, "[a-zA-Z]")
replace v1= ustrregexra(v1, "\u007c", " ", .)
export excel using myfile, replace
restore

Note that Excel will get the alignment right.

 . l, sep(0)

     +-----------------------------------------------------------------------+
     |                                                                    v1 |
     |-----------------------------------------------------------------------|
  1. |   Variable          Obs        Mean    Std. Dev.       Min        Max |
  2. | rep78                                                                 |
  3. | Very Poor            69    .0289855    .1689948          0          1 |
  4. |      Poor            69     .115942    .3225009          0          1 |
  5. |   Average            69    .4347826    .4993602          0          1 |
  6. |      Good            69    .2608696    .4423259          0          1 |
  7. | Very Good            69    .1594203    .3687494          0          1 |
  8. | foreign                                                               |
  9. |  Domestic            74    .7027027    .4601885          0          1 |
 10. |   Foreign            74    .2972973    .4601885          0          1 |
     +-----------------------------------------------------------------------+

.

ADDED IN EDIT: Actually, this is exported as one column in Excel. You can insert an undescore between words separated by a space and then export delimited, where the delimiter is a space -delim(" ")-, then open this as a CSV file in Excel.

[Attaullah Shah]

The latest version of asdoc can support factor variables. The new version of asdoc can be installed from my site. Copy and paste the following line in Stata and press enter.

net install asdoc, from(http://fintechprofessor.com) replace

Please note that the above line has to be copied in full. After installation of the new version, then restart Stata.

sysuse auto, clear
label define rep78 1 "Very Poor" 2 "Poor"  3 "Average"  4 "Good" 5 "Very Good"
label values rep78 rep78
asdoc sum ibn.rep78 ibn.foreign, replace

Please do remember to cite asdoc. To cite:
In-text citation
Tables were created using asdoc, a Stata program written by Shah (2018).

Bibliography
Shah, A. (2018). ASDOC: Stata module to create high-quality tables in MS Word from Stata output. Statistical Software Components S458466, Boston College Department of Economics.

[Sarah George]

Dear @Attaullah Shah

for me your code is still not working. I used the following code and got an error message:

asdoc sum ibn.edu ibn.oek_status, replace save(V_sumstats.doc) title(Descriptive statistics)

Errormessage:
asdocsum(): 3301 subscript invalid
<istmt>: - function returned error

I used your most updated asdoc version:

net install asdoc, from(http://fintechprofessor.com) replace

Thanks in advance for any help!!
Best,
Sarah