How to export unit root test results to excel?

[Santosh Dash]

Hello,
We have estout/esttab, outreg2 commands to exports regression output.
Is it possible to export unit root test results using these packages?
If yes, what is the code? I have a panel dataset and I'm testing the ADF test for each country.

dfuller gcf, drift lags(4), if id==1
dfuller gcf, drift lags(4), if id==2
esttab using example1.csv, replace

Thanking you in advance.

[Andrew Musau]

dfuller does not leave behind a results matrix, but you can write your own program to do this. The following is based on runby (SSC), written by Robert Picard and Clyde Schechter.

program my_results
tsset t
dfuller gcf, drift lags(4)
gen N = `r(N)'
gen Zt=`r(Zt)'
gen p=`r(p)'
gen cv1=`r(cv1)'
gen cv5= `r(cv5)'
gen cv10= `r(cv10)'
end
 
*ssc install runby
runby my_results, by(id) status
contract id N Zt p cv1 cv5 cv10
drop _freq
export excel my_results, firstrow(variables)

[Santosh Dash]

Dear Andrew,
Thank you for your help.
I tried with your code.
However, in the code

runby my_results, by(id) status

no output is coming.

Then when I ran

contract id N Zt p cv1 cv5 cv10

Stata reply is "no variables defined"

Can you kindly figure out why it does not work...

[Andrew Musau]

Copy and paste

dataex in 1/20

for specific advice.

[Santosh Dash]

Thank you for your reply. However, I do not know where to fit your second code in your first code. I tried but the problem persists. I would really appreciate if you can kindly write the code again. Thank you.

[Andrew Musau]

#4 is a request for a data example. Here, I use some observations from the Stata dataset air2.dta, modified to include a panel identifier (id), to illustrate the procedure.

*DATA SET
webuse air2
keep in 1/36
gen id= cond(_n<13, 1,cond(inrange(_n, 13, 24), 2, 3))
dataex

The data set I create and the procedure is as follows:

* Example generated by -dataex-. To install: ssc install dataex
clear
input int air float(time t id)
112      1949  1 1
118 1949.0834  2 1
132 1949.1666  3 1
129   1949.25  4 1
121 1949.3334  5 1
135 1949.4166  6 1
148    1949.5  7 1
148 1949.5834  8 1
136 1949.6666  9 1
119   1949.75 10 1
104 1949.8334 11 1
118 1949.9166 12 1
115      1950 13 2
126 1950.0834 14 2
141 1950.1666 15 2
135   1950.25 16 2
125 1950.3334 17 2
149 1950.4166 18 2
170    1950.5 19 2
170 1950.5834 20 2
158 1950.6666 21 2
133   1950.75 22 2
114 1950.8334 23 2
140 1950.9166 24 2
145      1951 25 3
150 1951.0834 26 3
178 1951.1666 27 3
163   1951.25 28 3
172 1951.3334 29 3
178 1951.4166 30 3
199    1951.5 31 3
199 1951.5834 32 3
184 1951.6666 33 3
162   1951.75 34 3
146 1951.8334 35 3
166 1951.9166 36 3
end

program my_results
tsset t
    dfuller air, drift lags(4)
    gen N = `r(N)' 
    gen Zt=`r(Zt)'
    gen p=`r(p)'
    gen cv1=`r(cv1)'
    gen cv5= `r(cv5)'
    gen cv10= `r(cv10)'
end

runby my_results, by(id) status
contract id N Zt p cv1 cv5 cv10
drop _freq
export excel my_results, firstrow(variables)

The regression outputs should look like below:

*id=1

Augmented Dickey-Fuller test for unit root         Number of obs   =         7

                               ----------- Z(t) has t-distribution -----------
                  Test         1% Critical       5% Critical      10% Critical
               Statistic           Value             Value             Value
------------------------------------------------------------------------------
 Z(t)             -3.482           -31.821            -6.314            -3.078
------------------------------------------------------------------------------
p-value for Z(t) = 0.0890


*id=2

