Exporting several regressions with loop

[Anne-Claire Jo]

hello statausers,

I am encountering several issues with my code and I couldn't find any problem to fix the issue.
This problem is also one of the follow-up question here: https://www.statalist.org/forums/for...erent-versions, but it's on slightly different issue.
In particular, Im trying to export regression tables in excel file in which it contains: Columns (1) to (4) are so-called "Aggregated" version and columns from (5) until the end is either version 0 or 1.
Aggregated version is when every country is included in the regression, 0 is when one country is excluded from the dataset at the time (for e.g. if countrylist is USA, UK, FRA, CAN then first set is excluding only USA, second set is UK, and so on), and 1 is when we are regressing one-by-one country (for e.g. only USA, then UK, then FRA so on).
To implement this, i used the following command:

* Choose dataset to use
loc dataset 1        // 1 if MainStat; 0 if 10plus

if `dataset' {        // MainStat
    loc data_use Quant
    gl folder ${Mainfolder}
}
else {                // 10plus
    loc data_use 10plus
    gl folder ${tenplusfolder}
}

use "~directory/`data_use'.dta", clear


* Generating variables / Basic setup
for varlist F5*_av : cap replace X = 5*X

keep if inrange(p_LogLP_VA,1,5)
keep if inlist(ind_a7, 3, 6)
drop if inlist(ind_a38, 19, 68)

gen keepcountry = 0 
foreach country in $countryList {
    replace keepcountry=1 if country=="`country'"
}
keep if keepcountry 
drop keepcountry

loc indlvl ind_a38
foreach wvar in L VA GO {
    * weighting variable
    cap egen `wvar'_cy = total(`wvar'_av * `wvar'_sum_w) if `wvar'_av >= 0, by(country year)
    cap gen `wvar'_sh_`indlvl' = `wvar'_av * `wvar'_sum_w / `wvar'_cy if `wvar'_av >= 0
    cap drop `wvar'_cy
}
ren L_sh_ind_a38 _w

loc prodvar  LogLP_VA
cap drop cell_share 
gen     cell_share = 10        if inlist(p_`prodvar',1,5)
replace    cell_share = 30        if inlist(p_`prodvar',2,4)
replace    cell_share = 20        if p_`prodvar' == 3

gen weightvar = cell_share * _w

gen Covid = 1 if year >= 2015
    replace Covid = 0 if year < 2015

    

* Regression
cap encode country, gen(cty)

loc analysislevel 1        // 1 if one-by-one (country); 0 if excluding one country
if `analysislevel' {
    loc regif "=="
}
else {
    loc regif "!="
}

levelsof country, local(countries)
local iteration 1

