Looping with Matrix and exporting output to excel file

[rakesh yadav]

Hi I am trying to right a loop to export a number of tables using putexcel. But my code is not working. I have a number of variables that I would like to crosstab with other several variables. And would like to export these crosstabs to excel file.

I would like to see my tables in excel something like this.

Predictor Variable	Wasted			Stunted
Wealth Index (Low)	10.6	14.3	11.83	21.2	25.2	22.53
(1.04)	(1.97)	(0.89)	(1.73)	(2.42)	(1.39)
Wealth Index (Medium)	12.6	10.6	11.83	21	24.9	22.53
(1.35)	(1.34)	(0.89)	(1.74)	(1.96)	(1.39)
761	505	1266	762	506	1268
Wealth Index (High)	12.3	10.7	11.83	25	16.2**	22.53
(1.01)	(1.65)	(0.89)	(1.58)	(2.83)	(1.39)
Residence	11	14.2	11.83	23.2	20.5	22.53
(0.98)	(1.86)	(0.89)	(1.38)	(3.72)	(1.39)
Gender	11.5	12.1	11.83	24.7	20.2	22.53
(1.16)	(1.32)	(0.89)	(1.71)	(1.94)	(1.39)
HH size	18.1	10.8*	11.83	23.1	22.4	22.53
(3.21)	(0.87)	(0.89)	(3.34)	(1.55)	(1.39)

I have been trying the following code to develop basic version of tables but its not working and exports only first table.

Please help of suggest some reference.

putexcel set "D:\Rakesh\CV\FREQS.XLS", sheet(unions)

foreach x of varlist occupation union{
tab `x' race, matcell(cellcounts)
local TotalCount = r(N)
local RowCount = r(r)
local ColCount = r(c)

forvalues row = 1/`RowCount'{
forvalues col = 1/`ColCount'{
local CellContents = cellcounts[`row',`col']
local Cell = char(64 + `col') + string(`row')
putexcel `Cell' = "`CellContents'"
loc w=`RowCount'+2
}
}
}
Thanks in advance.

[Clyde Schechter]

There are two problems. One is aesthetic: by puting "`CellContents'" into the -putexcel- command, the numbers are going into Excel as text rather than numeric information. That may or may not matter, but generally I like to keep numbers as numbers. So I would have -putexcel `Cell' = `CellContents'-, without the surrounding quotes.

More substantively, your loop is just writing the results for union over the same cells that it used for occupation. I think you were aware of this situation, because I see you had a command -loc w = `RowCount' + 2-, but you never did anything with it. And in any case, it's in the wrong place. What you need to do is modify the loop so that when the second variable, union, is being processed, -putexcel- targets rows farther down the spreadsheet, rather than restarting at row 1 again. The following code is one way to do this:

local w = 1
foreach x of varlist occupation union {
    display "`x'", `w'
    tab `x' race, matcell(cellcounts)
    local TotalCount = r(N)
    local RowCount = r(r)
    local ColCount = r(c)

    forvalues row = 1/`RowCount'{
        forvalues col = 1/`ColCount'{
            local CellContents = cellcounts[`row',`col']
            local Cell = char(64 + `col') + string(`=`row' + `w'')
            quietly putexcel `Cell' = `CellContents'
        }
    }
    loc w =`w' + `RowCount'+2
}

That said, you can probably do this more efficiently. -tab- has a -matcell()- option that saves the table cells in a matrix. You can the send the entire matrix to Excel in one -putexcel- command rather than looping over the individual rows and columns.

In the future, when posting code for people to review, be sure to put it between code delimiters. See FAQ #12 for instructions how to do that. It makes it much more readable. Also, when asking for help with code that is not working, you should:

1. Provide example data that demonstrates the problem you are encountering. Use the -dataex- command for that. (Also described in FAQ #12.)
2. Explain what the problem is. "Is not working" just doesn't provide information. In this case, because the problem was relatively easy to spot, this wasn't a major problem, and I was also able to find a data set on the Stata website that would be suitable for testing my solution. But in general, you should provide as much information and help to those who would help you as possible.

[rakesh yadav]

Thanks a lot Clyde for the corrections and your suggestions. Its working perfectly the way i wanted it to be.

[Mahmoodur Rahman]

Dear all,

I have been trying to get an excel output like the following using loops so i can reproduce such calculations for other tables:



FYI: The furthest-most left column having"DCodes, FacilityType and FacTypeDomain" is just for refernce. Not to be included in table.
I am picking up loops in STATA slowing and been reading a lot lately. I just can't fugire out how to loop both rows and columns sequentially to produce multiple two by two tables and then append them and export to excel.
Some notes about the data. The column varibles have Binary responses with 1 and 0. Here 1 is only to be considered for calculations. The Row variables are categorical with levels.

would be great to if i find a way to exclude missing values from this calculations too. But it is not essential and i can do before preparing data for this table, if this clause takes the codes to a different "Non-reproducible" turn.

So far i have managed to write:

 local w = 1 foreach x of varlist DCode FacilityType FacTypeDomain{     foreach y of varlist chl_cure_serv chl_grw_serv ///     chl_vac_serv fp_serv anc_serv del_serv all_with_del ///     all_no_del{                 display "`x'", `w'                 tab `x' `y', matcell(cellcounts)     local TotalCount = r(N)     local RowCount = r(r)     local ColCount = r(c)      forvalues row = 1/`RowCount'{         forvalues col = 1/`ColCount'{             local CellContents = cellcounts[`row',`col']             local Cell = char(64 + `col') + string(`=`row' + `w'')             quietly putexcel `Cell' = `CellContents'             }         }     }     loc w =`w' + `RowCount'+2 }

Unfortunately i got this :

   
    ........     40  1     28  1     27  1     26  0     86  2     35  3     53  2     39  1     45  0     77  18     10  0     40  0     14  0     301 25     91  4     64  0     392 29     ........

I am also trying my level best to comply the etiquette of interacting with this community. Please suggest / advice. I am new, but a fast learner !