Augmented Dickey-Fuller test for unit root         Number of obs   =         7

                               ----------- Z(t) has t-distribution -----------
                  Test         1% Critical       5% Critical      10% Critical
               Statistic           Value             Value             Value
------------------------------------------------------------------------------
 Z(t)             -4.800           -31.821            -6.314            -3.078
------------------------------------------------------------------------------
p-value for Z(t) = 0.0654


*id=3

Augmented Dickey-Fuller test for unit root         Number of obs   =         7

                               ----------- Z(t) has t-distribution -----------
                  Test         1% Critical       5% Critical      10% Critical
               Statistic           Value             Value             Value
------------------------------------------------------------------------------
 Z(t)             -2.300           -31.821            -6.314            -3.078
------------------------------------------------------------------------------
p-value for Z(t) = 0.1306

And your output exported to Excel should look as follows:

. l

     +-------------------------------------------------------------------+
     | id   N          Zt          p         cv1         cv5        cv10 |
     |-------------------------------------------------------------------|
  1. |  1   7   -3.482305   .0890126   -31.82052   -6.313752   -3.077683 |
  2. |  2   7    -4.80034   .0653749   -31.82052   -6.313752   -3.077683 |
  3. |  3   7   -2.299773   .1305591   -31.82052   -6.313752   -3.077683 |
     +-------------------------------------------------------------------+

[Santosh Dash]

Dear Sir,
Thank you very much for offering to help. I tried that code. When i ran
runby my_results, by(id) status data vanishes from datasheet. I have attached a snapshot.

Kindly help.

[Santosh Dash]

Also, I would request you to kindly consider assigning asterisks (***, **, *) to Zt based on P-values.

[Andrew Musau]

Please review the FAQs by clicking on the link in the top left hand side of this page and re-read my post in #4. If for some reason you are unable to provide a data example using dataex, I am sorry, I cannot be of more help.

[Santosh Dash]

Dear Sir,
My apologies for my failure to understand #4. Hence, I could not provide data. However, in your reply #6, you had given the data and example. I was trying to replicate your example.

All data vanished and no output came when "status" is added in
runby my_results, by(id) status

That shows an error message as shown in the image in #7.

However, when I dropped "status" and instead ran the following:
runby my_results, by(id) verbose

only unit root test results came and results were not stored.

I read the about "ruby" command and am still not able to fix it.



Thank you.

[Andrew Musau]

The code in #6 is tested. This means that it runs without errors and that it creates an Excel file with the compiled results. Now, if you want me to figure out why it does not work in your case, I referred you to the FAQs, which include advice on how to show this. There is evidence from this thread that you know how to use CODE delimiters to show Stata code/ results. Therefore, following the FAQ advice, you have to copy and paste all the commands that you entered and the resulting Stata output and present this within the CODE delimiters. To make it easier, I will present the entire code in one and include an additional list command.

webuse air2
keep in 1/36
gen id= cond(_n<13, 1,cond(inrange(_n, 13, 24), 2, 3))
program my_results
tsset t
    dfuller air, drift lags(4)
    gen N = `r(N)' 
    gen Zt=`r(Zt)'
    gen p=`r(p)'
    gen cv1=`r(cv1)'
    gen cv5= `r(cv5)'
    gen cv10= `r(cv10)'
end
runby my_results, by(id) status
contract id N Zt p cv1 cv5 cv10
drop _freq
export excel my_results, firstrow(variables)
list

[Santosh Dash]

Dear Sir,
I have added a dataex.

