Significance Stars in Frames

[Veronika Valcikova]

Dear Stata Enthusiasts

I would like to report, in addition to point estimates, also their significance levels (*, **, ***) and number of observations per country (the last column of the table) The code below runs, though I struggle with adding the asterisks. Any help is much appreciated!

Overall, I have 46 countries and for each one of them I run the regression where I regress the gender of the firstborn child (FirstDaughter ==1 if it is a daughter, FirstDaughter == 0 if it is a son) on a number of control variables which are stored in a local macro socio_demographic_char1. The resulting table shows the point estimates (in columns) per each country (in rows). Please find below a small extract of the resulting table.

label	age	age_sq	age_first_birth	secondary_educated	university_educated	N
Armenia	-0.0145478	0.0000818	0.0052730	0.0525472	0.1137214
Botswana	0.0048397	-0.0000156	-0.0030654	-0.0291215	-0.0341484
Cambodia	-0.0085150	0.0001295	-0.0017587	-0.0302719	-0.0418166
Fiji	-0.0040843	-0.0000255	0.0063371	0.0234825	0.0583551
Suriname	-0.0073907	0.0000415	0.0042340	0.0330114	-0.0639979
Trinidad and Tobago	-0.0059879	0.0000418	0.0028037	0.0153604	0.0187584

local socio_demographic_char1 c.mom_age c.mom_age_sq c.mom_age_first_birth i.mom_secondary_edu i.mom_university_edu

levelsof country, local(countries)
frame create results
frame results{
set obs `=wordcount("`countries'")'
gen label=""
gen age=.
gen age_sq=.
gen age_first_birth=.
gen secondary_educated=.
gen university_educated=.
}
local i 0
foreach country of local countries{
local ++i
regress FirstDaughter `socio_demographic_char1' [pw = mom_perwt] if country == `country', robust
frame results: replace label="`:lab (country) `country''" in `i'
frame results: replace age= `=_b[mom_age]' in `i'
frame results: replace age_sq= `=_b[mom_age_sq]' in `i'
frame results: replace age_first_birth= `=_b[mom_age_first_birth]' in `i'
frame results: replace secondary_educated= `=_b[1.mom_secondary_edu]' in `i'
frame results: replace university_educated= `=_b[1.mom_university_edu]' in `i'

}
frame change results
export excel using "results/exogeneity/exogeneity_new_small", replace firstrow(variables)

