Panel data regression by year and Industry

[Priyesh VP]

Hi Forum members,
My data is like the following.
I want to run panel regression for each industry(SIC) and Year ,and also save the beta coefficients of each regression. Can someone help me??

Thanks in Advance

It is just a representation image. Each SIC and Year have many observations

Company	SIC	Year	DV	IV1	IV2
A	121	2000	xxx	xxx	xxx
A	121	2001	xxx	xxx	xxx
A	121	2002	xxx	xxx	xxx
B	121	2000	xxx	xxx	xxx
B	121	2001	xxx	xxx	xxx
B	121	2002	xxx	xxx	xxx
C	34	2000	xxx	xxx	xxx
C	34	2001	xxx	xxx	xxx
C	34	2002	xxx	xxx	xxx
D	345	2000	xxx	xxx	xxx
D	345	2001	xxx	xxx	xxx
D	345	2002	xxx	xxx	xxx
E	23	2000	xxx	xxx	xxx
E	23	2001	xxx	xxx	xxx
E	23	2002	xxx	xxx	xxx
F	34	2000	xxx	xxx	xxx
F	34	2001	xxx	xxx	xxx
F	34	2002	xxx	xxx	xxx

[Kris Bitney]

What type of model do you want to use?

You first need to xtset your data.

xtset SIC year

Then you can use panel estimators. Read up on your options.

help xtreg

The fixed effects estimator is a common choice.

xtreg DV IV1 IV2, fe cluster(SIC)

The coefficient vector can be pulled from the most recently run regression. It is stored in e(b). You can display it in the output log.

matrix list e(b)

You could also save it to a local macro.

local b = e(b)

If you don't know how macros work, you may want to give their help file a quick read. Notably, local macros are dropped when your do file ends.

help macro

If macros are a bit over your head at this point and you need to save your coefficients, you may want to copy/paste your regression output to MS Excel and save them that way.

[Priyesh VP]

Thanks Bitney,
I was looking for a regression for each industry for each year. So I will be able to capture the industry wide effects. I will use the coefficients for my firm wise regression..
Basically industry wide fixed effects I have to control for.

[Priyesh VP]

What type of model do you want to use?

You first need to xtset your data.

xtset SIC year

Then you can use panel estimators. Read up on your options.

help xtreg

The fixed effects estimator is a common choice.

xtreg DV IV1 IV2, fe cluster(SIC)

The coefficient vector can be pulled from the most recently run regression. It is stored in e(b). You can display it in the output log.

matrix list e(b)

You could also save it to a local macro.

local b = e(b)

If you don't know how macros work, you may want to give their help file a quick read. Notably, local macros are dropped when your do file ends.

help macro

If macros are a bit over your head at this point and you need to save your coefficients, you may want to copy/paste your regression output to MS Excel and save them that way.

I want to run the regression for each industry and identify the coefficients and residuals for respective industry groups. But I was able to find residuals for the last run regression only. Is there anyway I can keep residuals and coefficients for each group wise regression ???

[Kris Bitney]

Yes, you have to save the coefficients after each regression. How you do that is up to you.

[Priyesh VP]

Hi,

But now I understand that companies are not important in my study. I want to run regression based on SIC codes only. One regression for each industry code for each year. And save the residual and coefficients after each regression. Plus I want to put another criteria that at least 20 observations should be under each SIC to run regression. In that case how can I proceed ???

[William Lisowski]

I believe that the statsby command will do what you need, allowing you to run the regression command of your choice over all combinations of SIC and year. See help statsby for syntax, but you should start by reading the full documentation for statsby in the Stata Data-Management Reference Manual PDF included as a PDF in the Stata installation (since version 11) and accessible from within Stata - for example, through Stata's Help menu.

To include your restriction to 20 observations or more, you can make a copy of your dataset dropping those SIC/year combinations with less than 20 observations.

by SIC year: drop if _N<20

[Priyesh VP]

Thank you very much for your help.It worked
But few regression outputs are shown even when the observation is less than 20. And 0 is coming as coefficients for some IVs in regression results

[Carlo Lazzaro]