clear all
input int id year gcf
1    1996    19.6239
1    1997    20.8261
1    1998    20.9708
1    1999    17.8629
1    2000    17.5335
1    2001    15.626
1    2002    10.8539
1    2003    14.147
1    2004    17.5513
1    2005    18.8883
1    2006    18.6805
1    2007    20.0991
1    2008    19.5728
1    2009    16.0531
1    2010    17.7055
1    2011    18.3985
1    2012    16.502
1    2013    17.3058
1    2014    17.2629
1    2015    17.0707
1    2016    17.027
2    1996    24.8513
2    1997    24.9167
2    1998    25.691
2    1999    26.2287
2    2000    26.32
2    2001    23.4898
2    2002    24.5032
2    2003    26.0271
2    2004    27.1452
2    2005    27.526
2    2006    27.5974
2    2007    27.5122
2    2008    28.6065
2    2009    27.3251
2    2010    26.7663
2    2011    26.4142
2    2012    27.6497
2    2013    27.8095
2    2014    26.6506
2    2015    26.1433
2    2016    25.2955
3    1996    26.3377
3    1997    26.2137
3    1998    26.1057
3    1999    26.1176
3    2000    25.927
3    2001    25.1608
3    2002    23.6427
3    2003    24.4087
3    2004    24.061
3    2005    23.8252
3    2006    23.6103
3    2007    24.5843
3    2008    24.4675
3    2009    22.7753
3    2010    22.6078
3    2011    24.1405
3    2012    23.9771
3    2013    23.7234
3    2014    23.5189
3    2015    23.5706
3    2016    24.1181
4    1996    8.20981
4    1997    6.09685
4    1998    6.77161
4    1999    5.88494
4    2000    2.78114
4    2001    4.46826
4    2002    3.94866
4    2003    7.86879
4    2004    10.3471
4    2005    18.2
4    2006    18.513
4    2007    15.1865
4    2008    13.409
4    2009    18.9749
4    2010    17.1604
4    2011    16.3457
4    2012    16.3243
4    2013    14.6199
4    2014    14.2
4    2015    12.1776
4    2016    9.19197
5    1996    21.6706
5    1997    22.2953
5    1998    22.4165
5    1999    22.7127
5    2000    23.7818
5    2001    22.5921
5    2002    20.6646
5    2003    20.8607
5    2004    22.4948
5    2005    23.6093
5    2006    23.8842
5    2007    24.4474
5    2008    25.7305
5    2009    21.6644
5    2010    22.6921
5    2011    23.9901
5    2012    23.2045
5    2013    22.1734
5    2014    23.3153
5    2015    23.5778
5    2016    23.9516
end

Kindly help.

[Santosh Dash]

I saw your reply before posting #12. As you mentioned, the code in #6 is tested. However, when I was running the code in #6, it was not giving the output you have shown in #6.
I again ran the whole code in #11. Again the problem persists in
runby my_results, by(id) status

Kindly see the snapshot.


Just wondering... does runby package require Stata 15?

I am extremely sorry to bother you so much.
Thank you.

[Andrew Musau]

Kindly see the snapshot

12.5 Posting attachments: please don't...

There are several "please don't" requests here, but good reasons for them all.

Please do not post .gph files, as they can't be read without flipping back and forth between Stata and the forum software, thus making your posts much more difficult to follow.

In particular, please do not post screenshots. Many members will not be able to read them at all; they usually can't be read easily; and they do not allow copy and paste of data or code, which is highly desirable to allow experienced members to make precise suggestions for your questions.

Just wondering... does runby package require Stata 15?

11. What should I say about the version of Stata I use?

The current version of Stata is 15.1. Please specify if you are using an earlier version; otherwise, the answer to your question may refer to commands or features unavailable to you. Moreover, as bug fixes and new features are issued frequently by StataCorp, make sure that you update your Stata before posting a query, as your problem may already have been solved.

I do not know what is so difficult about following the advice in #11. Look at how I present the result in its entirety below.

. do "C:\Users\709554\AppData\Local\Temp\STD37d8_000000.tmp"

. webuse air2
(TIMESLAB: Airline passengers)

. keep in 1/36
(108 observations deleted)

. gen id= cond(_n<13, 1,cond(inrange(_n, 13, 24), 2, 3))

. program my_results
  1. tsset t
  2.     dfuller air, drift lags(4)
  3.     gen N = `r(N)'
  4.     gen Zt=`r(Zt)'
  5.     gen p=`r(p)'
  6.     gen cv1=`r(cv1)'
  7.     gen cv5= `r(cv5)'
  8.     gen cv10= `r(cv10)'
  9. end

