exporting many tables to excel

[Ali Askary]

Hi , I have a question about how I can export many tables in a good format. I am trying to run regressions for 49 industries (later I need up to 100), and 12 time periods.
I am running my regressions using: [by industry yeargroup, sort : regress ind monday tuesday wednesday thursday friday, noconstant] .
this gives me 49x12 tables, I tried using the asdoc plug with the wide format, however Stata gives me an syntax error. I would like to know if there is another way to get these regression results in nice formatted tables, preferably all industries in 1 table for each time period (thus 12 tables for each time period, containing all the industries). Help is very appreciated

[Attaullah Shah]

You may you like to explore asdocx. It can send as many tables as you want to Excel, Word or LaTeX. The following table presents a summary of the differences between asdoc and asdocx.
asdocxvs asdoc

---

RTF Format	✓	✓
MS Excel	✘	✓
Native docx	✘	✓
LaTeX	✘	✓
Export Graphs	✘	✓
Unicode / Chinese Language	✘	✓
Custom Templates	✘	✓
Flexmat Support	✘	✓
tabmany command	✘	✓
table1 for patient baseline characteristics	✘	✓
codebook	✘	✓
Portable options	✘	✓
Works with putdocx	✘	✓

[William Lisowski]

Almost always when Stata reports a syntax error it means you have made a mistake in the syntax of a command you have tried to run.

Perhaps the place to start is by sharing the commands you ran and the error message Stata gave you, so that there's the possibility of helping you correct the error in your syntax.

The way to do that is to copy your commands and their output from Stata's Results window and paste it into a your post here, using code delimiters [CODE] and [/CODE] to surround it so it's more readable. More generally, the Statalist FAQ provides advice on effectively posing your questions, posting data, and sharing Stata output.

[Ali Askary]

excuse me for the formatting.

the regression that I want to run is

 by industry yeargroup, sort :  regress ind monday tuesday wednesday thursday friday, noconstant

. the problem that I am having is combining this code with the asdoc function. I am able to run the following code, which gives me almost the desired output :

 bysort industry: asdoc regress ind monday tuesday wednesday thursday friday ,  wide

.

however I am not able to combine both so that I have the regressions sorted by industry and yeargroup and also with the noconstant and wide part added .

I hope it is more clear now

[William Lisowski]

You do not show us the "combined" command that you ran that produced the error message.

[Ali Askary]

excuse me, the code is :

by industry yeargroup, sort : asdoc regress ind monday tuesday wednesday thursday friday, noconstant wide

.

which gives the error message : too many variables specified. this error stay, even when removing most of the day variables

[William Lisowski]

Almost always when Stata reports a syntax error ...

... and probems like this one are is why I wrote "Almost".

The problem is that asdoc apparently does not support more than a single by-group variable.

[added in edit: it's subtle: the output of help asdoc tells us the syntax is

[bysort varname:] asdoc Stata_Commands, [Stata_command_options asdoc_options]

while for most by-able commands, a varlist rather than a varname is allowed. I missed that when I reviewed the help file.]

Here is an example of working around that problem.

// prepare example data
use http://www.stata-press.com/data/r16/psidextract.dta, clear
label define SMSA 1 "Metropolitan" 0 "Non-metro"
label values smsa SMSA
label define SOUTH 1 "Southern" 0 "Non-Southern"
label values south SOUTH
// create a single by-variable encompassing all combinations of smsa and south
egen univ = group(smsa south), label
// run the regressions
bysort univ: asdoc regress lwage tdum1-tdum7, noconstant replace wide

The Word document created looks like this.

[Ali Askary]

thanks for the help, I am just a bit lost how to use this in my case, my data consist of 49 industries. how can I make a label for each industry? I also want a separate table for each year group. is It also possible to see whether the coefficients are significant?

[William Lisowski]

Very often a simple description really isn't clear without more detail, or at a minimum it is too difficult to guess at a good answer from what has been shared. When that happens other members will decide not to answer the question, or ask for an improved presentation that could have been provided to begin with.

Please help us help you. Show example data. Show us what you want your output to be like. The Statalist FAQ provides advice on effectively posing your questions, posting data, and sharing Stata output.

You will find that the time spend preparing a well-formed question will often pay off in a quicker answer that requires less followup.

The asdoc command is complicated, but there is no substitute for reading the documentation. I do not use asdoc, but a quick look at the section on regression output shows that there is a stars option which adds significance stars to the output.

[Ali Askary]

