getting putdocx to change the display format to %12.0fc

[Maria Boutchkova]

Dear Statalisters,

could you please help with a conundrum I have not been able to resolve myself.

The code below creates a properly formatted docx table of summary stats of the auto data.
My problem is that when I try to force the formatting of certain variables to %12.0fc it works for some rows but not others. It seems to be related to the numbers taking up relatively more spaces but I thought that 12 should be enough. I experimented with other total numbers of spaces: 9,10 but it does not help.
I have put !!! in the comments before the lines of code not working as expected.

version 16
set more off
sysuse auto, clear

* create a new var called model to add a nested structure to the data
gen model = substr(make,1,strpos(make," ")-1)
* Subaru is the only one with a single word
replace model = make if make == "Subaru" 
* remove full stops
replace model = subinstr(model,".","",.)
la var model "Model"

* collect all num vars in a macro
ds, has(type numeric)
local vars r(varlist)
* save number of obs in a macro to report in a single line at the bottom of the summary stats table
qui count 
local obs = r(N)
* save number of models in a macro as well
qui levelsof model, l(models) clean
local count_models: word count `models'
* start docx file
putdocx begin
qui tabstat `vars', stats(mean sd min p50 max) save
mat out = r(StatTotal)'
local rows = rowsof(out)
local cols = colsof(out)
* fill an auxilliary matrix with the two counts to add at the bottom
matrix add = J(2,5,.)
matrix add[1,1] = `count_models'
matrix add[2,1] = `obs'
mat out = out \ add
*matlist out

* add table to file 
putdocx table tbl = matrix(out), rownames colnames nformat(%12.3fc) 
putdocx table tbl(.,.), font("Times New Roman") border(all, nil) halign(center)
* name columns
putdocx table tbl(1,1) = ("Variable names"), font("Times New Roman") halign(center)
putdocx table tbl(1,2) = ("Mean"), font("Times New Roman") halign(center)
putdocx table tbl(1,3) = ("Std. Dev."), font("Times New Roman") halign(center)
putdocx table tbl(1,5) = ("Median"), font("Times New Roman") halign(center)
* name rows with var labels
local c = 2
foreach v of local vars {
local lab: var lab `v'
putdocx table tbl(`c',1)= ("`lab'"), font("Times New Roman")
local ++c
}
* adjust formatting of certain variables and columns as needed
* !!! price is not working
putdocx table tbl(2,.), nformat(%12.0fc)
* !!! this one works for all other vars except weight
forv i = 4/6 {
putdocx table tbl(.,`i'), nformat(%9.0fc)
}
* add the model and make labels
local c = `rows' + 2
putdocx table tbl(`c',1) = ("Models"), font("Times New Roman")
putdocx table tbl(`c',.), nformat(%6.0fc)
* replace the missing dots with blanks on that row
forv i = 3/6 {
putdocx table tbl(`c',`i') = ("")
}
* go to next row
local ++c
putdocx table tbl(`c',1)= ("Makes"), font("Times New Roman")
putdocx table tbl(`c',.), nformat(%6.0fc)
forv i = 3/6 {
putdocx table tbl(`c',`i') = ("")
}
* add borders
putdocx table tbl(1,.), border(top, thick) border(bottom, single) border(start, nil) border(end, nil)
local last = rowsof(out) + 1
putdocx table tbl(`last',.), border(bottom, thick) border(start, nil) border(end, nil)
* close and save
putdocx save sysauto_summ_stats, replace

What works is to not add a nformat option in the initial *add table to file line

putdocx table tbl = matrix(out), rownames colnames

and format the other lines separately:

* adjust format of remaining lines in cols 2 and 3
local last = `rows' + 1
forv c = 3/`last' {
putdocx table tbl(`c',2), nformat(%9.3fc)
putdocx table tbl(`c',3), nformat(%9.3fc)
}

This solution seems not very elegant to me and I still do not see why the original approach does not work. Any suggestion to make this code more compact and efficient are also very welcome.

Thank you!

[Maria Boutchkova]

The Stata Tech Suport team got back to me to explain:
"I checked with the developer who wrote the -putdocx- suite of commands.
He mentioned that the reason you don't see the numeric format being applied is that you originally formatted the whole table with the fc format. Even though this is a valid numeric format, once you apply that option, the resulting cells don't have valid numeric values that can be formatted again. -putdocx- treats the value as a string first, and checks whether it is a valid numeric value. -putdocx- has no way to reformat the string again if the string is not a normal numeric value.
He suggests that the -nformat()- option be used only once for each cell."

version 16
set more off
sysuse auto, clear
 
ds, has(type numeric)
local vars = r(varlist)
 
putdocx begin
tabstat `vars', stat(mean sd min p50 max) save
mat out = r(StatTotal)'
 
putdocx table tbl = matrix(out), rownames colnames
putdocx table tbl(2 7,.), nformat(%12.0fc)
forv i = 4/6 {
putdocx table tbl(.,`i'), nformat(%9.0fc)
}
 
forv col = 2/3 {
  forv row = 3/6 {
    putdocx table tbl(`row', `col'),  nformat(%9.3fc)
      }
  forv rows = 8/12 {
    putdocx table tbl(`rows', `col'),  nformat(%9.3fc)  } }
 
putdocx save report, replace