. runby my_results, by(id) status

  elapsed ----------- by-groups ----------    ------- observations ------       time
     time      count     errors    no-data        processed         saved  remaining
------------------------------------------------------------------------------------
 00:00:01          3          0          0               36            36   00:00:00

--------------------------------------
Number of by-groups    =             3
by-groups with errors  =             0
by-groups with no data =             0
Observations processed =            36
Observations saved     =            36
--------------------------------------

. contract id N Zt p cv1 cv5 cv10

. drop _freq

. export excel my_results, firstrow(variables)
file my_results.xls saved

. list

     +-------------------------------------------------------------------+
     | id   N          Zt          p         cv1         cv5        cv10 |
     |-------------------------------------------------------------------|
  1. |  1   7   -3.482305   .0890126   -31.82052   -6.313752   -3.077683 |
  2. |  2   7    -4.80034   .0653749   -31.82052   -6.313752   -3.077683 |
  3. |  3   7   -2.299773   .1305591   -31.82052   -6.313752   -3.077683 |
     +-------------------------------------------------------------------+

.
end of do-file

The data in #12 is fine, but you must first resolve the error that you receive before you proceed.

[Robert Picard]

It's worth repeating Andrew's advice to follow the FAQ and this is a case where the version used matters. In version 14, the dfuller help file shows what the command returns:

Stored results

    dfuller stores the following in r():

    Scalars   
      r(N)           number of observations
      r(lags)        number of lagged differences
      r(Zt)          Dickey-Fuller test statistic
      r(p)           MacKinnon approximate p-value (if there is a constant or trend in associated regression)

In Stata 15, the same help file shows:

Stored results

    dfuller stores the following in r():

    Scalars   
      r(N)           number of observations
      r(lags)        number of lagged differences
      r(Zt)          Dickey-Fuller test statistic
      r(cv1)         1% critical value
      r(cv5)         5% critical value
      r(cv10)        10% critical value
      r(p)           MacKinnon approximate p-value (if there is a constant or trend in associated regression)

So Andrew's code works and does not generate an error when run on Stata 15. The same code will throw an error if run on Stata 14, which is what Santosh was clumsily reporting in #7 and #13.

If I save the data example in #12 as "statalist_ex.dta", then the following will work in both Stata 14 and 15:

version 14
clear all
program my_results
    tsset year
    dfuller gcf, drift lags(4)
    gen N = `r(N)' 
    gen Zt=`r(Zt)'
    gen p=`r(p)'
    keep in 1
end

use "statalist_ex.dta", clear
runby my_results, by(id)
list

Note that there's no need to carry over all observations when the my_results program terminates at the end of a by-group so I keep only one observation. This removes the need to call contract to prune the data back to one observation per id. Here are the results (from Stata 14):

. version 14

. clear all

. program my_results
  1.         tsset year
  2.     dfuller gcf, drift lags(4)
  3.     gen N = `r(N)' 
  4.     gen Zt=`r(Zt)'
  5.     gen p=`r(p)'
  6.         keep in 1
  7. end

. 
. use "statalist_ex.dta", clear

. runby my_results, by(id)

--------------------------------------
Number of by-groups    =             5
by-groups with errors  =             0
by-groups with no data =             0
Observations processed =           105
Observations saved     =             5
--------------------------------------

. list

     +-------------------------------------------------+
     | id   year       gcf    N          Zt          p |
     |-------------------------------------------------|
  1. |  1   1996   19.6239   16   -3.397825   .0033979 |
  2. |  2   1996   24.8513   16   -1.120538   .1443368 |
  3. |  3   1996   26.3377   16   -3.051892   .0061061 |
  4. |  4   1996   8.20981   16   -2.194499   .0264658 |
  5. |  5   1996   21.6706   16   -1.735727   .0566317 |
     +-------------------------------------------------+

.