* Example generated by -dataex-. For more info, type help dataex
clear
input int country byte(FirstDaughter mom_age) int mom_age_sq byte(mom_age_first_birth mom_secondary_edu mom_university_edu) double mom_perwt
51 0 28  784 22 1 0 10
51 0 33 1089 22 0 1 10
51 1 31  961 24 1 0 10
51 0 31  961 21 1 0 10
51 0 31  961 29 0 1 10
51 0 36 1296 24 1 0 10
51 0 31  961 21 1 0 10
51 . 26  676  . 1 0 10
51 0 35 1225 28 0 1 10
51 1 32 1024 32 1 0 10
51 . 34 1156  . 0 1 10
51 . 29  841  . 1 0 10
51 0 36 1296 25 0 0 10
51 1 32 1024 21 0 1 10
51 . 25  625  . 1 0 10
51 0 24  576 22 0 1 10
51 1 30  900 24 0 1 10
51 1 24  576 21 1 0 10
51 0 22  484 21 0 1 10
51 1 32 1024 27 1 0 10
51 0 24  576 21 1 0 10
51 1 26  676 19 1 0 10
51 . 26  676  . 1 0 10
51 1 26  676 25 1 0 10
51 . 25  625  . 0 0 10
51 . 35 1225  . 1 0 10
51 1 25  625 18 1 0 10
51 . 25  625  . 1 0 10
51 0 27  729 19 1 0 10
51 . 25  625  . 1 0 10
51 1 33 1089 23 0 1 10
51 . 24  576  . 0 1 10
51 1 25  625 18 1 0 10
51 0 22  484 22 1 0 10
51 . 21  441  . 0 1 10
51 1 28  784 23 0 0 10
51 . 34 1156  . 0 1 10
51 . 32 1024  . 0 0 10
51 0 27  729 27 0 1 10
51 . 30  900  . 1 0 10
51 . 33 1089  . 0 1 10
51 0 46 2116 39 0 1 10
51 . 32 1024  . 1 0 10
51 . 25  625  . 0 1 10
51 1 32 1024 22 0 1 10
51 1 20  400 20 1 0 10
51 . 37 1369  . 1 0 10
51 . 33 1089  . 0 1 10
51 1 27  729 23 1 0 10
51 . 27  729  . 1 0 10
51 0 30  900 20 1 0 10
51 . 28  784  . 0 1 10
51 . 41 1681  . 1 0 10
51 0 34 1156 23 1 0 10
51 . 34 1156  . 1 0 10
51 . 28  784  . 1 0 10
51 . 25  625  . 1 0 10
51 . 28  784  . 0 0 10
51 0 31  961 25 1 0 10
51 1 35 1225 29 1 0 10
51 0 37 1369 26 1 0 10
51 . 30  900  . 0 1 10
51 0 27  729 24 1 0 10
51 . 26  676  . 1 0 10
51 . 26  676  . 1 0 10
51 1 22  484 21 1 0 10
51 0 33 1089 33 1 0 10
51 1 31  961 22 1 0 10
51 1 28  784 23 0 1 10
51 . 20  400  . 0 0 10
51 . 26  676  . 1 0 10
51 . 35 1225  . 1 0 10
51 1 32 1024 21 1 0 10
51 . 27  729  . 1 0 10
51 0 24  576 21 1 0 10
51 . 31  961  . 1 0 10
51 0 33 1089 31 1 0 10
51 1 27  729 22 1 0 10
51 1 26  676 23 1 0 10
51 0 39 1521 30 1 0 10
51 1 36 1296 27 0 0 10
51 1 26  676 20 0 0 10
51 . 37 1369  . 1 0 10
51 0 28  784 19 1 0 10
51 0 31  961 25 1 0 10
51 . 26  676  . 0 1 10
51 0 29  841 28 0 1 10
51 . 35 1225  . 0 1 10
51 1 27  729 21 1 0 10
51 0 33 1089 23 0 1 10
51 0 32 1024 22 0 1 10
51 1 32 1024 31 1 0 10
51 1 28  784 18 1 0 10
51 1 30  900 23 0 0 10
51 1 34 1156 22 1 0 10
51 1 20  400 19 1 0 10
51 0 29  841 18 1 0 10
51 . 33 1089  . 0 1 10
51 . 32 1024  . 1 0 10
51 . 36 1296  . 0 1 10
end
label values country country_lbl
label def country_lbl 51 "Armenia", modify
label values mom_age age_lbl
label def age_lbl 20 "20", modify
label def age_lbl 21 "21", modify
label def age_lbl 22 "22", modify
label def age_lbl 24 "24", modify
label def age_lbl 25 "25", modify
label def age_lbl 26 "26", modify
label def age_lbl 27 "27", modify
label def age_lbl 28 "28", modify
label def age_lbl 29 "29", modify
label def age_lbl 30 "30", modify
label def age_lbl 31 "31", modify
label def age_lbl 32 "32", modify
label def age_lbl 33 "33", modify
label def age_lbl 34 "34", modify
label def age_lbl 35 "35", modify
label def age_lbl 36 "36", modify
label def age_lbl 37 "37", modify
label def age_lbl 39 "39", modify
label def age_lbl 41 "41", modify
label def age_lbl 46 "46", modify

Thank you !

[Andrew Musau]

I only do this for 2 coefficients (highlighted), so generalize this to the others.

