Rouding Contents of matrix and exporting contents to excel

[lucasferreira]

Hello ,

I have a couple of questions for the Stata experts in this forum. I apologize if you deem them to be basic questions. I use Stata 13 .

QUESTION-1:
==========
After successfully running a survey estimation command (to get counts) (ONE WAY TABULATE COMMAND), how can round contents of a matrix before exporting them to excel using putexcel (new command in Stata 13).

After searching the internet, I found a trunc function, but I am not able to use it.

My commands so far are as follows

svy, subpop(MAIN): tabulate  VAR1 , count 
mat p = e(b)'* e(N_subpop)  
mat q=e(Row)'
putexcel A1=("NAME") B1=("COUNT") B2=matrix(p) A2=matrix(q, rownames)  using results, modify

My goal is to round the contents of matrix before I use the putexcel command.

Can anyone please let me know.Thank you for your time .

QUESTIONS-2:
===========
After running a survey estimation command (to get counts) (TWO WAY TABULATE COMMAND), how can I export the first column of the resulting 4 x 4 table to excel using the putexcel command.

If I use the matrix e(b), it lists all the cells but not specifically cells in the first column
My code is as follows

webuse nhanes2b, clear
svy: tabulate race sex , per 
**********RESULTS FROM THE COMMAND******************************
Number of strata   =        31                 Number of obs      =      10351
Number of PSUs     =        62                 Population size    =  117157513
                                               Design df          =         31
    
1=white,    
2=black,    1=male, 2=female   
3=other    Male  Female   Total
    
White    42.25   45.66   87.92
Black    4.35   5.201   9.551
Other    1.33   1.204   2.534
     
Total    47.94   52.06     100
*************************************************************************************
    

mat list e(b)

e(b)[1,6]
          p11        p12        p21        p22        p31        p32
y1  .42254909  .45660537  .04349737  .05200855  .01330376  .01203586

I am interested in exporting only p11, p21 and p31 to excel, and I am not sure how can I do it

Can anyone please let me know.Thank you for your time .

thank you again,
Sincerely,
Lucas

[Mike Lacy]

1) Rounding:
-help round- will bring up help that documents the round() function. Searching in Google on /Stata round/ returns the same help.
This function can be applied to individual elements of a Stata matrix, i.e., -matrix p[1,1] = round(x[1,1], 0.001)
You can then write a loop using -forvalues- to loop over the rows and columns of a matrix and apply round() to each element.
It is also possible to move your Stata matrix into Mata (Stata's matrix language). In that language, round() can be applied to an entire matrix.
However, at this point in your Stata career, Mata is probably not where you want to go.

2) You second question, about using -putexcel- on individual elements of a matrix, suggests that perhaps you are not familiar with how to index
a matrix in Stata. Per my response to the first question, p[1,1], p[2,1], etc. will reference those items, and -putexcel- will accept such references, e.g.
-putexcel A1 = p[1,1]

-help matrix- might be useful to you.

I hope I've correctly understood your difficulty.

No need to apologize for asking elementary questions, but if you think the question is elementary, trying Stata's help can often be useful.

Regards, Mike

[Nick Cox]

Sorry to disagree, but code like

 round(x[1,1], 0.001)

can only result in puzzlement and frustration at least some of the time. Something like

. di round(1/7, .001)
.143

may lead you to think that worked, but if you look closely

. di %23.18f  round(1/7, .001)
   0.143000000000000020

the result is not what you want. Sometimes you never notice, but often the extra digits show up to embarrass you, like dirt on a shoe.

Decimal rounding is only possible, in most cases, as an approximation given that Stata necessarily works with binary representations of numbers. Related issues arose repeatedly on the old Statalist and are documented under the heading precision. (Whenever a decimal corresponds to a number that can be held exactly as binary, things are OK: examples are 1/2, 1/4, 3/4, 1/8, etc.)

I don't use Excel unless I have to and I am certainly not a proficient user, but why not export to Excel and format the results as you want them there?

If you prefer a Stata solution then exporting as text using display formats is the only exact way to get this.

[lucasferreira]

Dear Mike, Nick and Statalist members,

This question helped me enrich my stata knowledge and solve my problem.

The answer for my question-1(rounding) helped me solve the rounding issue.

Re: Question-2: I am still struggling to use elements of matrix to be copied to excel using putexcel. Any advice will be much appreciated.

