Rotated Factor Matrix - Export with putexcel Question

[Alex Schmidt]

Hi there,
I'm currently trying to export rotated factor loading matrices with putexcel (Stata 14.1), but I can't get two little things to work. I hope that someone of you can help me out here:

My code looks like this and I basically want to recreate the stata output table ("Rotated factor loadings (pattern matrix) and unique variances"):

factor $variables, factor(5)
rotate

putexcel A1=(e(title)) B2=matrix(e(r_L)) , names
putexcel H3=matrix(e(Psi))

However, this returns the uniqueness matrix in column form, but not as fifth column behind the factor loadings as in the output table. Is there any way to rotate the e(Psi) matrix 90 degrees and append it to the first matrix?

Second, I wish it would show the labes and not variable names. Is there any way to change that as well?

Thank you as always for your support,

best
Alex

[daniel klein]

See rotate2 (SSC).

factor $variables , factor(5)
rotate2 , uniqueness matrix(mymat)
putexcel ... = mymat

For a very similar thing that I stumbled over too late, also see sortl (Enzmann, SSC).

Best
Daniel

[Alex Schmidt]

Wow, thank you so much Daniel! That's exactly what I was looking for. Works perfectly

[Alex Schmidt]

Is it possible to combine the rotate2 / putexcel command with an if-statement formatting? I would like to grey out small factor loadings in the generated excel tables. Instead of doing it manually, i was wondering if there was any code for that as the usual if-statement didn't work for me.

As always, thanks in advance for your support, guys

[daniel klein]

I have not used putexcel yet but the general answer to your question is: no.

If you want this, you need to program it. You need to identify the cells in the matrix returned by rotate2 that have (absolute) values below what you consider large enough. You can do this looping over the cells of the matrix (alternatively use Mata). Then you need to map those cells to their corresponding Excel representation (e.g. A1, B52, ...) and use putexcel cellrange , font(...).

Best
Daniel

[Alex Schmidt]

Thanks again for the helpful reply, daniel klein! Too bad that there isn't already something out there.