* Example generated by -dataex-. For more info, type help dataex
clear
input int country byte(FirstDaughter mom_age) int mom_age_sq byte(mom_age_first_birth mom_secondary_edu mom_university_edu) double mom_perwt
51 0 28  784 22 1 0 10
51 0 33 1089 22 0 1 10
51 1 31  961 24 1 0 10
51 0 31  961 21 1 0 10
51 0 31  961 29 0 1 10
51 0 36 1296 24 1 0 10
51 0 31  961 21 1 0 10
51 . 26  676  . 1 0 10
51 0 35 1225 28 0 1 10
51 1 32 1024 32 1 0 10
51 . 34 1156  . 0 1 10
51 . 29  841  . 1 0 10
51 0 36 1296 25 0 0 10
51 1 32 1024 21 0 1 10
51 . 25  625  . 1 0 10
51 0 24  576 22 0 1 10
51 1 30  900 24 0 1 10
51 1 24  576 21 1 0 10
51 0 22  484 21 0 1 10
51 1 32 1024 27 1 0 10
51 0 24  576 21 1 0 10
51 1 26  676 19 1 0 10
51 . 26  676  . 1 0 10
51 1 26  676 25 1 0 10
51 . 25  625  . 0 0 10
51 . 35 1225  . 1 0 10
51 1 25  625 18 1 0 10
51 . 25  625  . 1 0 10
51 0 27  729 19 1 0 10
51 . 25  625  . 1 0 10
51 1 33 1089 23 0 1 10
51 . 24  576  . 0 1 10
51 1 25  625 18 1 0 10
51 0 22  484 22 1 0 10
51 . 21  441  . 0 1 10
51 1 28  784 23 0 0 10
51 . 34 1156  . 0 1 10
51 . 32 1024  . 0 0 10
51 0 27  729 27 0 1 10
51 . 30  900  . 1 0 10
51 . 33 1089  . 0 1 10
51 0 46 2116 39 0 1 10
51 . 32 1024  . 1 0 10
51 . 25  625  . 0 1 10
51 1 32 1024 22 0 1 10
51 1 20  400 20 1 0 10
51 . 37 1369  . 1 0 10
51 . 33 1089  . 0 1 10
51 1 27  729 23 1 0 10
51 . 27  729  . 1 0 10
51 0 30  900 20 1 0 10
51 . 28  784  . 0 1 10
51 . 41 1681  . 1 0 10
51 0 34 1156 23 1 0 10
51 . 34 1156  . 1 0 10
51 . 28  784  . 1 0 10
51 . 25  625  . 1 0 10
51 . 28  784  . 0 0 10
51 0 31  961 25 1 0 10
51 1 35 1225 29 1 0 10
51 0 37 1369 26 1 0 10
51 . 30  900  . 0 1 10
51 0 27  729 24 1 0 10
51 . 26  676  . 1 0 10
51 . 26  676  . 1 0 10
51 1 22  484 21 1 0 10
51 0 33 1089 33 1 0 10
51 1 31  961 22 1 0 10
51 1 28  784 23 0 1 10
51 . 20  400  . 0 0 10
51 . 26  676  . 1 0 10
51 . 35 1225  . 1 0 10
51 1 32 1024 21 1 0 10
51 . 27  729  . 1 0 10
51 0 24  576 21 1 0 10
51 . 31  961  . 1 0 10
51 0 33 1089 31 1 0 10
51 1 27  729 22 1 0 10
51 1 26  676 23 1 0 10
51 0 39 1521 30 1 0 10
51 1 36 1296 27 0 0 10
51 1 26  676 20 0 0 10
51 . 37 1369  . 1 0 10
51 0 28  784 19 1 0 10
51 0 31  961 25 1 0 10
51 . 26  676  . 0 1 10
51 0 29  841 28 0 1 10
51 . 35 1225  . 0 1 10
51 1 27  729 21 1 0 10
51 0 33 1089 23 0 1 10
51 0 32 1024 22 0 1 10
51 1 32 1024 31 1 0 10
51 1 28  784 18 1 0 10
51 1 30  900 23 0 0 10
51 1 34 1156 22 1 0 10
51 1 20  400 19 1 0 10
51 0 29  841 18 1 0 10
51 . 33 1089  . 0 1 10
51 . 32 1024  . 1 0 10
51 . 36 1296  . 0 1 10
end

label values country country_lbl
label def country_lbl 51 "Armenia", modify
label values mom_age age_lbl
label def age_lbl 20 "20", modify
label def age_lbl 21 "21", modify
label def age_lbl 22 "22", modify
label def age_lbl 24 "24", modify
label def age_lbl 25 "25", modify
label def age_lbl 26 "26", modify
label def age_lbl 27 "27", modify
label def age_lbl 28 "28", modify
label def age_lbl 29 "29", modify
label def age_lbl 30 "30", modify
label def age_lbl 31 "31", modify
label def age_lbl 32 "32", modify
label def age_lbl 33 "33", modify
label def age_lbl 34 "34", modify
label def age_lbl 35 "35", modify
label def age_lbl 36 "36", modify
label def age_lbl 37 "37", modify
label def age_lbl 39 "39", modify
label def age_lbl 41 "41", modify
label def age_lbl 46 "46", modify

local socio_demographic_char1 c.mom_age c.mom_age_sq c.mom_age_first_birth i.mom_secondary_edu i.mom_university_edu