My stata command is as follows

webuse nhanes2b, clear
svy: tabulate race sex , per
ereturn li
mat list e(b)

* THE FOLLOWING WORKS

putexcel A1=matrix(e(b)) using results, replace

* THIS FOLLOWING DOESNT WORK

putexcel A1=matrix(e(b),p[1,1]) using results, replace

p not found
r(111);

end of do-file

Comming to Nick's comments regarding rounding in stata:

Thank you very much for the example. I am rounding the elements mainly for demonstration purpose only (during presentations etc). The results will not be used for further analysis.
But I will remember the advice during further analysis.

Thank you again Nick and Mike for your time and advice.


Sincerely,
Lucas

[Joseph Coveney]

Take a look at the example code below.

version 13.1

clear *
set more off

webuse nhanes2b
svy: tabulate race sex, percent

*
* Begin here
*
matrix define B = e(b)
matrix define Export = (B["y1", "p11"],  B["y1", "p21"],  B["y1", "p31"])

putexcel A1 = matrix(Export) using Results, sheet("Beta_hat") replace

exit

See help subscripting or help colnumb() for more information.

[lucasferreira]

Got it, thank you Joseph Convey.
Lucas

[Bram Hogendoorn]

I would take the Mata route, as suggested by Mike Lacy.
For example for matrix p:

mata : Mrounded=round(st_matrix("p"),.01) // round all values to two decimals in Mata
mata : st_matrix("p",Mrounded)            // take them back to Stata

[Nick Cox]

Rounding is really a formatting operation, as already discussed in #3. Mata can't solve this differently or better.

Use an appropriate decimal display format.

Explained in many other places too e.g. now https://journals.sagepub.com/doi/abs...urnalCode=stja

[Elena Draghici]

would there be a way to round my table before exporting it to excel?

  table ( sex ) () (), statistic(frequency) statistic(percent, across(sex)) statistic(mean income) statistic(median income) statistic(sd income) nformat(%9.0fc)

where sex is a binary and income is continuous.

collect export "file_path", as(xlsx) sheet(1) cell(A1) modify

So I would like Stata to produce rounded counts in the table, without me having to use Excel to round. I do not require random rounding, just controlled rounding to base 10. Also, if there are suggestions on how to round different variables to different bases, that would be helpful too. Thank you!

[Nick Cox]

#9 I don't think so. Rounding is in principle akin to formatting, but I don't know any tabulation command or option that rounds to integer multiples, i.e. more coarsely than to integers. It's a good detail for a wishlist, as this seems a common request, partly I suspect to avoid spurious precision but mostly I gather to suppress detail that might be informative in an unwanted manner.

I would be happy to be told otherwise.

[Elena Draghici]

yes, that would be precisely what I would use it for, Thank you!

[Andrew Musau]

estout from SSC is an option. The following illustrates:

sysuse auto
mkmat headroom gear_ratio in 1/10, mat(X)
mat l X
esttab mat(X, fmt(%3.0f)), nomtitle

Res.:

. mat l X

X[10,2]
       headroom  gear_ratio
 r1         2.5   3.5799999
 r2           3        2.53
 r3           3   3.0799999
 r4         4.5   2.9300001
 r5           4   2.4100001
 r6           4        2.73
 r7           3   2.8699999
 r8           2   2.9300001
 r9         3.5   2.9300001
r10         3.5   3.0799999

. 
. esttab mat(X, fmt(%3.0f)), nomtitle

--------------------------------------
                 headroom   gear_ratio
--------------------------------------
r1                      3            4
r2                      3            3
r3                      3            3
r4                      5            3
r5                      4            2
r6                      4            3
r7                      3            3
r8                      2            3
r9                      4            3
r10                     4            3
--------------------------------------

To export to Excel:

esttab mat(X, fmt(%3.0f)) using myfile.csv,  nomtitle

[Elena Draghici]

Hi Andrew, thank you for posting the tip. I am looking to round my sheets to base 10 though. Is that possible with esttab/estout?

[Andrew Musau]

What do you mean rounding to base 10? I can understand rounding to the nearest whole number in base 10 or rounding to the nearest 10 in base 10.

[Elena Draghici]

so for example, if I have a frequency count of 8765, then it would get rounded to 8770. Or 8763 would become 8760.