Problem with storing residuals from regression loop (by industry and year)

[Alexa Straus]

Hi all,

for part of my thesis I would like to run a regression loop by industry and year and store the residuals in one column. As a Stata Newbie I am struggling a bit to find the best code option for what I would like to do. I am using panel data with for about 1300 companies for the years 2000-2010 (see example below). The companies are sorted in 6 industry groups according to their SIC code. The residuals of the regression estimate the discretionary accruals of a company.
The regression command I am using is regress ACC CF_prev CF CF_next dSALE PPE,robust
Building on an earlier post I have tried it out only by industry with the code below.

gen newvar
forvalues IND = 1/6 {
reg ACC CF_prev CF CF_next dSALE PPE,robust predict temp, residuals replace newvar = temp if temp ~= . drop temp }

However it did not really work and I am also not sure how I would change it so that the regression runs per indsutry/year combination. Any help is greatly appreciated!

Thanks!

Alexa

* Example generated by -dataex-. To install: ssc install dataex
clear
input long ID int Year float IND double(ACC CF_prev CF CF_next dSALE PPE)
1004 2000 5   -.01648722216486931   .010502055287361145     .0665656328201294   .040039245039224625      -.2025349736213684 .27312490344047546
1004 2001 5   -.09419623017311096    .07027815282344818    .04227232187986374    .07553993165493011      -.3355883061885834  .2824305295944214
1004 2002 5   -.05386659502983093    .04177561402320862    .07465232163667679     .0321008563041687     -.04559848830103874  .3822309076786041
1004 2003 5    .01075703278183937    .07721582055091858    .03320317342877388    .04674048721790314      .06644276529550552  .4055716395378113
1004 2004 5    .02034282125532627   .032141901552677155    .04524652659893036   .035136450082063675       .1351911425590515  .3598010540008545
1004 2005 5    .05389426648616791    .04382912442088127   .034035757184028625    .03922264650464058       .2040834277868271 .46880897879600525
1004 2006 5     .0642876848578453    .02546129934489727   .029341479763388634    .01767946407198906      .16743136942386627 .41446375846862793
1004 2007 5    .09215901046991348   .026900626718997955   .016208747401833534    .04842207208275795      .30324089527130127 .44627973437309265
1004 2008 5   .050993748009204865   .012705482542514801    .03795640543103218    .05301356315612793     .028675999492406845 .30870479345321655
1004 2009 5   .008241673931479454    .03752928599715233   .052417006343603134    .09957089275121689     -.05214114114642143 .38371309638023376
1004 2010 5 -.0031478130258619785   .048103250563144684    .09137652814388275                     .       .2822246253490448  .4349392056465149
1013 2000 3     .5456927418708801                     .    .06075409799814224  -.016741173341870308       .8137096166610718   .630243182182312
1013 2001 3   -.26744741201400757   .025591988116502762  -.007052033208310604   -.17798765003681183      -.2229190170764923 .25470343232154846
1013 2002 3    -.1334560364484787  -.011201383545994759     -.282713919878006  -.008401012048125267      -.5421050786972046 .23110774159431458
1013 2003 3  .0030589138623327017    -.6176367402076721   -.01835344359278679    .05436112359166145     -.23990562558174133  .4899493157863617
1013 2004 3   .008327552117407322  -.016192466020584106   .047960519790649414   -.20379364490509033      .00855887122452259  .4233171343803406
1013 2005 3     .2919963598251343    .04355437308549881   -.18507106602191925    .05482809618115425       .2695189416408539  .4007422626018524
1013 2006 3   .054527685046195984   -.17218239605426788    .05100977420806885     .3298371136188507       .0734201967716217 .37543973326683044
1013 2007 3   -.20218442380428314   .048591289669275284     .3141988217830658   .050825364887714386     .025009308010339737 .36930617690086365
1013 2008 3   -.02487533539533615     .2868880033493042    .04640752077102661   -.20285584032535553      .07604260742664337 .33136898279190063
1013 2009 3  -.021499216556549072   .042634040117263794   -.18636126816272736   -.01728266105055809     -.23930245637893677  .2988547682762146
1013 2010 3     .1279398649930954   -.26644834876060486  -.024709729477763176                     .       .1190086305141449 .41403692960739136
1034 2000 3    .11065049469470978                     .  -.006929447874426842    .04210413619875908      .14509689807891846  .4624784290790558
1034 2001 3  -.004293868318200111  -.004992441274225712   .030334658920764923  -.019708961248397827      .04607202112674713  .4346769452095032
1034 2002 3  -.007222569547593594   .020440097898244858  -.013280291110277176    .03166056051850319      .10701721161603928   .317648708820343
1034 2003 3 -.0028490275144577026  -.013818481005728245    .03294362127780914   -.05864625424146652     .028961777687072754   .354287713766098
1034 2004 3   -.03984728455543518   .032486170530319214   -.05783189833164215    .15413512289524078     .018115133047103882 .35655277967453003
1034 2005 3   -.11464975029230118   -.06722386181354523    .17916682362556458   .003248266177251935     -.39217811822891235 .19952820241451263
1034 2006 3   .059355054050683975    .22115667164325714   .004009535536170006   .016155153512954712      .06172972917556763  .2763783931732178
1034 2007 3  .0070434873923659325   .007019778247922659   .028283974155783653                     .      .07397984713315964  .5771607756614685
1056 2000 3    .16099533438682556                     .  -.019465455785393715    .17673227190971375      .17443831264972687  .5512661933898926
1056 2001 3    .01456734724342823  -.012930897995829582    .11740320920944214   -.09025077521800995       .1885109692811966 .42871329188346863
1056 2002 3    .07775936275720596    .09411381185054779   -.07234763354063034    .06314221769571304     -.09725642204284668 .39863720536231995
1056 2003 3   .011367028579115868   -.07048184424638748    .06151382625102997    .03238975256681442      .27992403507232666  .4047289490699768
1056 2004 3    .09680414199829102     .0592530332505703   .031199345365166664    .11427155137062073       .3699790835380554 .40642014145851135
1056 2005 3   .009568523615598679   .018707238137722015    .06851762533187866   .015624112449586391      .08935583382844925 .27861717343330383
1056 2006 3    .08468269556760788    .06405029445886612   .014605424366891384                     .      .14999601244926453  .2903488874435425
1075 2000 4 .00042354516335763037                     .    .10500754415988922    .11409644037485123       .1916956752538681 1.2684789896011353
1075 2001 4  .0001766642672009766     .0970664918422699    .10546804964542389    .10025330632925034      .12046157568693161 1.3067622184753418
1075 2002 4  -.009607795625925064    .09446640312671661    .08979561924934387    .07904735207557678     -.23980887234210968  1.246184229850769
1075 2003 4   .004484198056161404    .08506320416927338    .07488138973712921    .08321043848991394      .02143094688653946 1.2568868398666382
1075 2004 4  -.006794193759560585    .06616096943616867    .07352004945278168    .07769636809825897      .00858533475548029 1.1382702589035034
1075 2005 4   -.01719019189476967    .07084297388792038    .07486721873283386    .05814890190958977     .008915050886571407 1.1279499530792236
1075 2006 4   .008858531713485718    .06543894857168198   .050826024264097214    .05545179918408394      .03654561564326286  1.027502417564392
1075 2007 4  .0038717882707715034    .05023462325334549    .05480657517910004   .043216343969106674     .010638320818543434 1.0827534198760986
1075 2008 4   .009679277427494526    .05584108084440231   .044032078236341476    .04217059165239334    -.013922804966568947  1.154585361480713
1075 2009 4   .001048443140462041    .04260585457086563     .0408046655356884    .06171370670199394    -.006021896842867136 1.1617485284805298
1075 2010 4   .002360910177230835    .04015478864312172    .06073082610964775                     .   -.0028332960791885853 1.2031036615371704
1078 2000 3    .03128454461693764                     .    .21841464936733246    .17787356674671173      .03927090764045715  .6998733282089233
1078 2001 3   .009447726421058178    .20680727064609528     .1684206873178482    .20909179747104645      .16615113615989685  .7344905138015747
1078 2002 3    .03328600898385048    .11048975586891174     .1371714025735855    .14585435390472412      .06007003411650658  .5214393734931946
1078 2003 3   .025942383334040642    .13172800838947296    .14006640017032623    .16413439810276031       .0822741910815239  .5478664040565491
1078 2004 3   .018071375787258148     .1271885335445404    .14904367923736572    .23462410271167755 -.000020400264475028962  .4679591655731201
1078 2005 3  -.053430408239364624     .1384115368127823     .2178869992494583    .07659982144832611      .09065064787864685  .4435708224773407
1078 2006 3    .03678365424275398    .21509279310703278    .07561749964952469    .15314984321594238     .006468985229730606 .49421223998069763
1078 2007 3   .027592027559876442    .06090924143791199    .12336086481809616    .18783272802829742      .09502735733985901 .43113845586776733
1078 2008 3   -.00560004822909832    .11237797886133194     .1711098700761795    .15463458001613617        .090983547270298  .3824520707130432
1078 2009 3   .039939507842063904    .16019737720489502    .14477278292179108    .14450471103191376     .029164975509047508  .3886660933494568
1078 2010 3   .021380659192800522    .11716028302907944     .1169433444738388  -.000714181805960834      .08398126810789108  .3314655125141144
1111 2000 6    -.1897190362215042   -.12086059153079987    .27627313137054443    .23166750371456146      .11712517589330673 .11308949440717697
1111 2001 6   -.03695162013173103    .23772841691970825    .19934602081775665   -.02348334528505802      .46186351776123047 .11860305815935135
1111 2002 6    .14856334030628204      .128851979970932  -.015179011039435863    .02794821746647358      .13949328660964966 .09260406345129013
1111 2003 6       .10236856341362  -.011993193067610264   .022082358598709106    -.3456589877605438      .11852739006280899 .08263007551431656
1111 2004 6     .4051961898803711   .016064953058958054    -.2514674961566925    .09123291820287704      .47294893860816956 .07114139944314957
1111 2005 6  -.025966305285692215    -.1864061802625656    .06762854009866714 -.0032135590445250273     .047547634690999985 .07130959630012512
1111 2006 6    .07599973678588867   .062321655452251434  -.002961387624964118     .7169119715690613     .031737592071294785 .07516560703516006
1111 2007 6   -.36153852939605713  -.002341207815334201     .5667747855186462                     .       .7721097469329834  .0739007294178009
1161 2000 3    .16423380374908447                     .    .19786058366298676     .1348647028207779       .4081101417541504  1.247642159461975
1161 2001 3  -.004873833153396845    .15017575025558472     .1023620143532753   .002808553399518132     -.13045553863048553 1.0621635913848877
1161 2002 3   -.09970211237668991    .10454607009887695  .0028684784192591906    .11443515866994858      -.2115590125322342 1.2465665340423584
1161 2003 3   .013334673829376698  .0028828028589487076    .11500661820173264    .19163042306900024      .14630939066410065 1.7925020456314087
1161 2004 3    .03363270312547684    .09109269827604294    .15178370475769043      .131353497505188        .208936408162117  1.555828332901001
1161 2005 3   .057744503021240234    .13727399706840515    .11879679560661316    .25012487173080444      .10786835849285126  .6995739340782166
1161 2006 3   -.17715026438236237    .12786708772182465      .269222229719162    -.3039334714412689    -.027247944846749306  1.006753921508789
1161 2007 3   .010724879801273346     .1492380052804947   -.16847950220108032   -.05978550389409065      .02768692560493946  .5999847650527954
1161 2008 3    -.0350649356842041    -.1917748898267746   -.06805194914340973   .012121211737394333    -.017748918384313583  .6197402477264404
1161 2009 3    .16872964799404144   -.10241042077541351   .018241042271256447   .016677524894475937    -.052768729627132416  .9765472412109375
1161 2010 3    .07270324230194092   .015421899035573006   .014100021682679653                     .       .1201806589961052 .22901520133018494
1209 2000 3  .0026349348481744528                     .    .08452431112527847    .13563232123851776       .0542772151529789 1.2520065307617188
1209 2001 3  -.001414666185155511    .08416661620140076    .13505834341049194    .13202345371246338       .0302400104701519   1.23650324344635
1209 2002 3   .001793644274584949     .1381724625825882    .13506759703159332    .12617360055446625     -.03908907622098923 1.3458269834518433
1209 2003 3  .0024014131631702185     .1285344362258911    .12007062882184982    .14507357776165009      .10548557341098785  1.380011796951294
1209 2004 3   .009181606583297253     .1081436350941658     .1306629627943039     .1483900249004364      .11812041699886322 1.2936416864395142
1209 2005 3   .004023742862045765     .1227441132068634    .13939683139324188    .15426675975322723      .07291541993618011 1.2861340045928955
1209 2006 3 -.0036123301833868027     .1344631463289261    .14880678057670593     .1601049154996872      .06791368871927261 1.3056548833847046
1209 2007 3   .019336892291903496    .13853336870670319     .1490514874458313    .18510469794273376      .10620086640119553 1.3494950532913208
1209 2008 3  -.008697026409208775     .1316402703523636    .16348198056221008    .13209842145442963      .02975631132721901 1.1839804649353027
1209 2009 3   -.01528083998709917    .16462896764278412    .13302522897720337    .14322306215763092     -.17168471217155457 1.2529571056365967
1209 2010 3   .007061117794364691    .12835116684436798    .13819067180156708                     .     .059083130210638046 1.2517902851104736
1230 2000 4    .07490482181310654                     . -.0027062990702688694    .23187008500099182      .04366771876811981  1.067565679550171
1230 2001 4    -.1296958178281784 -.0022433472331613302    .19220532476902008  -.049847912043333054    -.009277566336095333  .9580988883972168
1230 2002 4    .08764616400003433     .1713268905878067   -.04443315044045448    .01660735160112381     .024165397509932518  .8780884742736816
1230 2003 4    .05786788463592529   -.04550977423787117    .01700975000858307    .01753046363592148      .07661332190036774  .9962509274482727
1230 2004 4    .04277123138308525   .015034360811114311   .015494602732360363   .029884634539484978      .08560383319854736  .8692930936813354
1230 2005 4    .03913043439388275   .015142431482672691    .02920539863407612    .08860569447278976      .07541229575872421  .9150824546813965
1230 2006 4   -.05026371404528618    .02568565495312214    .07792721688747406    .14570148289203644      .09469936788082123  .8610495924949646
1230 2007 4  -.061342619359493256    .07247798144817352    .13551297783851624   -.01655588485300541     .042088739573955536  .9775820970535278
1230 2008 4   .030327996239066124    .12302656471729279  -.015030395239591599   .018504085019230843      .02545147016644478  .9684918522834778
1230 2009 4   .053292252123355865   -.01395897101610899   .017185041680932045    .09934651106595993    -.045599304139614105  .9320249557495117
1230 2010 4  .0002407223219051957    .01667000912129879    .09636910259723663                     .      .08676028251647949  .9281043410301208
end

