putexcel in foreach loop

[Anja Heimann]

I am having some issue writing my data to excel in a for loop. I am very new to macros and loops.
Basically what I need is a separate excel file for each dependent variable, for each year. Named after the "dependent variable _ year".
This is the code I have:

local depvar X Y
local Year "2010" "2011"
foreach yr of local Year{

        foreach i of local depvar{
            svy: proportion `i' if Year==`yr'
            matrix `i'=r(table)
            local n=e(N)

            matrix `i'=J(1,9,.)
            matrix colnames `i'="Percentage" "SE" "Lower95%" "Upper95%" "Difference" "SE" "Pvalue" "stars" "Base"
            matrix rownames `i'=`i'
            matrix `i'[1,1]=result[1,2]*100
            matrix `i'[1,2]=result[2,2]*100
            matrix `i'[1,3]=result[5,2]*100
            matrix `i'[1,4]=result[6,2]*100
            matrix `i'[1,9]=`n'
            putexcel set `i'_`yr'.xlsx, replace
            local row=10

* write the column names and the row with just the dependent variable
            putexcel A`row'=matrix(`i'),names
        }        
    }

It runs if I do not loop over the years, and just run it on my data from all years from the same time. But I need this subsample output.
I tihnk the mistake is in this line:

            putexcel set `i'_`yr'.xlsx, replace

As when I run this do file, I keep getting excel file output named "X_Year.xlsx", but not "X_2010.xlsx".

Does anyone have any ideas?

Oh, and I have to use the putexcel function. Even though using estout and then formatting in Excel later would be easier.

[William Lisowski]

When defining a local or global macro, if Stata encounters a double quote as the first character, it will expect a matching double quote as the final character, and will remove both.

. local y0 2010 2011

. local y1 "2010" "2011"

. local y2 `" "2010" "2011" "'

. macro list _y0 _y1 _y2
_y0:            2010 2011
_y1:            2010" "2011
_y2:             "2010" "2011"

In your code, replace

local Year "2010" "2011"

with

local Year 2010 2011

and all will be well.

[Anja Heimann]

Thanks for pointing that out William.

Unfortunately now the code still doesn't do what I would like. It runs, but it won't create these excel files named "X_2010.xlsx" and "Y_2010.xlsx". Instead, It produces files named "X_Year.xlsx" and consequently over-writes the results when is supposed to do the svy: proportion command for 2011.

[Andrew Musau]

You do not define a matrix "result" in #1, but you refer to it. Here are some corrections highlighted in red.

local depvar X Y
local Year 2010 2011
foreach yr of local Year{

        foreach i of local depvar{
            svy: proportion `i' if Year==`yr'
            matrix result=r(table)
            local n=e(N)

            matrix `i'=J(1,9,.)
            matrix colnames `i'="Percentage" "SE" "Lower95%" "Upper95%" "Difference" "SE" "Pvalue" "stars" "Base"
            matrix rownames `i'=`i'
            matrix `i'[1,1]=result[1,2]*100
            matrix `i'[1,2]=result[2,2]*100
            matrix `i'[1,3]=result[5,2]*100
            matrix `i'[1,4]=result[6,2]*100
            matrix `i'[1,9]=`n'
            putexcel set `i'_`yr'.xlsx, replace
            local row=10

* write the column names and the row with just the dependent variable
            putexcel A`row'=matrix(`i'),names
        }        
    }

[Anja Heimann]

Great, thanks both. That fixed it.

[Andrés Lahur Talavera Cuya]

1

[Andrés Lahur Talavera Cuya]

Unexpected error from -putexcel nformat-
I’m having a problem with -putexcel cell, nformat()-.
I wrote -putexcel `Cell' = "`CellContents'",nformat(#,##) - and Stata did not change the format in excel cells.

Hello, good morning, a consultation. I am trying to replicate table 2 with putexcel from the Peru Permanent Employment Survey report. It turns out that my excel file gets corrupted when running multiple times; plus it doesn't make the cell formatting changes that I require: one decimal and spacing for thousands. What do you think could be happening? What do you suggest for cell formatting and everything else?
please
thank you

[William Lisowski]

Your first problem is that by enclosing `CellContents' in quotation marks, you are creating a text cell in Excel, and the numeric format is ignored. Your second problem is that the Excel format specified will not yield what you want.

Consider the following example.

. putexcel set "example.xlsx", replace
Note: file will be replaced when the first putexcel command is issued

. local CellContents 1234.4321

. putexcel A1 = "`CellContents'", nformat(#,###.#)
file example.xlsx saved

. putexcel B1 =  `CellContents' , nformat(#,###.#)
file example.xlsx saved

The result of this in Excel is


As you see, B1 has one decimal and a thousands separator. In my copy of Excel, the decimal separator displays as as period and the thousands separator displays as a comma, which are the standard separators in the US. How B1 would display in your copy of Excel is a function of some combination of settings in Excel and settings on your operating system. If your installation of Excel normally displays decimal separators as a comma and thousand separators as a space I expect it would for this example as well.

For the reference of other readers, I've copied below from the output of help putexcel the description of the nformat option, with a table of formats as defined in the Excel application. What it doesn't say is that "." is how Excel formats specify the location of the decimal separator and "," is how Excel formats specify the presence of a thousands separator, but the actual characters displayed by Excel are a result of "localization" of Excel to your computing environment.

 nformat(excelnfmt) changes the numeric format of a cell range.  Codes for commonly used formats are
        shown in the table of numeric formats in the Appendix.  However, any valid Excel format is
        permitted.  Formats are formed from combinations of the following symbols.

                                                   Cell      Fmt      Cell
     Symbol        Description                    value     code  displays
    -----------------------------------------------------------------------------------------------------
     0             Digit placeholder (add zeros)   8.9      #.00      8.90
     #             Digit placeholder (no zeros)    8.9      #.##       8.9
     ?             Digit placeholder (add space)   8.9      0.0?       8.9
     .             Decimal point
     %             Percentage                       .1         %       10%
     ,             Thousands separator           10000     #,###    10,000
     E- E+ e- e+   Scientific format          12200000  0.00E+00  1.22E+07
     $-+/():space  Display the symbol               12     (000)     (012)
     \             Escape character                  3      0\!         3!
     *             Repeat character                  3        3*    3xxxxx
                          (fill in cell width)                        
     _             Skip width of next character   -1.2      _0.0       1.2
     "text"        Display text in quotes         1.23  0.00 "a"    1.23 a
     @             Text placeholder                  b   "a"@"c"       abc
    -----------------------------------------------------------------------------------------------------

[Andrés Lahur Talavera Cuya]

thank you

[Andrés Lahur Talavera Cuya]

I wrote -putexcel `Cell' = `CellContents',nformat(#,###.#)- and Stata did not show decimal zero in Excel cells.
I made the regional configuration in control panel of my pc.

[Andrés Lahur Talavera Cuya]

Resolved. Thank you very much William Lisowski
putexcel `Cell' = `CellContents',nformat(#,###.0)