how to loop with tab2xl?

[vicky chann]

I tried to use tab2xl in a loop to generate sheets faster, but after running the code there's no file generated, stata didn't report error as well.

I created three dummy variable apple,pear and orange, and I wanted to tab the variable "list" if the dummy variable is 1:

input str10 fruit
apple
pear
orange
end
levelsof fruit, local(fruit)

foreach a of local fruit {
tab2xl list if `a' == 1, using "test3.xls", sheet("`a'") row(1) col(1) append excel($append)
}

[Clyde Schechter]

I find it difficult to believe that Stata gave you no error messages. Your -tabxl- command has three syntax errors, and when I ran your code, Stata gave me error messages for all three (as I removed the errors one at a time.)

Anyway, the errors are:

1. There should be no comma before -using-. (By the way, this is a general principle of Stata syntax and is not peculiar to this command. When a command contains a -using- clause, it must come before the comma that sets off options.)
2. There is no option -append- for -tab2xl-.
3. There is no option -excel()- for -tab2xl-.

When those three errors are corrected, the code runs and produces text3.xls, with three tabs, each populated with the appropriate table.

[vicky chann]

I find it difficult to believe that Stata gave you no error messages. Your -tabxl- command has three syntax errors, and when I ran your code, Stata gave me error messages for all three (as I removed the errors one at a time.)

Anyway, the errors are:

1. There should be no comma before -using-. (By the way, this is a general principle of Stata syntax and is not peculiar to this command. When a command contains a -using- clause, it must come before the comma that sets off options.)
2. There is no option -append- for -tab2xl-.
3. There is no option -excel()- for -tab2xl-.

When those three errors are corrected, the code runs and produces text3.xls, with three tabs, each populated with the appropriate table.

Thank you so much Clyde, I've modified my code to
list list apple orange pear

+-------------------------------+
| list apple orange pear |
|-------------------------------|
1. | jenny 1 0 0 |
2. | fred 0 1 0 |
3. | benny 0 0 1 |
+-------------------------------+

input str10 fruit
apple
pear
orange
end
levelsof fruit, local(fruit)

foreach a of local fruit {
tab2xl list if `a' == 1 using "test3.xls", sheet("`a'") row(1) col(1)
}

But the same thing happens, state ran the code but did not export anything. I am using Stata 17

[Clyde Schechter]

I'm afraid I can't replicate your problem. The code you posted is a bit jumbled up in #3, with everything on one line. Also it -list-s the contents of the data set before you add the fruit variable to it, but after I clean up those details, the code runs just fine. I, too, am using Stata 17, MP4, for Windows.

. clear*

. input str5 list byte(apple orange pear)

          list     apple    orange      pear
  1. "jenny"   1   0   0
  2. "fred"    0   1   0
  3. "benny"   0   0   1
  4. end

. list, noobs clean

     list   apple   orange   pear  
    jenny       1        0      0  
     fred       0        1      0  
    benny       0        0      1  

.  
. input str10 fruit

          fruit
  1. "apple"
  2. "pear"
  3. "orange"

. list, noobs clean

     list   apple   orange   pear    fruit  
    jenny       1        0      0    apple  
     fred       0        1      0     pear  
    benny       0        0      1   orange  

.
.
. levelsof fruit, local(fruit)  
`"apple"' `"orange"' `"pear"'

. foreach a of local fruit {
  2.     tab2xl list if `a' == 1 using "test3.xls", sheet("`a'") row(1) col(1)
  3. }
(0 observations deleted)
file test3.xls saved
(0 observations deleted)
file test3.xls saved
(0 observations deleted)
file test3.xls saved

.
.
end of do-file

The only explanation I can think of is that you are running the code one line at a time, or in chunks. In particular, I think you ran the -levelsof- fruit command separately from the subsequent -foreach- loop. Doing that, you would get the results you are describing: no output at all. That's because when you run single lines or blocks of lines from a do-file, each such line or block is, in Stata's eyes, a separate do-file. And once that separate do-file terminates, any local macros defined in it disappear. So, if you stop the code between -levelsof- and -foreach-, then local macro fruit gets defined, but then immediately goes away. When you then get to -foreach a of local fruit-, local fruit being non existent, Stata sees that as a loop with nothing to do, and it just skips over the loop.

This is a general principle in Stata: when you are running code that uses local macros, you must not interrupt the code between the time a local macro is defined and the time it is used. If you do interrupt it, the local macro will go away and the subsequent use of it will fail.

Run the entire code from beginning to end with no interruptions, and I'm confident you will get the results you are looking for.

[vicky chann]

sorry for the confusion I think there was copy paste issue just now that my code all in one line. Thank you for pointing out the separation problem. As suggested, I ran the entire code, and everything is the same, but there's an error saying "if not allowed".

 clear*

. 
. input str5 list byte(apple orange pear)

          list     apple    orange      pear
  1. "jenny"   1   0   0
  2. "fred"    0   1   0
  3. "benny"   0   0   1
  4. end

. 
. 
. input str10 fruit

          fruit
  1. "apple"
  2. "orange"
  3. "pear"

. 
. list, noobs clean

     list   apple   orange   pear    fruit  
    jenny       1        0      0    apple  
     fred       0        1      0   orange  
    benny       0        0      1     pear  

. 
. levelsof fruit, local(fruit)
`"apple"' `"orange"' `"pear"'

. 
. foreach a of local fruit {
  2.         tab2xl list if `a' == 1 using "test3.xls", sheet("`a'") row(1) col(1)
  3. }
if not allowed
r(101);

end of do-file

r(101);

[Clyde Schechter]

I cannot reproduce this problem. The code

clear*
input str5 list byte (apple orange pear)
"jenny"   1   0   0
"fred"    0   1   0
"benny"   0   0   1
end

input str10 fruit
"apple"
"orange"
"pear"

list, noobs clean
levelsof fruit, local(fruit)
foreach a of local fruit {
        tab2xl list if `a' == 1 using "test3.xls", sheet("`a'") row(1) col(1)
}

runs perfectly on my setup. Sorry, but I don't know what else to say.