Priyesh:
have you investigated the existence of missing values?

[Priyesh VP]

Priyesh:
have you investigated the existence of missing values?

Yes Sir, there are some missing values of variables for certain SIC-years. Is that an issue in my case?? Isn't Stata automatically avoids such missing observations ??
I have five independent variables for one dependent variable.

[Carlo Lazzaro]

Priyesh:
yes, Stata omits observations with missing values by default via listwise deletion (or reports that you're dealing with an unbalanced panel dataset, that Stata can handle without any problem, though).
My remark related to your complaint about the limited number of regression outputs shown by Stata. As usually recommended on this forum, you would be better off with posting what you typed and what Stata gave you back or (even better in your case) to provide interested listers with an excerpt/example of your dataset via -dataex-. Thanks.

[Priyesh VP]

Priyesh:
yes, Stata omits observations with missing values by default via listwise deletion (or reports that you're dealing with an unbalanced panel dataset, that Stata can handle without any problem, though).
My remark related to your complaint about the limited number of regression outputs shown by Stata. As usually recommended on this forum, you would be better off with posting what you typed and what Stata gave you back or (even better in your case) to provide interested listers with an excerpt/example of your dataset via -dataex-. Thanks.

Please see the dataex codes. Kindly advice me how to save residuals (Y-y bar) as variables from the regression based on nic2digits and Year ..

rstatsby _b, by(nic2digit year) clear verbose nodots: regress ACC_w CFO_w PPE_w Rev_w LaCFO LeCFO

The above code is to get coefficients, that worked in my case. What if want to get residuals for each nic2digit -year regression ??

* Example generated by -dataex-. To install: ssc install dataex
clear
input long code int(year nic2digit) float(ACC_w CFO_w PPE_w Rev_w LaCFO LeCFO)
 11 2003  8 -.0035705785    .05129731 .57081646      .5389193            .    .02149475
 11 2004  8  -.064360715    .02149475  .7319333      .6442248    .05129731    .09297886
 11 2005  8   -.14587593    .09297886  .9083844      .7705522    .02149475    .07464965
 11 2006  8   .001665048    .07464965  .9456084      .9281254    .09297886     .0779597
 11 2007  8   .018513855     .0779597   .897733     1.0945843    .07464965    .07567864
 11 2008  8    .02610365    .07567864  .8343296     1.1711544     .0779597  .0012792398
 11 2009  8    .10809576  .0012792398  .7411367      1.256305    .07567864    .02340646
 11 2010  8   .074156255    .02340646  .6722737      1.265667  .0012792398     .0412007
 11 2011  8    .03789344     .0412007   .603212     1.3171893    .02340646    .05202771
 11 2012  8   .032831736    .05202771  .5085389     1.1589074     .0412007     .1516278
 11 2013  8   -.11012533     .1516278  .7010964     1.0286334    .05202771    .07685912
 11 2014  8  -.028768126    .07685912  .6780298      .9607556     .1516278     .0752905
 11 2015  8   -.01868411     .0752905  .6458484      .9593536    .07685912            .
289 1999 52   -.01046043    .07836749  .6197376      1.916488            .    .06201674
289 2000 52   .002710243    .06201674  .6055002       1.72499    .07836749   .034581266
289 2001 52   .016388513   .034581266  .5678845     1.6537362    .06201674    .05632088
289 2002 52  -.016591826    .05632088 .56229544     1.4730192   .034581266     .0962406
289 2003 52   -.04691729     .0962406  .5957895     1.4854136    .05632088    .04815172
289 2004 52  -.013778798    .04815172  .5920439      1.471961     .0962406   .018306635
289 2005 52   .015353953   .018306635  .6179966     1.4623164    .04815172    .06809493
289 2006 52   -.01211859    .06809493  .6357931      1.543966   .018306635            .
289 2012 52    -.1381082     .2212753  .6271381     1.5425204            .    .11337147
289 2013 52  -.014867387    .11337147   .627367     1.0816482     .2212753   .032804012
289 2014 52   -.06929935   .032804012  .6669453     1.1240424    .11337147     .0384489
289 2015 52   -.08314164     .0384489  .6258627     1.2276536   .032804012            .
365 2002 30    .05809642 -.0043263286 .43692285      .5581691            .    .11870936
365 2003 30    -.0775951    .11870936  .3355012      .4123985 -.0043263286   .022299957
365 2004 30    .08607177   .022299957  .4145841      .9940466    .11870936   .008239828
365 2005 30      .244347   .008239828  .4922633     1.4301988   .022299957    .21812125
365 2006 30   -.07961965    .21812125 .22461157      .8472432   .008239828   -.30929035
365 2007 30     .4094186   -.30929035 .13937888     .55072874    .21812125    .08773988
365 2008 30   .015734997    .08773988 .13802543     .51798517   -.30929035   -.07442024
365 2009 30     .1510003   -.07442024  .1898739      .4478916    .08773988   -.06114458
365 2010 30    .13817917   -.06114458 .15522642      .3803483   -.07442024    .23097613
365 2011 30    -.1772575    .23097613 .17284165      .3218299   -.06114458   -.05273814
365 2012 30    .11156725   -.05273814 .15883617     .29010478    .23097613  -.007510649
365 2013 30    .05078186  -.007510649 .14450423     .21438725   -.05273814  -.020489113
365 2014 30      .066911  -.020489113 .11699347      .1403223  -.007510649   -.07074534
365 2015 30    .06130627   -.07074534 .10675485     .03154876  -.020489113            .
400 1999 21  -.009715475     .0388619  .4677307      .3247745            .     .1261745
400 2000 21  -.030872483     .1261745 .41879195      .2214765     .0388619   .015564202
400 2001 21   -.05447471   .015564202  .4856031     .04357977     .1261745 -.0040650405
400 2002 21    -.2703252 -.0040650405  .6321138   .0020325202   .015564202  .0023121387
400 2003 21   -.03699422  .0023121387   .716763     .03468208 -.0040650405    .12642486
400 2004 21    -.1015544    .12642486  .7212435      .5284974  .0023121387    .10670732
400 2005 21  -.033536587    .10670732  .5609756      .8841463    .12642486    -.2972247
400 2006 21     .4726947    -.2972247  .3267681      .7162041    .10670732     .0938647
400 2007 21      .051914     .0938647  .4488726     .56318825    -.2972247   -.05990445
400 2008 21      .168688   -.05990445  .3289232      .6196252     .0938647   .016607355
400 2009 21  -.012940796   .016607355 .19389626     .17577915   -.05990445  -.014462013
400 2010 21    -.3735056  -.014462013  .6935982     .04300039   .016607355  -.011289936
400 2011 21  -.003122748  -.011289936  .8575547             0  -.014462013  -.002899952
400 2012 21  -.001449976  -.002899952  .8629773 -.00024166264  -.011289936            .
414 2001 46   -.09983687    .10636216 .22903752      1.395106            .    .09618574
414 2002 46   -.14726368    .09618574 .23349917       2.66136    .10636216    .20976925
414 2003 46   -.19778244    .20976925  .2091699      1.859155    .09618574   -.08047337
414 2004 46    .09467456   -.08047337  .2005917      1.895858    .20976925    -.1187779
414 2005 46    .12564367    -.1187779  .2279437     2.7092345   -.08047337    .01638807
414 2006 46  -.007866274    .01638807  .2182891     2.2477877    -.1187779    .08676671
414 2007 46   -.08894952    .08676671  .2095498     1.9050478    .01638807    .10735586
414 2008 46   -.09045725    .10735586 .17097415      2.392147    .08676671     .1649399
414 2009 46   -.15375052     .1649399 .14338997      2.220058    .10735586   -.09326247
414 2010 46    .11383507   -.09326247 .12309275      2.726213     .1649399    .10933204
414 2011 46   -.09639334    .10933204 .11612486      3.250526   -.09326247     .1197113
414 2012 46   -.12742658     .1197113 .09059233       2.53783    .10933204     .1598692
414 2013 46   -.17570126     .1598692 .06281191      3.027706     .1197113   -.11782376
414 2014 46    .10063418   -.11782376 .06058078      2.789052     .1598692   -.14030588
414 2015 46     .1349778   -.14030588 .07696103      3.078046   -.11782376            .
415 1999 62  -.033755273   .014064698 .27566808             .            .   .023255814
415 2000 62    .12684989   .023255814  .5285412     1.2198732   .014064698   .073897496
415 2001 62   -.05403258   .073897496 .35359555     .25744936   .023255814   -.12821469
415 2002 62    .15430488   -.12821469  .3317182      .7752516   .073897496    .05435592
415 2003 62   -.05212212    .05435592  .3380491       .641102   -.12821469   -.08753943
415 2004 62    .10015773   -.08753943  .1884858      .4416404    .05435592  .0008396305
415 2005 62  -.004198153  .0008396305  .2031906      .4819479   -.08753943   -.07029877
415 2006 62    .08435852   -.07029877  .2513181       .473638  .0008396305    .09372846
415 2007 62  -.014472777    .09372846  .2046864     1.2549965   -.07029877    .14536454
415 2008 62    .14131413    .14536454 .12826283      1.260126    .09372846    .50344926
415 2009 62    -.3258477    .50344926 .08454426     1.1947747    .14536454   .033081625
415 2010 62      .134484   .033081625 .08222462      .9063886    .50344926   -.14627369
415 2011 62     .3423638   -.14627369  .0724422     1.0449538   .033081625    .08888542
415 2012 62    .13934234    .08888542 .05998594      1.447317   -.14627369    .05757972
415 2013 62    .10372615    .05757972  .3581514      .9354638    .08888542    .08161873
415 2014 62   .067409344    .08161873  .3454587      .8416505    .05757972   .074147165
415 2015 62    .07339536   .074147165  .3028851        .77145    .08161873            .
612 1999 85    -.3899868     .2160738  .3926219     .02635046            .            .
612 2002 85    .14079422   -.15523465  .5379061             .            .    .11764706
612 2003 85   -.11418685    .11764706  .5051903             .   -.15523465     .3172905
612 2004 85   -.30659536     .3172905  .5240642      .9233512    .11764706     .1084991
612 2005 85     -.079566     .1084991  .1772152     1.0922242     .3172905    .04651163
612 2006 85  -.006644518    .04651163 .27242523     1.2990034     .1084991    .01506024
612 2007 85  -.012048192    .01506024 .29216868      .9457831    .04651163    .06257822
612 2008 85   -.06508135    .06257822   .310388     .29036295    .01506024    .17873304
612 2009 85   -.19004525    .17873304  .4366516      .0882353    .06257822    .33202615
612 2010 85    -.3267974    .33202615  .6431373      .2535948    .17873304   -.35335195
612 2011 85     .4371508   -.35335195  .8617318      3.340782    .33202615    .22064617
612 2012 85   -.12765957    .22064617  .8542159      1.609141   -.35335195            .
612 2014 85    -.1792396    .24140012 1.1080265     1.3922752            .     .2470212
612 2015 85   -.26794538     .2470212 1.3089218     1.4623656    .24140012            .
666 1999 46    -.1541502     .1506368  .4743083     2.2125604            .   -.01076779
end

[William Lisowski]

Let me emphasize, in response to post #10, that as Carlo says iin #11, Stata does indeed avoid (omit) such observations when running the regression. But they are not avoided when selecting SIC/year combinations that appear 20 times or more in your data. So you may start with 20 or more observations for a given year, but when Stata runs the regression and omits observations with missing values, the count drops to below 20.

In post #7 above, you could anticipate this problem with code like the following.

drop if missing(y,x1,x2,x3,x4,x5)
by SIC year: drop if _N<20

where the arguments to the missing() option are your dependent and independent variables. The they will not be included when counting observations for the regression.

Someone other than me may want to write for you a loop that saves both coefficients and residuals. It would be faster, perhaps, for you to just run your regressions twice: once with statsby to save the coefficients and other statistics you may want, and a second time in a loop, as you do in your other topic on this question.

https://www.statalist.org/forums/for...ustry-and-year