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.
Code:
*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 }
0 Response to Trouble printing value in a local in a loop using putexcel
Post a Comment