Announcement

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

  • Esttab or Estout to output (in quality tables) the results of several factor analysis from a loop

    Hello Everyone,

    I am using Stata 14.2/IC and running a factor analysis with the same variables from 15 cycles of data collection. I know that the user written program
    estout
    by Ben Jenn (available at ssc) has the capacity to output results from factor analysis in several different formats. However I haven't been able to find examples of how to output results from several factor analysis into EXCEL sheets.
    I have the following loop to conduct the factor analysis

    Code:
    levelsof Cycle, local(Cycles)
    
    foreach Cycle of local Cycles {
    factor m1r m13r m16r m19r m23r m2r m7r ///
    m11r m18r m22r m26r m29r m3r m4r m8r m9r m12r m14r m25r m5r m10r m17r m20r ///
    m24r m27r m6r m15r m21r m28r m30r if Cycle == "`Cycle'",factors(5)
      }

    and I am trying to use the following code to output the results in EXCEL
    Code:
    esttab using "excel.csv", ///
    cells("L[Factor1](t) L[Factor2](t) L[Factor3](t) L[Factor4](t) L[Factor5](t) Psi[Uniqueness]") ///
    nogap noobs nonumber nomtitle
    However, this code only outputs the results of one data collection cycle, and I want the results of every data collection cycle outputted in different sheets of the same EXCEL document.

    Do you folks have any recommendations on where I can find an example code as a guide?

    Thank you
    Patrick

  • #2
    You should enter the esttab inside the loop to have all the results appended one below the other.
    Code:
    est clear
    levelsof Cycle, local(Cycles)
    foreach Cycle of local Cycles {
    factor m1r m13r m16r m19r m23r m2r m7r ///
    m11r m18r m22r m26r m29r m3r m4r m8r m9r m12r m14r m25r m5r m10r m17r m20r ///
    m24r m27r m6r m15r m21r m28r m30r if Cycle == "`Cycle'",factors(5)
    esttab using "excel.csv", ///
    cells("L[Factor1](t) L[Factor2](t) L[Factor3](t) L[Factor4](t) L[Factor5](t) Psi[Uniqueness]") ///
    nogap noobs nonumber nomtitle append  
    
      }

    Comment


    • #3
      Thank you for your reply Oded. I did try what you have but I found two strange things in my output.
      1) As far as I know factor analysis uses listwise deletion but the N from factor analysis is not matching the expected N from using
      count if !missing (m1r, m2r...m30r)
      there are several factor analysis that have 2 or 3 cases fewer than the expected count. Any suggestions on how to identify why this is happening?

      2) When the output is saved to the EXCEL document it doesn't do it in order by cycle (i.e. first output is cycle 1, but then the next one is cycle 10 instead of 2, the data is sorted by cycle so I am not exactly sure why that's happening).

      Code:
      * code that i run
      est clear
      levelsof Cycle, local(Cycles)
      foreach Cycle of local Cycles {
      factor m1r m13r m16r m19r m23r m2r m7r ///
      m11r m18r m22r m26r m29r m3r m4r m8r m9r m12r m14r m25r m5r m10r m17r m20r ///
      m24r m27r m6r m15r m21r m28r m30r if Cycle == "`Cycle'",factors(5)
      esttab using "excel1.csv", /// 
      label ///
      cells("L[Factor1](t) L[Factor2](t) L[Factor3](t) L[Factor4](t) L[Factor5](t) Psi[Uniqueness]") ///
      mtitles("Cycle 1" "Cycle 2" "Cycle 3" "Cycle 4" "Cycle 5" "Cycle 6" ///
      "Cycle 7" "Cycle 8" "Cycle 9" "Cycle 10" "Cycle 11" "Cycle 12" ///
      "Cycle 13" "Cycle 14" "Cycle 15") append
      
        }
      CODE]
      *code that is outputted in Stata's results window
      . est clear

      . levelsof Cycle, local(Cycles)
      `"Cycle 1"' `"Cycle 10"' `"Cycle 11"' `"Cycle 12"' `"Cycle 13"' `"Cycle 14"' `"Cycle 15"' `"Cycle 2"' `"Cycle 3"' `"Cycle 4"' `"Cycle 5"' `"Cycle 6"' `"Cycle
      > 7"' `"Cycle 8"' `"Cycle 9"'

      . foreach Cycle of local Cycles {
      2. factor m1r m13r m16r m19r m23r m2r m7r ///
      > m11r m18r m22r m26r m29r m3r m4r m8r m9r m12r m14r m25r m5r m10r m17r m20r ///
      > m24r m27r m6r m15r m21r m28r m30r if Cycle == "`Cycle'",factors(5)
      3. esttab using "excel1.csv", ///
      > label ///
      > cells("L[Factor1](t) L[Factor2](t) L[Factor3](t) L[Factor4](t) L[Factor5](t) Psi[Uniqueness]") ///
      > mtitles("Cycle 1" "Cycle 2" "Cycle 3" "Cycle 4" "Cycle 5" "Cycle 6" ///
      > "Cycle 7" "Cycle 8" "Cycle 9" "Cycle 10" "Cycle 11" "Cycle 12" ///
      > "Cycle 13" "Cycle 14" "Cycle 15") append
      4.
      . }
      [/CODE]

      Thank you
      Patrick

      Comment


      • #4
        Patrick please follow the FAQ and post some data (using dataex) to get better help. As for the order of the cycles (question 2) that is because you are using string expressions. I suggest to use the following code to have the tables in ascending order.
        Code:
        est clear
        forval i=1/15 {
        factor m1r m13r m16r m19r m23r m2r m7r ///
        m11r m18r m22r m26r m29r m3r m4r m8r m9r m12r m14r m25r m5r m10r m17r m20r ///
        m24r m27r m6r m15r m21r m28r m30r if Cycle=="Cycle `i'",factors(5)
        esttab using "excel.csv", ///
        cells("L[Factor1](t) L[Factor2](t) L[Factor3](t) L[Factor4](t) L[Factor5](t) Psi[Uniqueness]") ///
        nogap noobs nonumber nomtitle mtitles("Cycle `i'") append  
        }

        Comment


        • #5
          Hello Oded,

          Thank you for your recommendations (I used your suggestion to solve question 2). Unfortunately I don't think posting some data will assist with question 1, I have 10000 observations...a sample data won't be sufficient to examine this inconsistency. Since the difference in sample size is below 5 for each cycle, I will not pursue another explanation for that.
          However, I do have a 3rd question I can't seem to find information on how to add eigen values in the tables, and results from promax rotation. Is that impossible with esttout? Should I be looking at another command?

          Thank you again
          Patrick

          Comment


          • #6
            Patrick check the help file of the mentioned commands (factor & rotate), go to end of the help file and see how they store the results (e-class or r-class), then you'll be able to add these to your esttab command.

            Comment


            • #7
              Dear Oded,

              I appreciate all your suggestions and recommendations I have been able to learn a few things through this process. I hope this will be my last question about this process. Below is the most recent code that I have been able to figure out.
              It works perfectly except for the eigen values. It only ouptus the eigen value for the first factor and it doesn't place it under the column of the the first factor (see screen shot below, row 4 2nd column, first I don't want to have column 2, and I want the information for row 4 to be under their corresponding factors). Reading this link didn't help me much as I am a little unfamiliar with some of the language used. Do you have any other recommendations? Thank you

              Code:
              est clear
              forval i=1/15 {
              factor m1r-m30r if Cycle=="Cycle `i'",factors(5)
              rotate, promax
              esttab using "excel4.csv", label ///
              cells("Ev(transpose) Psi[Uniqueness] L[1](t label(Factor 1)) L[2](t label(Factor 2)) L[3](t label(Factor 3)) L[4](t label(Factor 4)) L[5](t label(Factor 5)) r_L[1](t label(Rotated Factor 1)) r_L[2](t label(Rotated Factor 2)) r_L[3](t label(Rotated Factor 3))r_L[4](t label(Rotated Factor 4)) r_L[5](t label(Rotated Factor 5))") ///
              mtitles("Cycle `i'") append  
              }
              Click image for larger version

Name:	Screen Shot 2016-12-19 at 1.37.51 PM.png
Views:	1
Size:	101.0 KB
ID:	1368169

              Comment


              • #8
                To achieve that you'll need to access the e(Ev) matrix results and rename the factors to get the names of the variables. I guess it is possible, but I don't have the time to look into. However, You can append the results in separate lines before the table and then just copy and paste down:
                Code:
                est clear
                forval i=1/15 {
                factor m1r-m30r if Cycle=="Cycle `i'",factors(5)
                rotate, promax
                esttab using "excel4.csv", label ///
                cells("Ev[Eigenvalues] Psi[Uniqueness] L[1](t label(Factor 1)) L[2](t label(Factor 2)) L[3](t label(Factor 3)) L[4](t label(Factor 4)) L[5](t label(Factor 5)) r_L[1](t label(Rotated Factor 1)) r_L[2](t label(Rotated Factor 2)) r_L[3](t label(Rotated Factor 3))r_L[4](t label(Rotated Factor 4)) r_L[5](t label(Rotated Factor 5))") ///
                mtitles("Cycle `i'") append  
                }

                Comment

                Working...
                X