[Nick Cox]

"did not really work" is not a good problem report. In fact, we already advise against that (http://www.statalist.org/forums/help#stata 12.1)

Never say just that something "doesn't work" or "didn't work", but explain precisely in what sense you didn't get what you wanted.

Rearranging your code, and assuming that you put one command on each line,

gen newvar
forvalues IND = 1/6 {    
    reg ACC CF_prev CF CF_next dSALE PPE,robust      
    predict temp, residuals      
    replace newvar = temp if temp ~= .      
    drop temp  
}

the problems I can see are

1. You need to initialise correctly

gen newvar = .

2. The regression is always done on the entire dataset. Nothing in the loop machinery limits it to any subset:

reg ACC CF_prev CF CF_next dSALE PPE if IND == `IND' ,robust

Whether you have enough data for the regressions to work (well) is a separate question.

[Alexa Straus]

Hi Nick,

thank you for the fast reply and your help. Apologies for being imprecise. Let me try to do it better this time. I tried this new version of the code now:

gen newvar=.
forvalues IND = 1/6 { 
forvalues Year=2000/2010  {
    reg ACC CF_prev CF CF_next dSALE PPE if IND ==`IND' & Year==`Year',robust      
    predict temp, residuals      
    replace newvar = temp if temp ~= .      
    drop temp  
}
}

