Excluding group each time using loop in regression (+ output table adjustment)

[Anne-Claire Jo]

Hello Statausers,

I am trying to run (4 different types of - which you will see in my code below, written column (1), (2), etc.) regressions by excluding one country each time, for example, if there are USA UK GER FRA SPN, then I would like to have 5 regressions: one with all countries (USA UK GER FRA SPN); one with 4 of them (USA UK GER FRA); one with 3 of them (USA UK GER) etc. (and order doesn't matter).
I tried to do this by using loop but I see that my command doesn't exclude one by one as I intend to do..
And since the output will be super long, I would like to export the output regression in excel file with differentiation of country included in the model.
In addition, I also tried putting information on number of countries, number of sectors, used in the regressions, and number of country-sector in the exported output table, but only thing that I get is like below:

Fixed effects	i.cty#i.year i.cty#i.ind_a38	i.cty#i.year i.cty#i.ind_a38	i.cty#i.year i.cty#i.ind_a38	i.cty#i.year i.cty#i.ind_a38
h	Countries	Countries	Countries	Countries
6	Industries	Industries	Industries	Industries

where it should be like:

Fixed effects	i.cty#i.year i.cty#i.ind_a38	i.cty#i.year i.cty#i.ind_a38	i.cty#i.year i.cty#i.ind_a38	i.cty#i.year i.cty#i.ind_a38
Countries	list...	list	list	list
Industries	list	list	list	list

(list is where there should be country list or industry list used in the regression).

So, in sum, I would like to run regressions by excluding each country using loop; generate output table in excel file with different sheets; and put information as aforementioned.

The sample of the data is:

* Example generated by -dataex-. For more info, type help dataex
clear
input str3 country long cty int year byte(ind_a38 ind_a7 p_LogLP_VA) float(NJCR_1L NJCR_5L JCR_1L JCR_5L) double(LogL_av F1LogLP_VA_av) float weightvar
"TTT" 1 2000 10 3 2   -.10872902   -.3300066   .0809676  .1689697  1.840584397315979    .20446069538593292  .9630851
"TTT" 1 2000 10 3 1   -.25427234   -.8719715  .07382297 .13439047  1.461699366569519     .4158351719379425 .32102835
"TTT" 1 2000 10 3 3   -.06215283   -.3341145  .07957804  .1766271 2.1998374462127686    .17646874487400055  .6420567
"TTT" 1 2000 10 3 4   -.02620456  -.13305664  .07291858  .2144397 2.7542288303375244     .1420302540063858  .9630851
"TTT" 1 2000 10 3 5  -.011278235  -.53640324  .14847809 .18713714 3.1639435291290283    .08432700484991074 .32102835
"TTT" 1 2001 10 3 4  -.015539913   -.6024358  .13987286 .17630003 2.7520031929016113    -.0672094076871872 1.0510232
"TTT" 1 2001 10 3 5   .011977467 -.026743645  .10674387 .27145684 3.2838191986083984    -.1325097531080246  .3503411
"TTT" 1 2001 10 3 3    .03426892   -.2124657  .13642852  .1974412 2.1604278087615967   -.05476070195436478  .7006822
"TTT" 1 2001 10 3 2   -.08127491   -.3791687   .1101099 .13387752  1.881443738937378    -.0119343101978302 1.0510232
"TTT" 1 2001 10 3 1    -.4895266   -.9400033   .0785263 .09401135 1.5503216981887817    .18138520419597626  .3503411
"TTT" 1 2002 10 3 2   -.09032566    -.347756  .08411396 .14347175  1.915210247039795    .09014417976140976  1.025753
"TTT" 1 2002 10 3 1   -.19866233  -1.0509706  .11087405 .08516397  1.721211552619934    .20287148654460907  .3419177
"TTT" 1 2002 10 3 4  -.025287125  -.12230378  .04705971 .13302709 2.7997517585754395   .013578196056187153  1.025753
"TTT" 1 2002 10 3 5   -.05578554   -.3327879   .0834043 .11818015  3.137329578399658  -.038818009197711945  .3419177
"TTT" 1 2002 10 3 3   -.13531668   -.4864819  .06040643 .12510763 2.1886637210845947   .036729197949171066  .6838354
"TTT" 1 2003 10 3 4   -.23370764  -1.3446285 .030522786 .11777425 2.8017382621765137   -.07256508618593216  .9955797
"TTT" 1 2003 10 3 5    .16956623  -.08387914  .29200843 .39410675    3.2444908618927   -.18206992745399475  .3318599
"TTT" 1 2003 10 3 2    -.1315875   -.3668005   .0607442  .1417943 1.9475698471069336    .02600083127617836  .9955797
"TTT" 1 2003 10 3 1   -.26410466   -.4935505  .09796075 .15587753  1.726263403892517    .20946943759918213  .3318599
"TTT" 1 2003 10 3 3   -.06307048   -.1535872  .06537203  .1873949 2.2256076335906982   -.06714294850826263  .6637198
"TTT" 1 2004 10 3 5  -.068912715   -.3096411  .03852142 .14537948 3.2913637161254883   -.16507939994335175  .3214091
"TTT" 1 2004 10 3 2   -.05832967  -.14464293  .07579416 .15488026 1.9153269529342651 -.0027679228223860264  .9642272
"TTT" 1 2004 10 3 1            .           .          .         .                 .d    .18176698684692383  .3214091
"TTT" 1 2004 10 3 4  -.018388119  -.12270933  .05734575  .1837198 2.7984840869903564   -.08617919683456421  .9642272
"TTT" 1 2004 10 3 3  -.023526326  -.11197796  .05208328  .1231692  2.218564748764038   -.07116112112998962  .6428182
"TTT" 1 2005 10 3 1    -.1714545   -.5451496  .13117863 .20480147 1.5987634658813477    .18330104649066925  .3064613
"TTT" 1 2005 10 3 2  -.072437875  -.28999743  .08265004  .1957637 1.9225152730941772   .018706103786826134  .9193839
"TTT" 1 2005 10 3 4   -.02372933  -.16745813  .04764793 .12542543 2.8386151790618896   -.04821430519223213  .9193839
"TTT" 1 2005 10 3 5    .11709362    .1085546  .19226943 .28665808 3.2440032958984375   -.06850412487983704  .3064613
"TTT" 1 2005 10 3 3   -.10032904 -.003059964  .06235394 .15860726 2.2190847396850586  -.010342784225940704  .6129226
"TTT" 1 2006 10 3 4   -.09156723   -.4017831  .03094269 .09619105  2.860882043838501    .03132317587733269  .9045334
"TTT" 1 2006 10 3 5   -.05305692  -.12261589  .05060381  .2402903 3.0970280170440674  .0015575005672872067  .3015111
"TTT" 1 2006 10 3 3   -.08509123   -.3900774  .05506426 .18100406  2.229736566543579    .05566878989338875  .6030223
"TTT" 1 2006 10 3 1   -.15088387   -.5603878  .10095442    .21351 1.5661205053329468     .2084263414144516  .3015111
"TTT" 1 2006 10 3 2   -.08502053   -.4193031  .07959734 .13711129 1.9529547691345215    .09287622570991516  .9045334
"TTT" 1 2007 10 3 3   -.04808515   -.1297728  .05889824 .16097847 2.2365360260009766 -.0005355222383514047 .57723033
"TTT" 1 2007 10 3 5   -.01837397   -.2999383  .08636057   .183963  3.114090919494629   -.06761135905981064 .28861517
"TTT" 1 2007 10 3 4  -.018079145  -.02317263  .04303115 .13374186 2.8220808506011963  -.012523408979177475  .8658454
"TTT" 1 2007 10 3 2   -.08621883   -.3203627  .06987467 .14051035   1.98495352268219     .0688481479883194  .8658454
"TTT" 1 2007 10 3 1  -.034659393   -.4199567  .12120045  .1856747 1.5354024171829224    .23488043248653412 .28861517
"TTT" 1 2008 10 3 4  -.011995178  -.02510436  .05317536 .14279662 2.8634791374206543  -.028601009398698807  .8666521
"TTT" 1 2008 10 3 5   -.02256112 -.014921203  .05823082 .13390869 3.1483500003814697    -.0729430690407753 .28888404
"TTT" 1 2008 10 3 3  -.024728253  .036810108  .07242021  .1911903 2.2594432830810547  -.010509273037314415  .5777681
"TTT" 1 2008 10 3 2   -.05801844  -.17101985   .0644569  .1473267 1.9425759315490723    .06355470418930054  .8666521
"TTT" 1 2008 10 3 1    -.3306016    -1.39607 .064487204 .13253626  1.645038366317749    .24917109310626984 .28888404
"TTT" 1 2009 10 3 1   -.13104354   -.5134211  .07170298 .17344013  1.654848575592041      .203806534409523 .29459268
"TTT" 1 2009 10 3 3   .006211587  .005232121  .07995578  .1917343 2.2316367626190186   .024106912314891815 .58918536
"TTT" 1 2009 10 3 4   -.02978392  -.20066234  .05448442 .15180594  2.839977979660034  -.032453134655952454   .883778
"TTT" 1 2009 10 3 2   -.03841976  -.16122237 .070403084 .14132398  1.993923306465149     .0600602813065052   .883778
"TTT" 1 2009 10 3 5    .02447836   -.1118901  .06927878 .18524835 3.2027390003204346    -.0660695880651474 .29459268
"TTT" 1 2010 10 3 1   -.10286322   -.4651616  .11637723 .25014573 1.6262915134429932    .17507266998291016  .2932262
"TTT" 1 2010 10 3 4  -.006985628  -.11003485   .0446233 .22056776 2.8652076721191406   -.04635244980454445  .8796787
"TTT" 1 2010 10 3 2   -.05264198  -.11820843  .08799701  .2049648  1.943572759628296   .042402077466249466  .8796787
"TTT" 1 2010 10 3 5   .032369103  -.04611845  .07164272  .1621521  3.276958465576172    -.1221148669719696  .2932262
"TTT" 1 2010 10 3 3  -.007009926  -.02733204  .07176834  .1815952  2.239445686340332  -.020887069404125214  .5864524
"TTT" 1 2011 10 3 3   -.02261563  -.05403101  .06426923 .18174005  2.325199604034424   -.01404623594135046  .5764433
"TTT" 1 2011 10 3 4   .007371416   .23090065  .04145033 .29193836  2.771138906478882   -.05015496164560318   .864665
"TTT" 1 2011 10 3 2   -.05077169 -.026978467  .07474808 .23172092  1.932976484298706   .045708779245615005   .864665
"TTT" 1 2011 10 3 5   .016944235   -.1619442  .05682085  .1650002 3.2220661640167236   -.09345229715108871 .28822166
"TTT" 1 2011 10 3 1   -.11590383  -.20306905  .08484055 .21718296 1.6343930959701538     .2660410702228546 .28822166
"TTT" 1 2012 10 3 1    .00500576   -.1652223  .19851384  .3116431 1.5023117065429688    .19716870784759521 .28889653
"TTT" 1 2012 10 3 4     .0141976   .18419383 .065747865 .28600565  2.806609630584717   -.01995943859219551  .8666896
"TTT" 1 2012 10 3 2    -.0056441 -.071258694  .10400848 .25851518  1.985576868057251    .06225164979696274  .8666896
"TTT" 1 2012 10 3 3   -.03849619   -.1043887  .06285096 .16296484 2.3009135723114014  -.007140811532735825 .57779306
"TTT" 1 2012 10 3 5  -.011723422    -.075701  .06713542 .16508146 3.1773600578308105   -.05639851093292236 .28889653
"TTT" 1 2013 10 3 3  -.029609585 -.000833665 .073389694  .2158674 2.2662768363952637    .04141983389854431   .573291
"TTT" 1 2013 10 3 4 -.0004684053    .2346535  .05897416  .3484866  2.822906970977783   .009968907572329044  .8599365
"TTT" 1 2013 10 3 5   -.08177508  -.01864995   .0456963 .18758164  3.228485584259033   -.04382513090968132  .2866455
"TTT" 1 2013 10 3 2   -.03379227 -.032588106  .07354241 .21176194  1.955122947692871    .09294859319925308  .8599365
"TTT" 1 2013 10 3 1   -.11945468   -.4913515  .10927408 .23176213  1.613525152206421    .27786239981651306  .2866455
"TTT" 1 2014 10 3 4    .03738898   .19386198  .10627817  .3469386  2.773324489593506  -.004754411056637764  .8360332
"TTT" 1 2014 10 3 2   -.02289908  -.03950724   .0897713  .2313825 1.9541062116622925    .06177779287099838  .8360332
"TTT" 1 2014 10 3 3    .01878201   .05593342  .08733736 .26003924 2.2951557636260986    .03961857035756111  .5573554
"TTT" 1 2014 10 3 5   .005674758   .20306784 .068679385   .298298  3.405034303665161   -.04132387042045593  .2786777
"TTT" 1 2014 10 3 1    -.1299608   -.7979255  .11566855 .26062182 1.5550100803375244    .20990557968616486  .2786777
"TTT" 1 2015 10 3 3   -.01986189  -.23253934  .09018949  .1742279  2.369835615158081  -.021601341664791107  .5593823
"TTT" 1 2015 10 3 5     .0450442   .15573537  .09933616  .2807689  3.416618824005127   -.10463157296180725 .27969116
"TTT" 1 2015 10 3 2    .03646522   -.1689595  .12527935  .1719333 1.9589452743530273 -.0004747857165057212  .8390735
"TTT" 1 2015 10 3 4   .005012159   .03688784  .05564152  .2156424 2.7491583824157715   -.06275223940610886  .8390735
"TTT" 1 2015 10 3 1   -.08311782  -.50406194  .11935304  .2396623  1.591057300567627    .23239535093307495 .27969116
"TTT" 1 2016 10 3 4 -.0003281478  .016795985  .06015761  .1611231    2.8155517578125  -.041120611131191254  .8567294
"TTT" 1 2016 10 3 5    .06093359   .14443764  .08757858 .26031768  3.373270034790039   -.10383661091327667 .28557646
"TTT" 1 2016 10 3 3    .04558348   .05452177   .1030388 .24363166 2.2743964195251465   .015050857327878475  .5711529
"TTT" 1 2016 10 3 2   -.04457128  -.16379984  .08306017  .1540827 2.0011308193206787    .05475447699427605  .8567294
"TTT" 1 2016 10 3 1   -.12908328   -.4010049  .10590804 .15671264 1.6674010753631592     .1614076793193817 .28557646
"TTT" 1 2017 10 3 4    .04326027  -.00534049  .08506771  .1772065   2.77266263961792  -.011408915743231773  .8681946
"TTT" 1 2017 10 3 2   -.02767447  -.07452167   .0873228  .1960037  1.983992338180542    .08571377396583557  .8681946
"TTT" 1 2017 10 3 1   -.09507153   -.4512224  .10802395  .2189022 1.5812430381774902    .28419533371925354  .2893982
"TTT" 1 2017 10 3 3   .011042545 -.009180754  .09135258 .22349638  2.351762294769287    .03298109024763107  .5787964
"TTT" 1 2017 10 3 5   .036693707  .064886026  .06551674  .2069173 3.3141860961914063   -.07786355912685394  .2893982
"TTT" 1 2018 10 3 5    .04291535           .  .06113614         . 3.2301387786865234  -.024815494194626808 .29309466
"TTT" 1 2018 10 3 2  -.014783784   -5.182343  .09750334         0  2.021411895751953    .05022163689136505   .879284
"TTT" 1 2018 10 3 3    .02554265           .  .07854712         .  2.373128890991211 -.0018802389968186617  .5861893
"TTT" 1 2018 10 3 1    -.0537047  -4.6556005  .14087678         0 1.6035999059677124      .249224454164505 .29309466
"TTT" 1 2018 10 3 4   .014315114  -1.9244214 .067890264         0 2.7654192447662354   -.03557773679494858   .879284
"TTT" 1 2019 10 3 2    -.1827486   -5.341022   .0459206         0  2.048081398010254     .1738835573196411  .8921548
"TTT" 1 2019 10 3 5   .032495867  -.24507673  .06818398         0 3.1837241649627686   -.07534141838550568 .29738492
"TTT" 1 2019 10 3 3    -.1356803   -1.771528  .04311971         0 2.3456099033355713    .09867457300424576 .59476984
"TTT" 1 2019 10 3 4  -.020898974    -1.11198  .04577966         0 2.7039361000061035     .0407809354364872  .8921548
"TTT" 1 2019 10 3 1   -.15098006   -3.424912  .10082114         0  1.707952857017517     .3669770359992981 .29738492
end
label values cty cty
label def cty 1 "TTT", modify
label values ind_a38 ind_a38_labels
label def ind_a38_labels 10 "Food products, beverages and tobacco", modify
label values ind_a7 ind_a7_labels
label def ind_a7_labels 3 "Manufacturing", modify
label values p_LogLP_VA quantilelabels
label def quantilelabels 1 "0-10", modify
label def quantilelabels 2 "10-40", modify
label def quantilelabels 3 "40-60", modify
label def quantilelabels 4 "60-90", modify
label def quantilelabels 5 "90-100", modify

And my code is:

* 5 year change (as in the first paper (model))

loc job JCR NJCR JDR

foreach var of local job {
        
    levelsof country, local(countries)
    foreach c of local countries{

    local cty_exclude "`c'"
    
loc model_name "5-year `var'"
loc FElbl13     i.cty#i.year i.cty#i.ind_a38
loc FElbl24        i.cty#i.ind_a38#i.year


* Column (1)
reghdfe `var'_5L ib3.p_LogLP_VA LogL_av if country != "`cty_exclude'" [aw=weightvar], a(`FElbl13') vce(cluster i.cty#i.ind_a38)
    levelsof country if e(sample)
    local n_countries = `r(r)'
    levelsof ind_a38 if e(sample)
    local n_inds = `r(r)'

    outreg2 using "5y_`var'_reg.xls", replace dta ctitle(`model_name') label ///    
            addtext(Fixed effects, `FElbl13', h, Countries, `n_countries', Industries, `n_inds') 

* Column (2)
reghdfe `var'_5L ib3.p_LogLP_VA LogL_av if country != "`cty_exclude'" [aw=weightvar], a(`FElbl24') vce(cluster i.cty#i.ind_a38)
    levelsof country if e(sample)
    local n_countries = `r(r)'
    levelsof ind_a38 if e(sample)
    local n_inds = `r(r)'

    outreg2 using "5y_`var'_reg.xls", append dta ctitle(`model_name') label ///    
            addtext(Fixed effects, `FElbl13', h, Countries, `n_countries', Industries, `n_inds') 

* Column (3)
reghdfe `var'_5L F1LogLP_VA_av ib3.p_LogLP_VA `var'_1L LogL_av if country != "`cty_exclude'" [aw=`weight'], a(`FElbl13') vce(cluster i.cty#i.ind_a38)
    levelsof country if e(sample)
    local n_countries = `r(r)'
    levelsof ind_a38 if e(sample)
    local n_inds = `r(r)'

    outreg2 using "5y_`var'_reg.xls", append dta ctitle(`model_name') label ///    
            addtext(Fixed effects, `FElbl13', h, Countries, `n_countries', Industries, `n_inds') 

* Column (4)
reghdfe `var'_5L F1LogLP_VA_av ib3.p_LogLP_VA `var'_1L LogL_av if country != "`cty_exclude'" [aw=weightvar], a(`FElbl24') vce(cluster i.cty#i.ind_a38)
    levelsof country if e(sample)
    local n_countries = `r(r)'
    levelsof ind_a38 if e(sample)
    local n_inds = `r(r)'

    outreg2 using "5y_`var'_reg.xls", append dta ctitle(`model_name') label ///    
            addtext(Fixed effects, `FElbl13', h, Countries, `n_countries', Industries, `n_inds') 

            
        }    // end of countries loop    
    } // end of job loop




* 1 year change
foreach c in cty {

di _n "Omitting `c'"

loc job JCR NJCR JDR

foreach var of local job {

loc model_name "1-year `var'"

* Column (1)
reghdfe `var'_1L ib3.p_LogLP_VA LogL_av if country != "`cty_exclude'" [aw=weightvar], a(`FElbl13') vce(cluster i.cty#i.ind_a38)
    levelsof country if e(sample)
    local n_countries = `r(r)'
    levelsof ind_a38 if e(sample)
    local n_inds = `r(r)'

    outreg2 using "1y_`var'_reg.xls", replace dta ctitle(`model_name') label ///    
            addtext(Fixed effects, `FElbl13', h, Countries, `n_countries', Industries, `n_inds') 


* Column (2)
reghdfe `var'_1L ib3.p_LogLP_VA LogL_av if country != "`cty_exclude'" [aw=weightvar], a(`FElbl24') vce(cluster i.cty#i.ind_a38)
    levelsof country if e(sample)
    local n_countries = `r(r)'
    levelsof ind_a38 if e(sample)
    local n_inds = `r(r)'

    outreg2 using "1y_`var'_reg.xls", append dta ctitle(`model_name') label ///    
            addtext(Fixed effects, ``FElbl24'', h, Countries, `n_countries', Industries, `n_inds') 

* Column (3)
reghdfe `var'_1L F1LogLP_VA_av ib3.p_LogLP_VA LogL_av if country != "`cty_exclude'" [aw=weightvar], a(`FElbl13') vce(cluster i.cty#i.ind_a38)
    levelsof country if e(sample)
    local n_countries = `r(r)'
    levelsof ind_a38 if e(sample)
    local n_inds = `r(r)'

    outreg2 using "1y_`var'_reg.xls", append dta ctitle(`model_name') label ///    
            addtext(Fixed effects, `FElbl13', h, Countries, `n_countries', Industries, `n_inds') 

* Column (4)
reghdfe `var'_1L F1LogLP_VA_av ib3.p_LogLP_VA LogL_av if country != "`cty_exclude'" [aw=weightvar], a(`FElbl24') vce(cluster i.cty#i.ind_a38)
    levelsof country if e(sample)
    local n_countries = `r(r)'
    levelsof ind_a38 if e(sample)
    local n_inds = `r(r)'

    outreg2 using "1y_`var'_reg.xls", append dta ctitle(`model_name') label ///    
            addtext(Fixed effects, ``FElbl24'', Countries, `n_countries', Industries, `n_inds') 

    } // end of job loop

}     // end of country loop

I hope my question was clear!
Actually I am a bit in urgent with this issue, and can someone help me with this issue please?
Thanks SO MUCH in advance

[Anne-Claire Jo]

sorry, im a bit in hurry with solving this issue.. could someone help me please?

[Anne-Claire Jo]

Or someone could help me with maybe just only the first two points?

So, in sum, I would like to run regressions by excluding each country using loop; generate output table in excel file with different sheets; and put information as aforementioned.

or if second one doesn't work, then i can just use append command in .xls file; but I really have no clues on the first issue

[Clyde Schechter]

I don't see why your code isn't cycling through the various countries to exclude them. Your setup for that looks correct to me.

I can't replicate what is happening with your example data as it only contains one country. Even when I arbitrarily change some of the values of country to create some variation in that, the code falters on things like one regression where you have [aw = `weight'], which is a syntax error because local macro weight has never been defined. (I think you meant [aw = weightvar] there.) It also falters on reference to variable JDR_5L, which does not exist in the example data.

But when I patch those things over, the output I get does show variation in which country is omitted, and it does show the numbers of countries and industries in the -addtext()- option. Now it may also be that your syntax for -outreg2- is wrong or just doesn't do what you think it does. I don't use -outreg2-, nor do I have it installed. So I'm just having Stata -display- the -outreg2- commands you will get from this code, and they look OK to me.

So I really don't know what to tell you.

Actually I am a bit in urgent with this issue, and can someone help me with this issue please?

Statalist is not a good venue for consulting on urgent problems. Everyone who posts here does so voluntarily, and picks and chooses what posts they will read and which they will respond to, if any. At certain times of day, or days of the week, the traffic may be very light. So if your need is urgent, this is not a good resource to rely on.

In the ordinary course of things, and all the more when you feel some urgency to your problem, it behooves you to post in a way that optimizes your chances of getting a timely and helpful response. A lengthy post with lots of complicated code to read through is not going to draw many interested readers. Moreover, when the code you post is applied to your example data and it does not reproduce the particular problem you are asking for help with, that is a huge turnoff to those who might want to help you, enough of a turnoff that many would just move on to another thread. So, not just as a courtesy and sign of respect to others who might help you, but in your own direct interest, you should always verify in your own setup that the code and example data you post actually do reproduce the error you are asking about, and do not throw other errors along the way. If they don't, then find another data example, or see if you can reproduce the same problem in one of StataCorp's online data sets instead.