Trouble printing value in a local in a loop using putexcel

[Daniel Zaas]

Hi,
The following code takes in information from a survey, disaggregates it by region, compares the baseline and endline, and puts it in a table using putexcel. It all works except I am having trouble getting the difference in means to print with the asterisks next to them. I'm sure I am messing up the syntax of the local in the putexcel loop. Any help is greatly appreciated.

 
 *creates a table comparing baseline and endline disaggregated by region.  putexcel set "$tables\Acces_Midline_Tables", sheet("All Variables") modify 	// Sets Excel workbook and sheet 		putexcel B1 = ("Variable") C1 = ("Population Mean") D1 = ("N") E1 = ("Total Difference") F1 = ("Kedougou Mean") G1 = ("N")  H1 = ("Mean Diff") I1 = ("Matam Mean") /// 		J1 = ("N") K1 = ("P-Value") L1 = ("Tambacounda Mean")  M1 = ("N") N1 = ("Mean Diff") O1 = ("Ziguinchor Mean")  P1 = ("N") Q1 = ("Mean Diff") /// 		R1 = ("Kolda Mean")  S1 = ("N") T1 = ("Mean Diff") U1 = ("Sedhiou Mean") V1 = ("N") W1 = ("Mean Diff")		 		 	***local PPI column to be added 		local hhchar = "hhsize male female age"  		 	// overall totals of the variables by household 	 		foreach var in `hhchar' { 			forvalues y = 0/1 { 				sum `var' if ML==`y', detail 				local `var'_label: var label `var' 				scalar `var'_mean=string(r(mean), "%10.3fc") 				scalar `var'_obs`y' = r(N) 			} 			} 	  		local row = 2  		 		foreach var in `hhchar' { 			putexcel B`row' = ("``var'_label'") C`row' = (`var'_mean) D`row' = (`var'_obs1)  			local row = `row'+1 			} 	  	 		//// by region observations and mean at midline 		 		foreach var in `hhchar' { 			forvalues x = 1/6 { 				sum `var' if region==`x' & ML==1, detail 				local `var'_label: var label `var' 				scalar `var'_mean`x'=r(mean) 				scalar `var'_obs`x'= r(N)	 			} 			} 			 	// compares means to baseline by region 	 		foreach var in `hhchar' { 			forvalues y = 1/6 {	 				reg `var' ML if region == `y' // runs regression comparing variable to midline  				test ML 				scalar `var'_p2`y' = r(p)  //saves p value for each region 		scalar `var'_p`y' =string(r(p),"%10.5fc")	// store the p-value of the ttest of the coefficient on the consent variable 		scalar `var'_diff`y' = round(_b[ML], .001) //stores the differnce in means and rounds it to 3 decimal points 			if `var'_p2`y'  <= 0.1 & `var'_p2`y' > 0.05		local `var'_diff`y' = "``var'_diff`y''*"  // makes the *** show up 			if `var'_p2`y'  <= 0.05 & `var'_p2`y' > 0.01 	        local `var'_diff`y' = "``var'_diff`y''**"  			if `var'_p2`y'  <= 0.01					local `var'_diff`y' = "``var'_diff`y''***" 			}	 			} 	 			// enter in scalars into excel by region 	 		local row = 2 		*Add quotes and the `y' to the `var'_diff 		foreach var in `hhchar' { 			putexcel  F`row' = (`var'_mean1) G`row' = (`var'_obs1) H`row' = ("`var'_diff1") I`row' = (`var'_mean2) ///  						J`row' = (`var'_obs2) K`row' = ("`var'_diff2") L`row' = (`var'_mean3) M`row' = (`var'_obs3) N`row' = ("`var'_diff3") /// 						O`row' = (`var'_mean4) P`row' = (`var'_obs4) Q`row' = ("`var'_diff4") R`row'=(`var'_mean5)  /// 						S`row' = (`var'_obs5) T`row' = ("`var'_diff5") U`row' = (`var'_mean6) V`row' = (`var'_obs6) W`row' = ("`var'_diff6") 				local row = `row'+1 				} 				 		/// calculate the mean and p-value of the total sample		 		foreach var in `hhchar' { 			reg `var' ML  // runs regression comparing variable to midline  				test ML 				scalar `var'_p2 = r(p)  //saves p value for each region 				scalar `var'_p=string(r(p),"%10.5fc")	// store the p-value of the ttest of the coefficient on the consent variable 				local `var'_diff = round(_b[ML], .001) //stores the differnce in means and rounds it to 3 decimal points 					if `var'_p2 <= 0.1 & `var'_p2 > 0.05		local `var'_diff = "``var'_diff'*"  // makes the *** show up 					if `var'_p2 <= 0.05 & `var'_p2 > 0.01 		local `var'_diff = "``var'_diff'**"  					if `var'_p2 <= 0.01							local `var'_diff = "``var'_diff'***" 			}	 			 			local row = 2 		 		foreach var in `hhchar' { 			putexcel E`row' = (`var'_diff) 				local row = `row'+1 				}

