Storing coefficients and residual value as variables when running regression by industry group

[Taylor Foster]

Hello! I have found posts about bits and pieces of this issue, but my efforts to piece the solutions together have not been fruitful so I am hoping for some help. I am looking to store certain coefficients and the residuals of a set of regressions that I am running on a panel dataset by group. I would like to store them as new variables in the dataset. I most recently tried:

by industry: xtreg depvar indvar1 indvar2 indvar3 i.calendaryear, fe
gen coeff1 = _b[indvar1]

There are 48 industries, which are mutually exclusive so each firm-year gets included in exactly one regression. The problem is that the variable coeff1 is equal to the same value for all observations, so the values from the 48th regression are being stored for all observations no matter what regression they are included in.

Is the best solution for this a for loop? Something like:

forvalues i=1/48 {
quietly xtreg depvar indvar1 indvar2 indvar3 i.calendaryear if industry == 'i', fe
gen coeff'i' = _b[indvar1]
}

[Clyde Schechter]

Is the best solution for this a for loop? Something like:

forvalues i=1/48 {
quietly xtreg depvar indvar1 indvar2 indvar3 i.calendaryear if industry == 'i', fe
gen coeff'i' = _b[indvar1]
}

Well, this will work. But it is not the best way to do it because you will end up with your coefficients scattered across 48 different variables, which will make them very difficult to work with from that point forward. Better would be:

gen coefficient = .
gen residual = .
forvalues i=1/48 {
    quietly xtreg depvar indvar1 indvar2 indvar3 i.calendaryear if industry == 'i', fe
    replace coeff'i' = _b[indvar1] if industry == `i'
    predict resid, resid
    replace residual = resid if industry == `i'
    drop resid
}

This way you get one variable for all of the coefficients, with the value for the appropriate industry appearing in each observation, and one variable all of the residuals, again, the residual from the regression appropriate to the industry of the observation.

In the future, when asking for help with code, please use the -dataex- command and show example data. Although sometimes, as here, it is possible to give an answer that has a reasonable probability of being correct, this is usually not the case. Moreover, such answers are necessarily based on experience-based guesses or intuitions about the nature of your data. When those guesses are wrong, both you and the person trying to help you have wasted their time as you end up with useless code. To avoid this, a -dataex- based example provides all of the information needed to develop and test a solution.

If you are running version 17, 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

When asking for help with code, always show example data. When showing example data, always use -dataex-.

[Taylor Foster]

Thanks very much for your response Clyde! Here is the dataex:

* Example generated by -dataex-. For more info, type help dataex
clear
input long gvkey double(scaledcashtax neg0pti negptiinteraction pospti posptiinteraction scalednolbpd tripleinteraction) float calendaryear double ff48rec
 10390    .0041323535766350315 0                    0 1  .009181239069276622  .00002849059231652381 2.6157893928330076e-07 2015 1
 29962     .009700505487022342 0                    0 1   .03619601372410361  .00001993266354495769  7.214829632312283e-07 2015 1
 64275      .06793971747626522 0                    0 1    .2217635921260338                      0                      0 2015 1