For which Stata gives me the error no observations r(2000). I think it has something to do with the way I coded the if statement. So I was wondering whether the Year limit should go somewhere else in the code.

Regarding the amount of data. I have between 20 and 2000 observations for each industry and year and read somewhere that 20 is a lower minimum. Would you agree with that?

Thanks again!

Alexa

[Roman Mostazir]

Not sure why you are having the error !!! I implemented exactly the same code as yours (#3) on hypothetical data and finished smoothly.

set obs 10000
gen IND=floor((6-1+1)*runiform()+1)
gen Year = floor((2010-2000+1)*runiform()+2000)

loc all ACC CF_prev CF CF_next dSALE PPE
foreach var of loc all {
gen `var' = rnormal()
}

//Hypothetical data example

li in 1/10, clean noobs

   IND   Year         ACC     CF_prev          CF     CF_next       dSALE         PPE  
      5   2000   -.5466629   -1.032927   -.3377286    1.591364   -.1868865     1.57137  
      3   2005   -1.840036    .9058285   -.6002342   -1.039842    1.234516    1.846865  
      2   2002   -.6418102   -2.080284   -1.453076    -.555684    .2872522   -.3810118  
      1   2000   -.2938689   -.1121605    .7581662    1.650273    .7766241    .2203996  
      5   2008   -.6086062    -.236618    .7650568   -.2646004   -.3570794    .2901241  
      3   2005   -.0511031    .6404909    .5954695    1.296005    .2223045    2.129478  
      2   2000    .0414784   -.2333049    1.553701   -.6968812   -.5163202   -.1393682  
      6   2008    .6609436    .2718188    .5641794    .4558699     .435329   -1.669046  
      2   2008    .5665206    -.605439   -.2033229    1.421315   -.7156065    2.613141  
      5   2005    .9921234   -.1024982     .015069   -2.114321   -.1338761    1.665226  


//Regression:

 gen newvar=. //New variable

forvalues IND = 1/6 {
forvalues Year=2000/2010  {
    reg ACC CF_prev CF CF_next dSALE PPE if IND ==`IND' & Year==`Year',robust      
    predict temp, residuals      
    replace newvar = temp if temp ~= .      
    drop temp  
}
}