[William Lisowski]

Wel;come to Statalist.

I think perhaps you want

putexcel E`row' = "``var'_diff'"

While we appreciate that you used CODE delimiters, you seem to have had a disastrous problem with your copy-and-paste - somehow all the newlines were lost, although all the tab characters remain. I produced a readable copy lacking most indentation; in case my suggestion doesn't solve your problem, here's an improved presentation of the code for the next person to look at.

 *creates a table comparing baseline and endline disaggregated by region.  putexcel set "$tables\Acces_Midline_Tables", sheet("All Variables") modify

// Sets Excel workbook and sheet
putexcel B1 = ("Variable") C1 = ("Population Mean") D1 = ("N") E1 = ("Total Difference") F1 = ("Kedougou Mean") G1 = ("N")  H1 = ("Mean Diff") I1 = ("Matam Mean") ///
J1 = ("N") K1 = ("P-Value") L1 = ("Tambacounda Mean")  M1 = ("N") N1 = ("Mean Diff") O1 = ("Ziguinchor Mean")  P1 = ("N") Q1 = ("Mean Diff") ///
R1 = ("Kolda Mean")  S1 = ("N") T1 = ("Mean Diff") U1 = ("Sedhiou Mean") V1 = ("N") W1 = ("Mean Diff")
***local PPI column to be added
local hhchar = "hhsize male female age"

// overall totals of the variables by household
foreach var in `hhchar' {
forvalues y = 0/1 {
sum `var' if ML==`y', detail
local `var'_label: var label `var'
scalar `var'_mean=string(r(mean), "%10.3fc")
scalar `var'_obs`y' = r(N)
}
}

local row = 2
foreach var in `hhchar' {
putexcel B`row' = ("``var'_label'") C`row' = (`var'_mean) D`row' = (`var'_obs1)
local row = `row'+1
}

//// by region observations and mean at midline
foreach var in `hhchar' {
forvalues x = 1/6 {
sum `var' if region==`x' & ML==1, detail
local `var'_label: var label `var'
scalar `var'_mean`x'=r(mean)
scalar `var'_obs`x'= r(N)
}
}

// compares means to baseline by region
foreach var in `hhchar' {
forvalues y = 1/6 {
reg `var' ML if region == `y' // runs regression comparing variable to midline
test ML
scalar `var'_p2`y' = r(p)  //saves p value for each region
scalar `var'_p`y' =string(r(p),"%10.5fc")
// store the p-value of the ttest of the coefficient on the consent variable
scalar `var'_diff`y' = round(_b[ML], .001) //stores the differnce in means and rounds it to 3 decimal points
if `var'_p2`y'  <= 0.1 & `var'_p2`y' > 0.05
local `var'_diff`y' = "``var'_diff`y''*"  // makes the *** show up
if `var'_p2`y'  <= 0.05 & `var'_p2`y' > 0.01
        local `var'_diff`y' = "``var'_diff`y''**"
if `var'_p2`y'  <= 0.01
local `var'_diff`y' = "``var'_diff`y''***"
}
}

// enter in scalars into excel by region
local row = 2
*Add quotes and the `y' to the `var'_diff
foreach var in `hhchar' {
putexcel  F`row' = (`var'_mean1) G`row' = (`var'_obs1) H`row' = ("`var'_diff1") I`row' = (`var'_mean2) ///
J`row' = (`var'_obs2) K`row' = ("`var'_diff2") L`row' = (`var'_mean3) M`row' = (`var'_obs3) N`row' = ("`var'_diff3") ///
O`row' = (`var'_mean4) P`row' = (`var'_obs4) Q`row' = ("`var'_diff4") R`row'=(`var'_mean5)  ///
S`row' = (`var'_obs5) T`row' = ("`var'_diff5") U`row' = (`var'_mean6) V`row' = (`var'_obs6) W`row' = ("`var'_diff6")
local row = `row'+1
}

/// calculate the mean and p-value of the total sample
foreach var in `hhchar' {
    reg `var' ML  // runs regression comparing variable to midline
    test ML
    scalar `var'_p2 = r(p)  //saves p value for each region
    scalar `var'_p = string(r(p),"%10.5fc")
    // store the p-value of the ttest of the coefficient on the consent variable
    local `var'_diff = round(_b[ML], .001) //stores the differnce in means and rounds it to 3 decimal points
    if `var'_p2 <= 0.1 & `var'_p2 > 0.05 local `var'_diff = "``var'_diff'*"  // makes the *** show up
    if `var'_p2 <= 0.05 & `var'_p2 > 0.01 local `var'_diff = "``var'_diff'**"
    if `var'_p2 <= 0.01 local `var'_diff = "``var'_diff'***"
}

local row = 2
foreach var in `hhchar' {
    putexcel E`row' = (`var'_diff)
    local row = `row'+1
}