DWatson in Panel Data

[Kate Pryce]

Hi all,

I have a panel dataset of 100 companies throughout a 72 month period (each company recorded activity at different times during the months, so the observations range from 3 to 61 months). I've been recording my regression results in a table for each of the companies using the following:

xtset Fullid Time

statsby _b _se  N=e(N) R=e(r2), by(Fullid) clear: regress logthpp logcpn
gen df_r = _eq2_N -1
gen plogthpp = 2 * ttail(e(df_r), abs(_b_cons/_se_cons))
gen plogcpn = 2 * ttail(e(df_r), abs(_b_logcpn/ _se_logcpn))
list, clean 
export excel using Results.xlsx, replace

I've been trying to add in a Durbin Watson test in this regression, but I can't seem to place it anywhere in the code where it will work. I initially tried placing it before stats by, ex:

xtset Fullid Time
estate Watson

statsby _b _se  N...(continues on as above)

This gave me a d-statistic of = .

I'm not sure why this has happened. Is estat dwatson the proper code to use for panel data?

[Andrew Musau]

I don't think you can get post-estimation statistics with statsby. Install runby from SSC by Robert Picard and Clyde Schechter.

program my_results
    xtset Fullid Time
    regress logthpp logcpn
    gen b=_b[logcpn] 
    gen se=_se[logcpn] 
    gen N = `e(N)' 
    gen R=`e(r2)'
    estat dwatson
    gen D= r(dw)
    keep in 1
end

use mydata, clear
*ssc install runby
runby my_results, by(Fullid)
browse

[Kate Pryce]

Hmm... Maybe I am not fully understanding what is happening here.

When I ran what you suggested I got the error: file my data.dta not found, so I altered the code to end with:

runby my_results, by (Fullid) 
list

However, when I do this, I end up with results (mostly 0s) for every single one of my variables rather than just the regression results of logthpp, logcpn, R, N, and D-stat. Why are all these other variables still present? I tried just keeping the four variables that are of use in the regression:

 keep Fullid Time logthpp logcpn

But then when I ran the code I ended up with only regression results for one of my companies.

Is there another way to run a regression through panel data, at a d-stat to each regression, and then export all those results to excel?

[Andrew Musau]

use mydata, clear

This is pseudo code. Here, you are supposed to use your dataset. If your dataset is loaded into memory, just run the code excluding that line.

But then when I ran the code I ended up with only regression results for one of my companies.

You should get the same results as with statsby + the DW statistic. If it is failing, provide a sample of your dataset using dataex.

[Kate Pryce]

Andrew,

Okay, here is some data:
* Example generated by -dataex-. To install: ssc install dataex
clear
input long Fullid float(logcpn logthpp Time)
1 2.968016 -.07918125 -6209
1 2.9726655 -.06382149 -6150
1 3.001734 -.05305674 -6089
1 3.0216026 -.04175473 -6058
1 3.043755 -.04175473 -6028
1 3.062958 .04270675 -5997
1 3.075912 .09633057 -5966
1 3.0982976 .0934217 -5936
1 3.1169395 -.03229605 -5905
1 3.1332195 .09108047 -5875
1 3.1504495 -.05142405 -5844
1 3.159266 -.024568195 -5813
2 1.544068 .3460734 -6209
2 1.832509 .3656217 -6178
2 2.045323 .59423465 -6150
2 2.2479732 .59423465 -6119
2 2.3961995 .3767073 -6089
2 2.5037906 .20431383 -6058
2 2.591065 .20431383 -6028
2 2.668386 .1045359 -5997
2 2.731589 .1033149 -5966
2 2.7916906 .19728057 -5936
2 2.841985 .10648232 -5905
2 2.886491 .1775365 -5875
2 2.926857 .11750802 -5844
2 2.951823 .1645653 -5813
2 2.977266 .1645653 -5784
3 1.2787536 .6373078 -6484
3 1.6812413 .456642 -6454
3 1.9822712 .3004374 -6423
3 2.1731863 .20527 -6393
3 2.4785664 .10792907 -6362
3 2.6294096 .10359641 -6331
3 2.748188 .06773569 -6301
3 2.848805 .01665353 -6270
3 2.923244 -.001846379 -6240
3 3.041393 .11292981 -6209
3 3.079181 .08005774 -6178
3 3.113943 -.03908259 -6150
3 3.146128 -.03908259 -6119
3 3.176091 -.03908259 -6089
3 3.20412 -.03908259 -6058
3 3.230449 -.03908259 -6028
3 3.2552724 -.03908259 -5997
4 1.50515 .6003547 -6696
4 1.6434526 .57465684 -6666
4 1.763428 .55444 -6635
4 1.863323 .5614237 -6605
4 2.089905 .52935606 -6574
4 2.307496 .5112851 -6543
4 2.463893 .4620432 -6515
4 2.563481 .3269823 -6484
4 2.656098 .2680974 -6454
4 2.74351 .2365903 -6423
4 2.8247764 .1932711 -6393
4 2.893207 .19376005 -6362
4 2.9561684 .17513096 -6331
4 3.008174 .1680165 -6301
4 3.056142 .18208003 -6270
4 3.0958664 .17273673 -6240
5 .60206 .50657266 -6605
5 .90309 .50657266 -6574
5 1.1760913 .4994121 -6543
5 1.3222193 .4989994 -6515
5 1.4913617 .4921315 -6484
5 1.5797836 .4921315 -6454
5 1.662758 .4921315 -6423
5 1.748188 .4846557 -6393
5 1.80618 .4846557 -6362
5 1.845098 .4846557 -6331
5 1.8864907 .4771213 -6301
5 1.919078 .4771213 -6270
5 1.919078 .4771213 -6240
5 1.919078 .4830164 -6209
5 2.0043213 .4680518 -6178
5 2.0569048 .4024333 -6150
5 2.10721 .4024333 -6119
5 2.1522884 .3653944 -6089
5 2.1875207 .3653944 -6058
5 2.220108 .3653944 -6028
5 2.25042 .3250023 -5997
5 2.2810333 .3250023 -5966
5 2.3138673 .3250023 -5936
5 2.3598354 .3142887 -5905
5 2.3944516 .3142887 -5875
5 2.431364 .3142887 -5844
5 2.468347 .3033041 -5813
5 2.507856 .3033041 -5784
5 2.539076 .3122832 -5753
5 2.570543 .2397998 -5723
5 2.577492 .2527317 -5692
5 2.577492 .2397998 -5662
6 2.462398 .4780352 -6209
6 2.518514 .4665558 -6178
6 2.60206 .4579219 -6150
6 2.6532125 .433721 -6119
6 2.69897 .41151395 -6089
6 2.778151 .3767709 -6058
6 2.845098 .3544976 -6028
6 2.90309 .3320456 -5997

