Store and export p values from pwcorr in a loop

[SL Navarro]

I am running correlations for ~3,000 variables. The nuance of the usefulness of p values versus confidence intervals aside, I want to get the p values as a filtering method to include those variables with a p value <0.1 for further evaluation in more complex models. I know that the p value is not stored in the scalars and have read the various hacks to get it to export but cannot seem to get something that works. My code below runs and returns the R2 coefficients, but leaves blank the column for p values. Is there a way to get the p values to export with the correlation values?

tempfile results
tempname fh

postfile `fh' str32 metabolite rho p using "`results'"

foreach var of varlist pyruvate - methioninesulfoxide { 

    pwcorr `var' bmi,sig
    post `fh' ( "`var''" ) ( r(rho)^2 ) ( r(p) )  
}

postclose `fh'

use `results', clear

[William Lisowski]

The matrix of p-values is stored in the Stata matrix r(sig), so this modifies your existing code

    matrix sig = r(sig)
    post `fh' ( "`var'" ) ( r(rho)^2 ) ( sig["bmi","`var'"] )

This demonstrates a slightly different approach.

sysuse auto, clear
tempfile results
tempname fh

postfile `fh' str32 varname rho p using "`results'"

foreach var of varlist length gear_ratio { 

    pwcorr `var' price,sig
    matrix sig = r(sig)
    post `fh' ( "`var'" ) ( r(rho)^2 ) ( sig["price","`var'"] )  
}

postclose `fh'

use `results', clear
list

. pwcorr price `vars', sig

             |    price      mpg   length gear_r~o
-------------+------------------------------------
       price |   1.0000 
             |
             |
         mpg |  -0.4686   1.0000 
             |   0.0000
             |
      length |   0.4318  -0.7958   1.0000 
             |   0.0001   0.0000
             |
  gear_ratio |  -0.3137   0.6162  -0.6964   1.0000 
             |   0.0065   0.0000   0.0000
             |

. matrix r = r(C)

. matrix sig = r(sig)

. 
. foreach var of varlist `vars' { 
  2.     post `fh' ( "`var'" ) ( r["price","`var'"]^2 ) ( sig["price","`var'"] )  
  3. }

. 
. postclose `fh'

. 
. use `results', clear

. list

     +----------------------------------+
     |    varname        rho          p |
     |----------------------------------|
  1. |        mpg   .2195829   .0000255 |
  2. |     length   .1864782   .0001222 |
  3. | gear_ratio   .0983833   .0065022 |
     +----------------------------------+

[SL Navarro]

Exactly what I needed, thanks so much, William!

[William Lisowski]

I now realize that I posted the wrong source in post #2 for the "slightly different approach". That was the source I used to test the change to the original code in post #1 - lacking any example data to test with. The code for the slightly different approach was

sysuse auto, clear
tempfile results
tempname fh

postfile `fh' str32 varname rho p using "`results'"
local vars mpg length gear_ratio
pwcorr price `vars', sig

matrix r = r(C)
matrix sig = r(sig)

foreach var of varlist `vars' { 
    post `fh' ( "`var'" ) ( r["price","`var'"]^2 ) ( sig["price","`var'"] )  
}

postclose `fh'

use `results', clear
list