140760     .048006194347657766 0                    0 1   .15297135114208285  2.116808610342517e-07  3.238110732332896e-08 2015 1
184259    .0016534782095193102 1   -.0315656863902996 0                    0  .00030963671644731723                      0 2015 1
  1722     .015137418592370942 0                    0 1   .05743166788201866 1.6589628655679586e-07  9.527700432390096e-09 2015 2
  2282     .013172591813327268 0                    0 1  .011290971470837385  5.944366382423226e-06  6.711767123614548e-08 2015 2
  2390     .001787883650033236 0                    0 1   .09303871455749879  .00004971766784364593   4.62566790696951e-06 2015 2
  2663     .042490749011101184 0                    0 1   .12632384841138192  9.479473491102324e-07 1.1974835723097232e-07 2015 2
  4579      .00232525996569022 0                    0 1   .00285643823291083  .00040852838180952997 1.1669360890299347e-06 2015 2
  4809     .051234892611634365 0                    0 1   .18752231791461407  4.474448071735684e-06  8.390588738004508e-07 2015 2
  5071     .025911580072125018 0                    0 1   .08503014420397655  2.219067931650163e-07 1.8868766622663333e-08 2015 2
  5210      .03875315922493682 0                    0 1   .06457455770850885                      0                      0 2015 2
  5597      .10700442373665946 0                    0 1    .2604676538185328   5.67557521255166e-08 1.4783037596839516e-08 2015 2
  5709      .03641480801720203 0                    0 1   .12983358985406074                      0                      0 2015 2
  6375     .022301634570842433 0                    0 1  .051154787902852224 3.7161288329225136e-07 1.9009778226782495e-08 2015 2
  6573      .06035598909785142 0                    0 1   .21679733253657313                      0                      0 2015 2
  6574     .006016573484719657 0                    0 1  .020761977592515118                      0                      0 2015 2
  7146      .04705435516542877 0                    0 1   .22489027683997298  3.944903124544295e-06  8.871703557856409e-07 2015 2
  8582     .006704812878423191 0                    0 1  .015770774253429192   7.78268849925063e-06  1.227390234064413e-07 2015 2
  9777     .012468640711707259 0                    0 1   .03272001551541845  4.455352919285416e-07  1.457792166456837e-08 2015 2
 10609     .027105719915531012 0                    0 1   .10061721484923938  6.963844146592192e-06  7.006826026742847e-07 2015 2
 10793     .039472495709511335 0                    0 1   .17351639418300063 1.1494945547854821e-06 1.9945615027937055e-07 2015 2
 11976       .0954298369236964 0                    0 1    .2909200724783572  .00023043439445079447  .00006703799073513148 2015 2
 12785      .11978752869639102 0                    0 1   .33001531139033147  4.855237499077712e-06 1.6023027151321453e-06 2015 2
 12825      .05058300700623135 0                    0 1   .12905372059020234  5.811846458189744e-06  7.500404089283762e-07 2015 2
 13323      .08641027253535714 0                    0 1   .19275485318561794                      0                      0 2015 2
 13540      .02828330962375349 0                    0 1   .11225595484201009  3.913364483740219e-06  4.392984667670682e-07 2015 2
 15087      .03737368692670096 0                    0 1   .06645690788926067  .00016557870861091768  .00001100384898657849 2015 2
 24755       .0370296865540033 0                    0 1    .1127069102821441  4.012251871984059e-06  4.522085117650721e-07 2015 2
 29517      .02865777193018523 0                    0 1   .20332490457688326  .00010644358983388052 .000021642632745794657 2015 2
 29609     .028272704295488806 0                    0 1   .12893158611442565 3.4117918531597446e-06 4.3988773512016146e-07 2015 2
 30651   -.0006460172451483254 0                    0 1    .0210258924137567  5.590620099409593e-06 1.1754777673637187e-07 2015 2
 62169   .00043671936413660576 0                    0 1   .06251200978251376  .00005419986141017882  3.388142266683988e-06 2015 2
 62655    -.011187436857955965 1   -.0051069995054153 0                    0  5.992196947847964e-06                      0 2015 2
 64102     -.01043502244832289 1   -.5030416328281823 0                    0   .0004350662526338043                      0 2015 2
109084     .013708757551434244 0                    0 1   .11646257928628762 .000020473213341144123  2.384363231988079e-06 2015 2
114415    .0009013693881088577 1   -.3727509100364015 0                    0    .011960478419136765                      0 2015 2
142953     .011804972349427182 0                    0 1   .12492671409783074    2.2119633379809e-07  2.763333115188232e-08 2015 2
158743    .0030878871564199825 0                    0 1  .039835321446818714                      0                      0 2015 2
160196     .020026576190701032 0                    0 1  .022427876670286607   .0002857421653776817 6.4085900445912856e-06 2015 2
163983     .007647473457115087 0                    0 1   .02616386452304997                      0                      0 2015 2
165124     .006958782127458552 0                    0 1   .08270988634152371                      0                      0 2015 2
170527     .004956947204239044 1 -.017872785932525694 0                    0  1.150555432710841e-06                      0 2015 2
180833      .03283019791080559 0                    0 1    .2131271864373609 1.6139877156369088e-06  3.439846607781577e-07 2015 2
185334     .002802076370491324 0                    0 1  .049911679324356105  3.960020602689222e-06 1.9765127843926782e-07 2015 2
 24463      .08099889360977813 0                    0 1    .2445613391904366                      0                      0 2015 3
 25731    -.004903491785870448 1 -.026859891311137484 0                    0    .008163491446872662                      0 2015 3