foreach c of local countries {

    if `iteration' == 1 local replace_op replace 
    if `iteration' > 1 local replace_op append

    noi di _n "Country: `c'"
    
    if `analysislevel' {
        loc ex_lab 
    }
    else {
        loc ex_lab "Excluding, `c',"
    }
    
    loc LHS            F5LogW_av
    loc model_name "`var'"
    loc FElbl13     i.cty#i.year i.cty#i.ind_a38
    loc FElbl24        i.cty#i.ind_a38#i.year
    loc FE13        "C-I C-Y"
    loc FE24         "C-I-Y"
    loc cl_CI        i.cty#i.ind_a38

    
    * Regression (Columns 1 to 4)    
    forval col = 1/4 {
            
        * Setting RHS for columns 1&2 // 3&4
        if inlist(`col', 1, 2)    local xvars ib3.p_LogLP_VA LogW_av  //    Columns 1&2 
        else local xvars F1LogLP_VA_av ib3.p_LogLP_VA F1LogW_av LogW_av    /// Columns 3&4 
        
        * Setting FE/labels for columns 1&3 // 2&4
        if inlist(`col', 1, 3) {
            local fe         `FElbl13'   
            local fe_table  `FE13'
        }
        else {                    
        local fe         `FElbl124'  
        local fe_table  `FE24'
        }
             
        * REGRESSION
        capture reghdfe `LHS' `xvars' `regif' [aw=weightvar], a(`fe') vce(cluster `cl_CI')
            
        if c(rc) == 0 {
                
            levelsof country if e(sample), clean local(countrylist)
            local n_countries = `r(r)'
            levelsof ind_a38 if e(sample)
            local n_inds = `r(r)'
        
            * Export to excel file
            outreg2 using "${folder}/`analysislevel'_Baseline.xls", `replace_op' ctitle(`model_name') label ///    
                    addtext(Fixed effects, `fe_table', Country List, `countrylist', `ex_lab' Countries, `n_countries', Industries, `n_inds')
        }
        else if !inlist(c(rc), 2000, 2001) {
            display as error `"Unexpected regression error: var = `var', country = `c'"'
        }
    
    }    // end of forval col
    
    local ++iteration

}    // end of countries loop

But i have the following error message:

Country: CAN
Unexpected regression error: var = , country = CAN
Unexpected regression error: var = , country = CAN
Unexpected regression error: var = , country = CAN
Unexpected regression error: var = , country = CAN

Country: FIN
Unexpected regression error: var = , country = FIN
Unexpected regression error: var = , country = FIN
Unexpected regression error: var = , country = FIN
Unexpected regression error: var = , country = FIN

Country: FRA
Unexpected regression error: var = , country = FRA
Unexpected regression error: var = , country = FRA
Unexpected regression error: var = , country = FRA
Unexpected regression error: var = , country = FRA

Country: HRV
Unexpected regression error: var = , country = HRV
Unexpected regression error: var = , country = HRV
Unexpected regression error: var = , country = HRV
Unexpected regression error: var = , country = HRV

Country: ITA
Unexpected regression error: var = , country = ITA
Unexpected regression error: var = , country = ITA
Unexpected regression error: var = , country = ITA
Unexpected regression error: var = , country = ITA

Country: LTU
Unexpected regression error: var = , country = LTU
Unexpected regression error: var = , country = LTU
Unexpected regression error: var = , country = LTU
Unexpected regression error: var = , country = LTU

Country: PRT
Unexpected regression error: var = , country = PRT
Unexpected regression error: var = , country = PRT
Unexpected regression error: var = , country = PRT
Unexpected regression error: var = , country = PRT

Country: SVN
Unexpected regression error: var = , country = SVN
Unexpected regression error: var = , country = SVN
Unexpected regression error: var = , country = SVN
Unexpected regression error: var = , country = SVN

I assure that it's not the problem on data or regression, but it's really on the command itself.

In addition, it's also on the same issue from previous post above: not all the 4 regressions of aggregated version are exported, but only the column 4 is survived + rest of non-aggregated version.
If someone has any insights on this issue again, I would appreciate a lot for your help! (but the main issue here is on the command with error message)

Could someone have any idea/insights on this issue, please?

Thanks so much in advance!

[Clyde Schechter]

See https://www.statalist.org/forums/for...versions/page2 at #18, which crossed with this thread being started. #1 here is a continuation of that thread, but I believe #18 in the original thread may have solved it..

[Anne-Claire Jo]

sorry I thought it might be different issue with the previous post from https://www.statalist.org/forums/for...rent-versions; cause the regressions and variables are different (i.e. no nested loop).
In addition to the issue raised in the previous post, I have the issue that regressions are not running here..

[Clyde Schechter]

Sorry, I just glanced at the code and it looked very similar to that in the other thread. So I thought you were just looking for somebody not following the previous thread to perhaps notice it and join the conversation.

That said, looking a bit more closely at the code here, it does contain some of the same errors that the code in the other thread contains, and you definitely need to fix those, even if they are not the entire problem. As for the regressions not running, I suggest that for diagnostic purposes you take the -capture- off of the -reghdfe- command and also precede it with -set tracedepth 1- and -set trace on-, and follow it with -set trace off-. That way you will see the error message that may explain what is going wrong, and you will also be able to see the command with all of the macros fully expanded. My best guess is that there is either a reference to a variable that Stata doesn't find, or a syntax error. In either case, it probably results from a macro that is expanding to something you didn't intend it to. Your macros are a bit hard to work with because often one macro is defined in terms of another, and one has to chase back through a chain of definitions to figure out what the final result will be. (There's nothing wrong with that as a programming approach--it's a feature of Stata that one can do that, but it does make debugging harder.) Anyway, I suspect the problem is there, but I can't really say anything more than that.

[Hemanshu Kumar]

I suggest that for diagnostic purposes you take the -capture- off of the -reghdfe- command and also precede it with -set tracedepth 1- and -set trace on-, and follow it with -set trace off-. That way you will see the error message that may explain what is going wrong, and you will also be able to see the command with all of the macros fully expanded.

Anne-Claire Jo several of us have given you this suggestion across multiple threads. It is absolutely key to solving your issues.

[Anne-Claire Jo]

Clyde Schechter Hemanshu Kumar
the first issue on error message has been solved but not the second one (which is also related to the previous post) - for only the last column (4) is survived for aggregated version in the output table.
I put set traceon etc in the code as you may see below:

cap encode country, gen(cty)

loc analysislevel 0        // 1 if one-by-one (country); 0 if excluding one country
if `analysislevel' {
    loc regif "=="
}
else {
    loc regif "!="
}

levelsof country, local(countries)
local iteration 1

foreach c in "" `countries' {

    * Excel file replace/append option
    if `iteration' == 1 local replace_op replace
    if `iteration' > 1  local replace_op append

    * Regression versions: Aggregate + 0/1
    if `analysislevel' {     // 1; one-by-one
        loc regif if country =="`c'"
        loc ex_lab
        loc cn `c'
    }
    else {                    // 0; excluding
        loc regif if country != "`c'"
        loc ex_lab Excluding, "`c'",
        loc cn `c'
    }
        
    if missing("`c'")  {        // Aggregate
        loc regif
        loc ex_lab
        loc cn Aggregate
    }
    
    loc LHS            F5LogW_av
    loc model_name "`LHS'"
    loc FElbl13     i.cty#i.year i.cty#i.ind_a38
    loc FElbl24        i.cty#i.ind_a38#i.year
    loc FE13        "C-I C-Y"
    loc FE24         "C-I-Y"
    loc cl_CI        i.cty#i.ind_a38

    
    * Regression (Columns 1 to 4)    
    forval col = 1/4 {
            
        * Setting RHS for columns 1&2 // 3&4
        if inlist(`col', 1, 2)    local xvars ib3.p_LogLP_VA LogW_av      //    Columns 1&2
        else local xvars F1LogLP_VA_av ib3.p_LogLP_VA F1LogW_av LogW_av    // Columns 3&4
        
        * Setting FE/labels for columns 1&3 // 2&4
        if inlist(`col', 1, 3) {
            local fe         `FElbl13'  
            local fe_table  `FE13'
        }
        else {                    
        local fe         `FElbl24'  
        local fe_table  `FE24'
        }
            
        * REGRESSION
        set tracedepth 1
        set trace on
        
        reghdfe `LHS' `xvars' `regif' [aw=weightvar], a(`fe') vce(cluster `cl_CI')
        
        set trace off
        
        estimates store `var'_`cn'_`col'
        
            levelsof country if e(sample), clean local(countrylist)
            local n_countries = `r(r)'
            levelsof ind_a38 if e(sample)
            local n_inds = `r(r)'
        
        local estimates_list `estimates_list' `var'_`cn'_`col'
        
            * Export to excel file
            outreg2 using "${folder}/`analysislevel'_Baseline.xls", `replace_op' ctitle(`model_name') label ///    
                    addtext(Fixed effects, `fe_table', Country List, `countrylist', `ex_lab' Countries, `n_countries', Industries, `n_inds')
        
    
    }    // end of forval col
    
    local ++iteration

}    // end of countries loop

