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

[Patrick Abi Nader]

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

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

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

[Oded Mcdossi]

You should enter the esttab inside the loop to have all the results appended one below the other.

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  

  }

[Patrick Abi Nader]

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 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

[Oded Mcdossi]

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.

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  
}

[Patrick Abi Nader]

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

[Oded Mcdossi]

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.

[Patrick Abi Nader]

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

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  
}

[Oded Mcdossi]

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:

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  
}