176378                       0 1  -.24768634317158578 0                    0    .021573286643321657                      0 2015 3
179700      .03533857624348892 0                    0 1   .12092738228985803   4.46697636036472e-08  5.401797580095831e-09 2015 3
184526                       . 0                    0 1  .005500977298567906   .0008814849828174304  4.849028879507206e-06 2015 3
  2710      .02290369072369447 0                    0 1   .15939347243463123 1.1638048655206791e-06 1.8550289875166007e-07 2015 4
  3505       .0045703184908747 0                    0 1  .013997239404938397 2.9863684052678446e-07  4.180091351987811e-09 2015 4
 17501  .000059692583196537826 1  -.07783912848828532 0                    0   .0010246698575867596                      0 2015 4
 30666       .0160459985291168 0                    0 1   .07080296850972788  .00011194853215699229  7.926288397021784e-06 2015 4
 61192     .001964956627778957 0                    0 1  .018356052115784328  .00005007548076924995   9.19188134723308e-07 2015 4
 61581      .04464324391100872 0                    0 1    .2486936404314476                      0                      0 2015 4
142812                       . 1    -.532233630701902 0                    0     .01451775795320653                      0 2015 4
  8543      .07011249479190779 0                    0 1   .18698208416277023  7.232649024553819e-08 1.3523757886289001e-08 2015 5
179621      .07519470299313452 0                    0 1   .26376489104773276                      0                      0 2015 5
  4322    .0009684744857066525 0                    0 1   .08282126636387924  .00020361324462409678  .00001686350676822602 2015 6
  6497    -.040878938640132666 0                    0 1  .020729684908789386     .00225864183933116 .000046820933651143454 2015 6
 12788     .027955921072874403 0                    0 1   .39231300082197434 .000012274607196126195  4.815487983023268e-06 2015 6
 13354     .003995747425759133 0                    0 1 .0010392526936952003 .000012071871834661488 1.2545725322115172e-08 2015 6
 22373                       0 0                    0 1  .036426359537132615    .007018338480304252   .0002556525208368556 2015 6
 24978     .005502660022579671 0                    0 1   .02024200000605353  .00011188642649431813  2.264805045775295e-06 2015 6
 27760     .003927149573958315 0                    0 1   .12016237022091718   .0001140121540138295  .00001369997066029401 2015 6
148410   -.0034496117963809404 1   -.2763387546177447 0                    0   .0006428559105355737                      0 2015 6
162233   .00010618529333687285 1   -.6391292805946376 0                    0    .016216561065551874                      0 2015 6
185372    -.036178411742066076 1  -.19202972578721544 0                    0   .0009392902761665989                      0 2015 6
  2342     .015548623222224953 0                    0 1   .05897668934685957  .00001450804183202674  8.556362761586844e-07 2015 7
  5671     -.00424717082157345 0                    0 1   .01390092436548136 .000018181772445809984 2.5274344359959765e-07 2015 7
  6116     .020706875443005347 0                    0 1   .04185744663223882 2.1320506229705555e-06  8.924219516822156e-08 2015 7
  8266                       . 0                    0 1   .05098640614941876   .0001682856280613809   8.58027938144759e-06 2015 7
 12669    -.012793953497171615 0                    0 1 .0071135327392776735  .00009523529422274089  6.774593833882092e-07 2015 7
 14418    .0004047096252164584 1  -.03588040101816313 0                    0 3.4889591432092496e-07                      0 2015 7
 19190    .0010898001069565091 1 -.008673112964381554 0                    0  .00012156584418145488                      0 2015 7
 20423    .0035898747582443716 0                    0 1    .2694201006062401 1.6489638575872764e-06  4.442640084072178e-07 2015 7
 21808    .0009257799580230656 0                    0 1   .03446791608322437 .000012885573692840176  4.441388727290188e-07 2015 7
 25314 -.000024063414565335907 0                    0 1  .005217612096097662  .00007387490786552827  3.854506128772806e-07 2015 7
 28749    -.028259450561925307 1 -.011770632307866956 0                    0  .00019692758126787008                      0 2015 7
 29011   -.0001830329555653152 0                    0 1 .0063264022432670645 .000012932735458016639  8.181768661317598e-08 2015 7
 29151     .011315748400187295 0                    0 1   .07042848355199736  .00012920149132702354  9.099465106818807e-06 2015 7
 31099     .035388818600216365 0                    0 1   .08465812200891139   .0000966033078385275  8.178254621458487e-06 2015 7
 31419     -.00566587238549113 1   -.3095843261848027 0                    0     .00484791417504855                      0 2015 7
 31560    .0005907114552045753 1 -.026904433362219024 0                    0 .000022310674168172674                      0 2015 7
 31629     .012437073034541125 0                    0 1   .04018746491939823  .00008406231887626426 3.3782514908831376e-06 2015 7
 63388    .0029247294110377638 0                    0 1  .024108420955068332   .0017978445810449722 .000043343193971620655 2015 7
 63708      .04134172695668514 0                    0 1   .08977209851023625    .003601741363650599  .00032333588050603425 2015 7
 64344    -.001781211900961234 0                    0 1   .06016394723970576 3.7145009839135378e-06  2.234790412180092e-07 2015 7