and in stata, it shows:

 ------------------------------------------------------------------------ begin reghdfe ---
  - cap syntax, store_alphas
  - if (!c(rc)) {
    Store_Alphas
    exit
    }
  - cap syntax, shrug
  - if (!c(rc)) {
    di as text _n `"    {browse "https://www.theawl.com/2014/05/the-life-and-times-of-%C2%AF
> _%E3%83%84_%C2%AF/":¯\_(ツ)_/¯}"'
    exit
    }
  - cap syntax, worker[*]
  - if (!c(rc)) {
    ParallelWorker, `options'
    exit
    }
  - cap syntax anything(everything) [fw aw pw/],[*] VERSION(integer) [noWARN]
  - if !c(rc) {
    _assert inlist(`version', 3, 5)
    if ("`warn'" != "nowarn") di as error "(running historical version of reghdfe: `version'
> )"
    if ("`weight'"!="") local weightexp [`weight'=`exp']
    if (`version' == 3) {
    reghdfe3 `anything' `weightexp', `options'
    }
    else {
    reghdfe5 `anything' `weightexp', `options'
    }
    exit
    }
  - if replay() {
    Replay `0'
    exit
    }
  - loc keep_mata 0
  - Cleanup 0 `keep_mata'
  = Cleanup 0 0
  - qui which ftools
  - ms_get_version ftools, min_version("2.49.1")
  - cap noi Estimate `0'
  = cap noi Estimate F5LogW_av F1LogLP_VA_av ib3.p_LogLP_VA F1LogW_av LogW_av if country !=
> "SVN" [aw=weightvar], a(i.cty#i.year i.cty#i.ind_a38) vce(cluster i.cty#i.ind_a38)
[...REGRESSION TABLE HERE BUT ERASED DUE TO CONFIDENTIALITY ISSUE...]
* = FE nested within cluster; treated as redundant for DoF computation
  - Cleanup `c(rc)' `keep_mata'
  = Cleanup 0 0
  -------------------------------------------------------------------------- end reghdfe ---
- set trace off

Actually, what i'm wondering is that even though i put set traceon, it is essentially saying where the issue comes from, if i'm not mistaken.
But here, the issue that I have been struggling is not related to any bug or error, it's rather on the exported output table (which has no error in Stata).
If i run the above code, then I get the following result:

	(1)	(2)	(3)	(4)	(5)	(6)	(7)	(8)	(9)	(10)	(11)	(12)	(13)	(14)	(15)	(16)	(17)
VARIABLES	F5LogW_av	F5LogW_av	F5LogW_av	F5LogW_av	F5LogW_av	F5LogW_av	F5LogW_av	F5LogW_av	F5LogW_av	F5LogW_av	F5LogW_av	F5LogW_av	F5LogW_av	F5LogW_av	F5LogW_av	F5LogW_av	F5LogW_av
Mean of F1LogLP_VA (unweighted)
p_LogLP_VA = 1, 0-10
	(0
p_LogLP_VA = 2, 10-40	0.
	(0.00
p_LogLP_VA = 4, 60-90	-
	(
p_LogLP_VA = 5, 90-100	-
	(
Mean of F1LogW (unweighted)
Mean of LogW (unweighted)	-0.
	(0.0
Constant																	0.
Observations	1
R-squared	0
Fixed effects	C-I-Y	C-I C-Y	C-I-Y	C-I C-Y	C-I-Y	C-I C-Y	C-I-Y	C-I C-Y	C-I-Y	C-I C-Y	C-I-Y	C-I C-Y	C-I-Y	C-I C-Y	C-I-Y	C-I C-Y	C-I-Y
Country List	CAN FIN FRA HRV ITA LTU PRT SVN	FIN FRA HRV ITA LTU PRT SVN	FIN FRA HRV ITA LTU PRT SVN	FIN FRA HRV ITA LTU PRT SVN	FIN FRA HRV ITA LTU PRT SVN	CAN FRA HRV ITA LTU PRT SVN	CAN FRA HRV ITA LTU PRT SVN	CAN FRA HRV ITA LTU PRT SVN	CAN FRA HRV ITA LTU PRT SVN	CAN FIN HRV ITA LTU PRT SVN	CAN FIN HRV ITA LTU PRT SVN	CAN FIN HRV ITA LTU PRT SVN	CAN FIN HRV ITA LTU PRT SVN	CAN FIN FRA ITA LTU PRT SVN	CAN FIN FRA ITA LTU PRT SVN	CAN FIN FRA ITA LTU PRT SVN	CAN FIN FRA ITA LTU PRT SVN
Countries	8	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7
Industries	22	22	22	22	22	22	22	22	22	22	22	22	22	22	22	22	22
Excluding		CAN	CAN	CAN	CAN	FIN	FIN	FIN	FIN	FRA	FRA	FRA	FRA	HRV	HRV	HRV	HRV

where you can see only the column (1) is survived - which correspond to column (4) of aggregated version. (i.e. initial columns (1)-(3) are gone).
Could you help me with this issue that i have been encountering?

[Hemanshu Kumar]

That problem is happening because you are setting the local macro replace_op to replace for the first set of models, which in this case happens to be the Aggregate set. So that first column keeps getting overwritten and only the last model in that set survives. When the outer loop moves on to individual countries, then the macro is set to append, and new columns get appended to the table, as you need.

To fix this, I would recommend the following changes:

• replace the line local iteration 1 with

  local first 1

  I am changing the name of the macro, because we don't care to count iterations. We just need to distinguish between the first and the rest. We will no longer be using the macro iteration.
• remove all other lines in your current code that have anything to do with the macro iteration
• just above the line where you run the reghdfe command, insert the following lines:

  * Excel file replace/append option
  	        if `first' {
  	                local replace_op replace
  	                local first 0
  	        }
  	        else local replace_op append

Notice that I am putting this chunk of code inside the inner forval loop. This means that replace_op will be set to replace only for the first column of the first set of models, rather than the entire first set of models, which was what was happening earlier. This should fix your issue.

[Anne-Claire Jo]

Hemanshu Kumar wow thanks so much, it solved perfectly the problem!