I apologize for the inconvience , thanks for sticking with me! I have come a far way by running this code:

 bysort industry: asdoc regress ind monday tuesday wednesday thursday friday , noconstant wide stars

this gives me the following output :

which is good, I have the stars and the coefficients for each industry for each day. but it is now run for the whole time period I have, but I want to have a separate table for each year group I have.

the code I have tried running is :

 bysort industry: asdoc regress ind monday tuesday wednesday thursday friday if yeargroup =1 , noconstant wide stars

(I would then run this regress for every yeargroup I have. but then I get the error message invalid syntax.

running this code :

 bysort industry yeargroup :  regress ind monday tuesday wednesday thursday friday , noconstant

gives me the correct output in STATA, but when I add the asdoc function as follows:

 bysort industry yeargroup : asdoc  regress ind monday tuesday wednesday thursday friday , noconstant replace wide

, I get the error message : too many variables. I am not sure how to work around this one using labels, since I have 12 year groups and 49 industries. Thanks again for your help!

[Ali Askary]

my output was not copied correctly so I have added the screenshot below!

[William Lisowski]

I understand now that you want 12 separate tables, one for each value of yeargroup. To accomplish that you would have to convince asdocx to create 12 separate tables, one for each value of yeargroup, each containing the results of regressions for each of the 49 industries. It should be possible to do that within a single document, but I am unable to persuade asdoc to do that.

Instead, the following code demonstrating creating two tables in separate documents.

// prepare example data
use http://www.stata-press.com/data/r16/psidextract.dta, clear
label define SMSA 1 "Metropolitan" 0 "Non-metro"
label values smsa SMSA
label define SOUTH 1 "Southern" 0 "Non-Southern"
label values south SOUTH
// run the regressions
forvalues sm = 0/1 {
    preserve
    keep if smsa==`sm'
    local ttl : label (smsa) `sm'
    bysort south: asdoc regress lwage tdum1-tdum7, noconstant wide save(table_`sm') title(`ttl') replace
    restore
}

[Ali Askary]

Thank you very much for your time and help! I think I am getting there. I ran the code as following :

  forvalues sm = 0/12{
  2. preserve
  3. keep if yeargroup= `sm'
  4. local ttl : label (yeargroup) `sm'
  5. bysort industry: asdoc regress ind monday tuesday wednesday thursday friday, noconstant wide save(table_`sm') title(`t
> tl') replace
  6. restore
  7. }

however, I get the error that it is already preserved. I tried restoring again but that does not seem to help. Is there something wrong with my code?

[William Lisowski]

Did the error message come out immediately after the output you show in post #12, or did you omit some output before it that you have decided we do not need to see? The Statalist FAQ I linked to in Post #8 is clear on that:

12.1 What to say about your commands and your problem

Say exactly what you typed and exactly what Stata typed (or did) in response. N.B. exactly!

In this case you would have achieved this by copying everything from your Results window starting with the commands you ran and ending with the error message from Stata.

I am going to abandon preserve and restore because I cannot tell where your problem arose. Instead, try

// save the data as it is
tempfile mydata
save `mydata'
// run the regressions
forvalues sm = 0/12 {
    use `mydata', clear
    keep if yeargroup==`sm'
    local ttl : label (yeargroup) `sm'
    bysort industry: asdoc regress ind monday tuesday wednesday thursday friday, noconstant wide save(table_`sm') title(`ttl') replace
}

[Ali Askary]

this is how I got the error message from post #12, it came immediately after the output. I am not sure why since I have not yet tried to use the preserve function for anything else.

 forvalues sm = 0/12{
  2. preserve
  3. keep if yeargroup= `sm'
  4. local ttl : label (yeargroup) `sm'
  5. bysort industry: asdoc regress ind monday tuesday wednesday thursday friday, noconstant wide save(table_`sm') title(`t
> tl') replace
  6. restore
  7. }
already preserved
r(621);

I tried running the code with the tempfile as following:

tempfile mydata

. save `mydata'
file /var/folders/lw/1qvqt29n5f5djq4h1g8npm7r0000gn/T//S_66527.000003 saved

. forvalues sm = 0/12 {
  2. use `mydata', clear
  3. keep if yeargroup==`sm'
  4. local ttl : label (yeargroup) `sm'
  5.  bysort industry: asdoc regress ind monday tuesday wednesday thursday friday, noconstant stars wide save(table_`sm') t
> itle(`ttl') replace
  6. }
(1,221,766 observations deleted)
__000009 not found
r(111);

it ended up deleting al my data.