In order to make it fit in dataex I had to get rid of a lot of the data that remains and is causing confusion with the runby. I could always just drop all data that isn't a result after running runby though.

My problem, is that I'm missing the constant, it's p-value, and the p-value for the coefficient (b). Is there a way to put that back in the program section?

Also, my D-stat is 0 for all 100 of my companies. I've never had to run Durbin Watson on panel data like this before, so I'm not sure if I'm getting 0 for everyone because all of the data is autocorrelated or if there is some issue with how I'm running it.

Thanks for all your help.

[Andrew Musau]

You have monthly data, so your xtset command is not correct. This leads to an incorrect calculation of the Durbin-Watson statistic. The following code is valid for Stata 16+.

* Example generated by -dataex-. To install: ssc install dataex
clear
input long Fullid float(logcpn logthpp Time)
1 2.968016 -.07918125 -6209
1 2.9726655 -.06382149 -6150
1 3.001734 -.05305674 -6089
1 3.0216026 -.04175473 -6058
1 3.043755 -.04175473 -6028
1 3.062958 .04270675 -5997
1 3.075912 .09633057 -5966
1 3.0982976 .0934217 -5936
1 3.1169395 -.03229605 -5905
1 3.1332195 .09108047 -5875
1 3.1504495 -.05142405 -5844
1 3.159266 -.024568195 -5813
2 1.544068 .3460734 -6209
2 1.832509 .3656217 -6178
2 2.045323 .59423465 -6150
2 2.2479732 .59423465 -6119
2 2.3961995 .3767073 -6089
2 2.5037906 .20431383 -6058
2 2.591065 .20431383 -6028
2 2.668386 .1045359 -5997
2 2.731589 .1033149 -5966
2 2.7916906 .19728057 -5936
2 2.841985 .10648232 -5905
2 2.886491 .1775365 -5875
2 2.926857 .11750802 -5844
2 2.951823 .1645653 -5813
2 2.977266 .1645653 -5784
3 1.2787536 .6373078 -6484
3 1.6812413 .456642 -6454
3 1.9822712 .3004374 -6423
3 2.1731863 .20527 -6393
3 2.4785664 .10792907 -6362
3 2.6294096 .10359641 -6331
3 2.748188 .06773569 -6301
3 2.848805 .01665353 -6270
3 2.923244 -.001846379 -6240
3 3.041393 .11292981 -6209
3 3.079181 .08005774 -6178
3 3.113943 -.03908259 -6150
3 3.146128 -.03908259 -6119
3 3.176091 -.03908259 -6089
3 3.20412 -.03908259 -6058
3 3.230449 -.03908259 -6028
3 3.2552724 -.03908259 -5997
4 1.50515 .6003547 -6696
4 1.6434526 .57465684 -6666
4 1.763428 .55444 -6635
4 1.863323 .5614237 -6605
4 2.089905 .52935606 -6574
4 2.307496 .5112851 -6543
4 2.463893 .4620432 -6515
4 2.563481 .3269823 -6484
4 2.656098 .2680974 -6454
4 2.74351 .2365903 -6423
4 2.8247764 .1932711 -6393
4 2.893207 .19376005 -6362
4 2.9561684 .17513096 -6331
4 3.008174 .1680165 -6301
4 3.056142 .18208003 -6270
4 3.0958664 .17273673 -6240
5 .60206 .50657266 -6605
5 .90309 .50657266 -6574
5 1.1760913 .4994121 -6543
5 1.3222193 .4989994 -6515
5 1.4913617 .4921315 -6484
5 1.5797836 .4921315 -6454
5 1.662758 .4921315 -6423
5 1.748188 .4846557 -6393
5 1.80618 .4846557 -6362
5 1.845098 .4846557 -6331
5 1.8864907 .4771213 -6301
5 1.919078 .4771213 -6270
5 1.919078 .4771213 -6240
5 1.919078 .4830164 -6209
5 2.0043213 .4680518 -6178
5 2.0569048 .4024333 -6150
5 2.10721 .4024333 -6119
5 2.1522884 .3653944 -6089
5 2.1875207 .3653944 -6058
5 2.220108 .3653944 -6028
5 2.25042 .3250023 -5997
5 2.2810333 .3250023 -5966
5 2.3138673 .3250023 -5936
5 2.3598354 .3142887 -5905
5 2.3944516 .3142887 -5875
5 2.431364 .3142887 -5844
5 2.468347 .3033041 -5813
5 2.507856 .3033041 -5784
5 2.539076 .3122832 -5753
5 2.570543 .2397998 -5723
5 2.577492 .2527317 -5692
5 2.577492 .2397998 -5662
6 2.462398 .4780352 -6209
6 2.518514 .4665558 -6178
6 2.60206 .4579219 -6150
6 2.6532125 .433721 -6119
6 2.69897 .41151395 -6089
6 2.778151 .3767709 -6058
6 2.845098 .3544976 -6028
6 2.90309 .3320456 -5997
end