//New variable with residuals "newvar''

li in 1/10, clean

       IND   Year         ACC     CF_prev          CF     CF_next       dSALE         PPE      newvar  
  1.     5   2000   -.5466629   -1.032927   -.3377286    1.591364   -.1868865     1.57137   -.4124069  
  2.     3   2005   -1.840036    .9058285   -.6002342   -1.039842    1.234516    1.846865   -1.827467  
  3.     2   2002   -.6418102   -2.080284   -1.453076    -.555684    .2872522   -.3810118   -.4603041  
  4.     1   2000   -.2938689   -.1121605    .7581662    1.650273    .7766241    .2203996   -.2570139  
  5.     5   2008   -.6086062    -.236618    .7650568   -.2646004   -.3570794    .2901241   -.6631202  
  6.     3   2005   -.0511031    .6404909    .5954695    1.296005    .2223045    2.129478   -.0528797  
  7.     2   2000    .0414784   -.2333049    1.553701   -.6968812   -.5163202   -.1393682   -.0770799  
  8.     6   2008    .6609436    .2718188    .5641794    .4558699     .435329   -1.669046    .5945024  
  9.     2   2008    .5665206    -.605439   -.2033229    1.421315   -.7156065    2.613141    .6587997  
 10.     5   2005    .9921234   -.1024982     .015069   -2.114321   -.1338761    1.665226     .952503

