st: RE: tricky putexcel syntax

[Joe Canner <jcanner1@jhmi.edu>]

Nick,

-putexcel- is new to Stata 13, which you should have mentioned.  I don't have Stata 13, but based on the documentation, here is what I would do:

local row 1
foreach x of varlist `funct' {
         tabulate var119 `x', chi2 exact V gamma
        local ++row
        putexcel A`row'=`x' using chi2.xlsx, sheet("var119")
        putexcel B`row'=`r(chi2)' using chi2.xlsx, sheet("var119")
}

I would also consider adding another loop to automate the use of the different sheets:

foreach sheet in "var119" "var5_r" ... {
   local row 1
   foreach x of varlist `funct' {
         tabulate `sheet' `x', chi2 exact V gamma
        local ++row
        putexcel A`row'=`x' using chi2.xlsx, sheet("`sheet'")
        putexcel B`row'=`r(chi2)' using chi2.xlsx, sheet("`sheet'")
  }
}

Regards,
Joe Canner
Johns Hopkins University School of Medicine

-----Original Message-----
From: owner-statalist@hsphsun2.harvard.edu [mailto:owner-statalist@hsphsun2.harvard.edu] On Behalf Of Nick Bornschein
Sent: Tuesday, March 18, 2014 8:36 AM
To: statalist@hsphsun2.harvard.edu
Subject: st: tricky putexcel syntax

Hello Stata list,

I'm trying a little tricky putexcel command with the tab syntax command. 
Hope someone can help me (I'm sure).
First the code:

------------

local funct (here are 20 variables included)

putexcel A1=("Company Type / Industry") using chi2.xlsx, sheet("var119") 
replace
putexcel A1=("Company Size") using chi2.xlsx, sheet("var5_r") modify
putexcel A1=("Company Spread") using chi2.xlsx, sheet("var90") modify
putexcel A1=("Company Turnover") using chi2.xlsx, sheet("var4") modify
putexcel A1=("Accounting Standard") using chi2.xlsx, sheet("var796") modify
putexcel A1=("Currency Number") using chi2.xlsx, sheet("num_curr_r") modify

foreach x of varlist `funct' {
         tabulate var119 `x', chi2 exact V gamma
         putexcel ...
}

-----------

Aus you can see I create an Excel file with some sheets. After that I 
calculate a Chi2 statistic with my first variable "var119" in a loop and 
now I want to put the results to the same sheet. In this sheet A1 is the 
title. Now I want all variable names I use in the loop, defined via 
"local funct..." starting in A3 to A4, A5 and so on (20 new lines with 
variable labels). Column B (first should be B3) should contain the chi2 
value, which I know is available via return list command, but I also 
dont know how to put the values step by step zo the next line because of 
the loop.

Best
-Nick
*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/faqs/resources/statalist-faq/
*   http://www.ats.ucla.edu/stat/stata/

*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/faqs/resources/statalist-faq/
*   http://www.ats.ucla.edu/stat/stata/