gen time= mofd(Time)
format time %tm

cap program drop my_results
program my_results
    xtset Fullid time
    regress logthpp logcpn
    gen b_logcpn=r(table)["b", "logcpn"]
    gen se_logcpn=r(table)["se", "logcpn"]
    gen pval_logcpn=r(table)["pvalue", "logcpn"]
    gen b_cons=r(table)["b", "_cons"]
    gen se_cons=r(table)["se", "_cons"]
    gen pval_cons=r(table)["pvalue", "_cons"]
    gen N = `e(N)'
    gen R=`e(r2)'
    estat dwatson
    gen D= r(dw)
    keep in 1
end

*ssc install runby
runby my_results, by(Fullid)
keep Fullid b_logcpn-D
browse

[Kate Pryce]

Andrew,

I can't tell you how many times I looked at my time variable thinking something was off but not figuring out what it was. THANK YOU.

This worked perfectly.

To make sure I'm understanding, in:
gen b_cons=r(table)["b", "_cons"] r(table) directs the storage of the data to follow in the table, and then ["b", "_cons"] announces that the data to be stored is the value of the constant? Thank you again for all your help.

[Andrew Musau]

Andrew,
To make sure I'm understanding, in:
gen b_cons=r(table)["b", "_cons"] r(table) directs the storage of the data to follow in the table, and then ["b", "_cons"] announces that the data to be stored is the value of the constant? Thank you again for all your help.

After running the regression, the code refers to the row name and column name of the _cons coefficient in the matrix r(table). Consider the following:

sysuse auto, clear
regress mpg weight disp
return list
matrix list r(table)
display r(table)["b", "_cons"]
display r(table)["se", "weight"]
display r(table)["pvalue", "displacement"]

Res.:

. sysuse auto, clear
(1978 Automobile Data)

. regress mpg weight disp

      Source |       SS           df       MS      Number of obs   =        74
-------------+----------------------------------   F(2, 71)        =     66.79
       Model |  1595.40969         2  797.704846   Prob > F        =    0.0000
    Residual |  848.049768        71  11.9443629   R-squared       =    0.6529
-------------+----------------------------------   Adj R-squared   =    0.6432
       Total |  2443.45946        73  33.4720474   Root MSE        =    3.4561

------------------------------------------------------------------------------
         mpg |      Coef.   Std. Err.      t    P>|t|     [95% Conf. Interval]
-------------+----------------------------------------------------------------
      weight |  -.0065671   .0011662    -5.63   0.000    -.0088925   -.0042417
displacement |   .0052808   .0098696     0.54   0.594    -.0143986    .0249602
       _cons |   40.08452    2.02011    19.84   0.000     36.05654    44.11251
------------------------------------------------------------------------------

. return list

scalars:
              r(level) =  95

matrices:
              r(table) :  9 x 3

. matrix list r(table)

r(table)[9,3]
              weight  displacement         _cons
     b    -.00656711     .00528078     40.084522
    se     .00116624     .00986958     2.0201103
     t    -5.6310042      .5350558      19.84274
pvalue     3.351e-07     .59428314     1.106e-30
    ll    -.00889252     -.0143986     36.056537
    ul    -.00424169     .02496015     44.112508
    df            71            71            71
  crit     1.9939434     1.9939434     1.9939434
 eform             0             0             0

. display r(table)["b", "_cons"]
40.084522

. display r(table)["se", "weight"]
.00116624

. display r(table)["pvalue", "displacement"]
.59428314

[Kate Pryce]

Thank you! That makes a lot of sense.