Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Exporting multiple VIFs to excel

    Dear Statalisters,

    I am running a large amount of regressions analyses (specifically 4 models separately for 39 countries). I am looking to export VIF-statistics from each model to excel for the purpose of creating a table reporting VIFs. Each row should represent a country and each column a predictor variable.
    I am only concerned with the VIFs of my primary predictor variables (i.e. not VIFs of my control variables).

    This is the code I've been using so far:

    Code:
    forvalues i = 1(1)39 {
    
    putexcel set myexcelsheet.xls, sheet(VIF) modify
    
    quietly: reg y control1 control2 predictor1 predictor2 predictor3 if country==`i'
    
    estat vif
    putexcel A`i'=`r(vif_3)'
    putexcel B`i'=`r(vif_4)'
    putexcel C`i'=`r(vif_5)'
    }
    What I thought was that
    Code:
     r(vif_3)
    would represent the third independent variable in my model. However, the problem is that
    Code:
    estat vif
    orders the VIF-statistics from highest to lowest. So effectively I only get the three lowest VIF-statistics, regardless of which variable they represent.
    Just taking all of them is not an option either as again i wouldn't know which VIF-statistic represent which variable.

    Essentially what I am asking is whether anyone has an idea for a solution that would allow me to only put VIF-statistics for specific variables to excel.

    Thank you for your help.
    Last edited by Emil Smith; 12 Nov 2019, 04:41.

  • #2
    Well, that was an interesting problem! I think the following code may point you in a useful direction.
    Code:
    sysuse auto, clear
    regress price weight length trunk mpg
    estat vif
    return list
    forvalues i=1/4 {
        local vif_`r(name_`i')' `r(vif_`i')'
    }
    macro list _vif_weight _vif_length _vif_trunk _vif_mpg
    Code:
    . return list
    
    scalars:
                  r(vif_4) =  2.129926681518555
                  r(vif_3) =  2.955591678619385
                  r(vif_2) =  10.36373710632324
                  r(vif_1) =  11.34435081481934
    
    macros:
                 r(name_4) : "trunk"
                 r(name_3) : "mpg"
                 r(name_2) : "weight"
                 r(name_1) : "length"
    
    . forvalues i=1/4 {
      2.     local vif_`r(name_`i')' `r(vif_`i')'
      3. }
    
    . macro list _vif_weight _vif_length _vif_trunk _vif_mpg
    _vif_weight:    10.36373710632324
    _vif_length:    11.34435081481934
    _vif_trunk:     2.129926681518555
    _vif_mpg:       2.955591678619385
    
    .
    Last edited by William Lisowski; 12 Nov 2019, 08:43.

    Comment


    • #3
      Thank you so much!

      This works as far as the point where i am exporting to excel:

      Code:
      sysuse auto, clear
      regress price weight length trunk mpg
      estat vif
      return list
      forvalues i=1/4 {
          local vif_`r(name_`i')' `r(vif_`i')'
      }
      macro list _vif_weight _vif_length _vif_trunk _vif_mpg
      
      putexcel set auto.xls, sheet(VIF) modify
      
      putexcel A1=(r(_vif_weight))
      putexcel B1=(r(_vif_length))
      putexcel C1=(r(_vif_trunk))
      putexcel D1=(r(_vif_mpg))
      My excel sheet is completely blank after i run this code. That is, no error is returned and i get the following output:
      Code:
      . putexcel set auto.xls, sheet(VIF) modify
      
      .
      . putexcel A1=(r(_vif_weight))
      file auto.xls saved
      
      . putexcel B1=(r(_vif_length))
      file auto.xls saved
      
      . putexcel C1=(r(_vif_trunk))
      file auto.xls saved
      
      . putexcel D1=(r(_vif_mpg))
      file auto.xls saved
      But my excel sheet remanins blank. I found a similar problem here. I tried the solution posted on there:

      Code:
      sysuse auto, clear
      regress price weight length trunk mpg
      estat vif
      return list
      forvalues i=1/4 {
          local vif_`r(name_`i')' `r(vif_`i')'
      }
      macro list _vif_weight _vif_length _vif_trunk _vif_mpg
      
      putexcel set auto.xls, sheet(VIF) modify
      
      putexcel A1=(r(_vif_weight))
      putexcel B1=(r(_vif_length))
      putexcel C1=(r(_vif_trunk))
      putexcel D1=(r(_vif_mpg))
      
      putexcel close
      display "`c(current_date)' `c(current_time)'"
      pwd
      dir "`c(pwd)'/auto.xls"
      import excel "auto.xls", clear
      list
      But the excel sheet remains blank:

      Code:
      . putexcel close
      
      . display "`c(current_date)' `c(current_time)'"
      13 Nov 2019 09:18:06
      
      . pwd
      C:\Users\au457354\Desktop
      
      . dir "`c(pwd)'/auto.xls"
         6.5k  11/13/19  9:18  auto.xls          
      
      . import excel "auto.xls", clear
      
      . list
      
           +---------------+
           | A   B   C   D |
           |---------------|
        1. | .   .   .   . |
           +---------------+

      Comment


      • #4
        The code in post #2 creates local macros vif_weight etc. which are what you need to give to putexcel. The r(vif_weight) etc. do not exist and thus become missing values.

        Comment


        • #5
          I am again at my computer.
          Code:
          . macro list _vif_weight _vif_length _vif_trunk _vif_mpg
          _vif_weight:    10.36373710632324
          _vif_length:    11.34435081481934
          _vif_trunk:     2.129926681518555
          _vif_mpg:       2.955591678619385
          
          . 
          . putexcel set auto.xls, sheet(VIF) modify
          
          . 
          . putexcel A1=`vif_weight'
          file auto.xls saved
          
          . putexcel B1=`vif_length'
          file auto.xls saved
          
          . putexcel C1=`vif_trunk'
          file auto.xls saved
          
          . putexcel D1=`vif_mpg'
          file auto.xls saved
          
          . 
          . putexcel close
          
          . display "`c(current_date)' `c(current_time)'"
          13 Nov 2019 08:48:47
          
          . pwd
          /Users/lisowskiw/Research/PSID 1901 ARC/190909 Paper 1/191010 Q123 191010
          
          . dir "`c(pwd)'/auto.xls"
          
          -rw-r--r--  1 lisowskiw  staff  6656 Nov 13 08:48 /Users/lisowskiw/Desktop/auto.xls
          
          . import excel "auto.xls", clear
          (4 vars, 1 obs)
          
          . list
          
               +-----------------------------------------------+
               |         A           B           C           D |
               |-----------------------------------------------|
            1. | 10.363737   11.344351   2.1299267   2.9555917 |
               +-----------------------------------------------+
          
          .

          Comment


          • #6
            Of course! Thanks again - works just fine now.

            Comment


            • #7
              Hi I am new to stata
              I had run this regression

              reg log_daystoreject_ctnf i.filing_year i.uspc_encoded centered_mnovelty_1_sub c.female_proportion95 c.predicted_fem_score c.predicted_male_score

              then
              estat vif

              I would like to store the vif results to excel.
              Can anyone help me with this?

              Comment

              Working...
              X