[Nick Cox]

You can get counts easily by

tab year IND

although that takes no account of missing values in the regression variables.

This is more paranoid:

gen newvar=.
forvalues IND = 1/6 { 
forvalues Year=2000/2010  {
    capture { 
     reg ACC CF_prev CF CF_next dSALE PPE if IND ==`IND' & Year==`Year',robust      
    predict temp, residuals      
    replace newvar = temp if temp ~= .      
    drop temp  
    } 
}
}

[Alexa Straus]

Hi again,

I figured out now that the code only works if I do not declare the data set to be panel data. However, I need to do so in order to calculate some of my variables. If I try to run the code with the data set being panel data it gives me the no observations r(2000) error. I tried to fix it by copying the Year column (Year2) since Stata changes the content of the original Year column if it is used as the time variable for the panel data. I still get the same error though.
This is the code I used

gen DACC=.
forvalues IND = 1/6 { 
forvalues Year2=2000/2010  {
    reg ACC CF_prev CF CF_next dSALE PPE if IND ==`IND' & Year2==`Year2',robust      
    predict temp, residuals      
    replace Kdacc = temp if temp ~= .      
    drop temp  
}
}

Is there any way by how the functioning of the code is influenced by the panel data setting that I am overlooking?

As always, any help is very much appreciated.

Alexa

[Nick Cox]

Sorry, but this is difficult to follow and does not allow much precise and useful comment.

1. We can't see your tsset or xtset settings or see why that makes a difference to definitions of variables. Nothing you've shown us explains that. I realise that the full dataset may be too large to show completely but without examples of what is happening the thread reduces to a guessing game, even for experienced Stata users. So, what did you specify please?

2. You're not applying the capture code I suggested in #5. Your choice, but it remains a way to catch industry-year combinations that would be empty.

3. "Stata changes the content of the original Year column [meaning: variable] if it is used as the time variable for the panel data." This isn't precise at all, but at a wild guess it suggests to me that you are supplying tsset or xtset settings that clash with the nature of the data or previous statements and have a side-effect of changing display formats (NB not the content of the variable). See #1 again.

4. In your code you have (other stuff set aside)

gen DACC=.      

replace Kdacc = temp if temp ~= .

which would work if and only if you had previously something like

generate Kdacc = .

This is unlikely to be linked to any other problems, but it should be cleaned up. (Conversely, if you are not copying and pasting exact code, that is not best practice.)