117036   .00040565240838221053 0                    0 1   .03472145996687968  .00019203106522994775   6.66759894377889e-06 2015 7
125240      .03610898930443016 0                    0 1   .06028593633248627  .00007505320850623521  4.524652949555713e-06 2015 7
147579        .157137013385451 0                    0 1    .8061098106947253  .00004423449981955168 .000035657864275714663 2015 7
147638     .040027213969837855 0                    0 1   .09574025777676987  3.952738456258593e-06  3.784361987263491e-07 2015 7
147657     .009560891196192947 0                    0 1   .01677945675349958  .00007839944537884431 1.3155001032326706e-06 2015 7
149318   .00024034686913694393 0                    0 1  .024197567301515358  9.853939675386468e-07 2.3844136848023647e-08 2015 7
155616                       . 1   -.1383776349344311 0                    0   .0010769908486004437                      0 2015 7
161844     .011016854755972446 0                    0 1   .12809038057550443   1.11542224925477e-06 1.4287486040942866e-07 2015 7
164606    -.032487133179016275 0                    0 1  .028858138529239696  .00017735926486137514  5.118258234813678e-06 2015 7
165746     .006547199651817359 0                    0 1 .0009392002029488492  4.678082464447056e-06  4.393656000020127e-09 2015 7
end

With the more specific data, the regression I am trying to run would be:

xtreg scaledcashtax neg0pti negptiinteraction pospti posptiinteraction scalednolbpd tripleinteraction i.calendaryear if ff48rec == `i', fe

My attempt to replicate what you did:

gen coefficient1 = .
gen residual = .

forvalues i = 1/3 {
     xtreg scaledcashtax neg0pti negptiinteraction pospti posptiinteraction scalednolbpd tripleinteraction i.calendaryear if ff48rec == `i', fe  
    replace coefficient1 = _b[neg0pti] if industry == `i'
    predict resid, resid
    replace residual = resid if industry == `i'
    drop resid
}

However when I run this, the coefficient 1 and residual variables remain blank

[Clyde Schechter]

OK, thanks.

One obvious problem here is that your data set does not contain a variable named industry, to which the code refers. So I think the reason you are getting all empty results because the code isn't even running. I would expect that you got an error message indicating that the variable industry was not found. Right?

If I change -industry- to -ff48rec- in the code wherever it appears, then the code runs. In the example data, the results are rather unsatisfactory because in the example data, most of the industries are represented by too few observations to allow a regression with so many independent variables. And, of course, as the loop only goes up to 3, those observations with ff48rec greater than 3 are never dealt with at all. On top of that, in the one group that is large enough, (ff48rec == 2), we still get no results because, having -xtset gvkey- (my best guess--you don't say how you -xtset- the data), in the example data, gvkey uniquely identifies observations in the data, so that every group for the gvkey is a singleton.

Assuming that you -xtset ff48rec-, then things are a bit better for ff48rec == 2. There are some variables that get omitted due to colinearity--perhaps that will not happen in the full data set, or perhaps it is expected based on how the variables are calculated.

Anyway, try replacing -industry- with -ff48rec-. It will at least partially solve your problem. Remaining difficulties after that will reflect issues about the suitability of your data for the proposed regressions.

[Taylor Foster]

Ah yes you were absolutely correct - it was just a silly manual error from me forgetting to replace my made-up/simplified "industry" with the variable's new name. Thank you very much for your assistance today - not only did you solve my problem but I learned a lot that I'm sure I will use in the future. Best wishes!