levelsof country, local(countries)
frame create results
frame results {
set obs `=wordcount("`countries'")'
gen label=""
gen age=""
gen age_sq=""
gen age_first_birth=""
gen secondary_educated=""
gen university_educated=""
}
local i 0
foreach country of local countries{
local ++i
regress FirstDaughter `socio_demographic_char1' [pw = mom_perwt] if country == `country', robust
frame results: replace label="`:lab (country) `country''" in `i'
frame results: replace age= cond(`=r(table)["pvalue", "mom_age"]'<0.01, "`=_b[mom_age]'***", cond(`=r(table)["pvalue", "mom_age"]'<0.05, "`=_b[mom_age]'**",cond(`=r(table)["pvalue", "mom_age"]'<0.1, "`=_b[mom_age]'*", "`=_b[mom_age]'")))  in `i'
frame results: replace age_sq= "`=_b[mom_age_sq]'" in `i'
frame results: replace age_first_birth= "`=_b[mom_age_first_birth]'" in `i'
frame results: replace secondary_educated= cond(`=r(table)["pvalue", "1.mom_secondary_edu"]'<0.01, "`=_b[1.mom_secondary_edu]'***", cond(`=r(table)["pvalue", "1.mom_secondary_edu"]'<0.05, "`=_b[1.mom_secondary_edu]'**",cond(`=r(table)["pvalue", "1.mom_secondary_edu"]'<0.1, "`=_b[1.mom_secondary_edu]'*", "`=_b[1.mom_secondary_edu]'")))  in `i'
frame results: replace university_educated= "`=_b[1.mom_university_edu]'" in `i'
}
frame change results
list

Res.:

Linear regression                               Number of obs     =         60
                                                F(5, 54)          =       4.12
                                                Prob > F          =     0.0031
                                                R-squared         =     0.1117
                                                Root MSE          =     .49675

--------------------------------------------------------------------------------------
                     |               Robust
       FirstDaughter | Coefficient  std. err.      t    P>|t|     [95% conf. interval]
---------------------+----------------------------------------------------------------
             mom_age |   .0193096   .0801381     0.24   0.811    -.1413577    .1799768
          mom_age_sq |  -.0006708   .0012765    -0.53   0.601      -.00323    .0018883
 mom_age_first_birth |  -.0032793   .0220244    -0.15   0.882    -.0474356    .0408769
 1.mom_secondary_edu |  -.3225292   .1814746    -1.78   0.081    -.6863639    .0413055
1.mom_university_edu |  -.4565794   .2151214    -2.12   0.038    -.8878718    -.025287
               _cons |   .9393261   1.421904     0.66   0.512     -1.91142    3.790072
--------------------------------------------------------------------------------------

. 
. frame change results

. 
. list

     +------------------------------------------------------------------------------------------------------------------+
     |   label                 age               age_sq      age_first_birth    secondary_educated   university_educa~d |
     |------------------------------------------------------------------------------------------------------------------|
  1. | Armenia   .0193095559575387   -.0006708320328955   -.0032793318333202   -.3225292231931355*   -.4565794290746258 |
     +------------------------------------------------------------------------------------------------------------------+

[Veronika Valcikova]

Thank you ! It works perfectly.

[Andrew Musau]

If you want to limit the number of decimal places displayed in the Excel document, e.g., to 3 below (display format highlighted in blue), you can set up the conditions as:

frame results: replace age= cond(`=r(table)["pvalue", "mom_age"]'<0.01, string(`=_b[mom_age]', "%4.3f")+"***", cond(`=r(table)["pvalue", "mom_age"]'<0.05,  string(`=_b[mom_age]', "%4.3f")+"**",cond(`=r(table)["pvalue", "mom_age"]'<0.1,  string(`=_b[mom_age]', "%4.3f")+"*",  string(`=_b[mom_age]', "%4.3f"))))  in `i'

frame results: replace age_sq=cond(`=r(table)["pvalue", "mom_age_sq"]'<0.01, string(`=_b[mom_age_sq]', "%4.3f")+"***", cond(`=r(table)["pvalue", "mom_age_sq"]'<0.05,  string(`=_b[mom_age_sq]', "%4.3f")+"**",cond(`=r(table)["pvalue", "mom_age_sq"]'<0.1,  string(`=_b[mom_age_sq]', "%4.3f")+"*",  string(`=_b[mom_age_sq]', "%4.3f"))))  in `i'

[Veronika Valcikova]

Dear Andrew, you are a mind reader! I was just trying to figure it out ... thank you so much .