Stata to excel

[Janet Lewis]

Hey there,

I have over 60 different crosstabs which I would like presented in excel:

table v1, stat(mean v2 v3)

Each cross tab is for different variables and so I was hoping there was quick way to do this as opposed to specifying columns etc.

When I simply copy and paste the format isn't right in excel.

Thanks

[Leonardo Guizzetti]

Here is one of many approaches. If -v1- always changes, then of course this would be amenable to a loop structure.

sysuse auto

table foreign, stat(mean price) stat(mean length)
collect style cell var[price]#result[mean] , nformat(%6.1f)
collect style cell var[length]#result[mean] , nformat(%4.2f)
collect export "results.xlsx", as(xlsx) replace sheet("Foreign") cell(A1)

table rep78, stat(mean price length)
collect style cell var[price]#result[mean] , nformat(%6.1f)
collect style cell var[length]#result[mean] , nformat(%4.2f)
collect export "results.xlsx", as(xlsx) modify sheet("Repair") cell(A1)

[Janet Lewis]

Leonardo Guizzetti Thank you, this worked well. Yes, v1 always changes. Could you pleas assist with what the loop code would look like. Thanks again - I am a beginner at Stata and this has saved me lots of time!

[Leonardo Guizzetti]

Certainly.

sysuse auto

foreach v of varlist foreign rep78 {
  local sheetname = strtitle("`v'")
  di as text "Processing variable `v', storing results in sheet (`sheetname')"
 
  table `v', stat(mean price) stat(mean length)
  collect style cell var[price]#result[mean] , nformat(%6.1f)
  collect style cell var[length]#result[mean] , nformat(%4.2f)
  collect export "results.xlsx", as(xlsx) modify sheet("`sheetname'") cell(A1)
  collect drop Table // drop collection when done, not strictly necessary
}

[Janet Lewis]

Thanks Leonardo Guizzetti, this seems to work although is there a way I can append the table? When I run this code the excel file is updated with only the most recent tab.

[Leonardo Guizzetti]

You're welcome. I don't know what you mean by append the table. Running the code above stores each table into its own sheet. In the example, 2 sheets are created.

Are you running the code inside of a do-file, and not running the code line-by-line?

[Janet Lewis]

Thnak you Leonardo Guizzetti

foreach v of varlist a b {
  local sheetname = strtitle("`v'")
  di as text "Processing variable `v', storing results in sheet (`sheetname')"
 
table `v', stat(mean c d)
collect style cell var[c]#result[mean] , nformat(%6.1f)
collect style cell var[d]#result[mean] , nformat(%4.2f)
collect export "results.xlsx", as(xlsx) modify sheet("sheetname") cell(A1)
collect drop Table // drop collection when done, not strictly necessary
}

}

Here's what I have done. The excel file only includes the tabs for the last variable 'b'. I am running this in a do file, not line by line. Cannot figure out what isn't right...

[Leonardo Guizzetti]

Hi Janet,

There is a typo, evidently when you typed (not copied and pasted) the code. Please note the single left and right quotes which are used for macro substitution. The left tick is especially tricky for new users. It is sometimes called a backtick (`) and on most US QWERTY keyboards is found on the tilde (~) key left of the the number 1. The single quote (right quote) is on the same key as the double quote (").

foreach v of varlist foreign rep78 {
  local sheetname = strtitle("`v'")
  di as text "Processing variable `v', storing results in sheet (`sheetname')"
 
  table `v', stat(mean price) stat(mean length)
  collect style cell var[price]#result[mean] , nformat(%6.1f)
  collect style cell var[length]#result[mean] , nformat(%4.2f)
  collect export "results.xlsx", as(xlsx) modify sheet("`sheetname'") cell(A1)
  collect drop Table // drop collection when done, not strictly necessary
}

[Janet Lewis]

Thank you for explaining this to me Leonardo Guizzetti. You have been very helpful. The code works now, and does exactly what I wanted it to do. Thanks

[Leonardo Guizzetti]

You're welcome.

[Janet Lewis]

HI again Leonardo Guizzetti, would you be able to assist me with including frequency in the tables as opposed to just means? Thanks

[Leonardo Guizzetti]

You can experiment with the following, if that's what you had in mind.

sysuse auto
table foreign, stat(frequency) stat(percent)

[Janet Lewis]

Dear Leonardo Guizzetti
Thank you again for your assistance. Sorry to bother you again, though how would one code t-tests for significance between cross tabs?

[Leonardo Guizzetti]

Someone else may offer a working example, but that's not a one-line command. You can access everything returned by the t-test by examining the returned results in r(), run -return list- right after your -ttest- command. Those results can in turn be exported using -putexcel- for example.

[Janet Lewis]

Thanks Leonardo Guizzetti Is it possible to incorporate the chi2 option in the following code such that for each tab the test stat is presented in the output window. So I wouldnt have to manually write a code for each crosstab.

oreach v of varlist foreign rep78 {   local sheetname = strtitle("`v'")   di as text "Processing variable `v', storing results in sheet (`sheetname')"     table `v', stat(mean price) stat(mean length)   collect style cell var[price]#result[mean] , nformat(%6.1f)   collect style cell var[length]#result[mean] , nformat(%4.2f)   collect export "results.xlsx", as(xlsx) modify sheet("`sheetname'") cell(A1)   collect drop Table // drop collection when done, not strictly necessary }

f