Exporting Multiple Corr and Sum Results Efficiently

[Nitsan Machlis]

I am new to Stata, and have over 50 corr and sum mean results in my current project (each with only two variables) with varied and inconsistent conditions that I want to export into a single Excel table. Here is an example of three:

corr nifsal shaked if migzar=="Jewish" & sug_pikuah=="a"
corr nifsal shaked if migzar=="Christian" & notzri==0
corr nifsal shaked if migzar=="Muslim" & notzri==0 & migdar=="mixed"

My two tables, one for corr and one for sum means, would ideally have 4 columns, "Variable 1", "Variable 2", "Conditions", "Result". There are only two variables so there is no need to collect the whole corr matrix, only one scalar.
I can't seem to find an efficient way to export this data, without individually collecting each result as a loop can't loop over inconsistent conditions. I am also having difficulty collecting and exporting the corr results without collecting the entire matrix. Any advice? Thanks.

[Andrew Musau]

Using frames is one way. Here is an example:

sysuse auto, clear
frame create wanted
frame wanted{
    set obs 100
    gen Variable1= ""
    gen Variable2= ""
    gen Conditions= ""
    gen Result=.
}
*LIST CONDITIONS
local condition1 regexm(make, "^AMC") & !foreign
local condition2 rep78==2 & inrange(headroom, 1, 5)

*C EQUALS NO. OF CONDITIONS BELOW
forval c= 1/2{
    corr mpg weight if `condition`c''
    frame wanted: replace Variable1= word("`:colnames r(C)'", 1)  in `c'
    frame wanted: replace Variable2= word("`:colnames r(C)'", 2)  in `c'
    frame wanted: replace Conditions= `"`condition`c''"' in `c'
    frame wanted: replace Result= r(rho) in `c'
}
frame change wanted
export excel using myfile.xls, replace keepcellfmt firstrow(var)

Res.:

[Nitsan Machlis]

Thanks! This works great.

[Nitsan Machlis]

Hi Andrew, I have been trying to add regression results to this same table. Any ideas why the code is not working? I recieve the error message: no observations or invalid syntax, even when using e(b)[1,1] to access the stored results, instead of r(table). I have attached the loop below - only the last three lines cause problems.

forval c= 1/52{
    corr nifsal z_shaked if `condition`c''
    frame frame1: replace Variable1= word("`:colnames r(C)'", 1)  in `c'
    frame frame1: replace Variable2= word("`:colnames r(C)'", 2)  in `c'
    frame frame1: replace Conditions= `"`condition`c''"' in `c'
    frame frame1: replace Corr= r(rho) in `c'
    sum nifsal if `condition`c''
    frame frame1: replace Nifsal_Mean= r(mean) in `c'
    sum z_shaked if `condition`c''
    frame frame1: replace Z_Shaked_Mean= r(mean) in `c'
    regress z_shaked nifsal if `condition`c''
    frame frame1: replace Regression_Beta= r(table)[1,1] in `c'
    frame frame1: replace Std_Error= r(table)[2,1] in `c'

}

[Andrew Musau]

Hi Nitsan - It is common to have "insufficient observations" errors as some of your conditions may not identify sufficient observations in the sample. You need several observations to get correlations. Consider the condition below where my sample is cars with an engine displacement of less than 100 cubic inches and worth more than 10,000 dollars. No car in the auto dataset satisfies this condition.

sysuse auto, clear
corr mpg weight if disp<100 & price>10000

Res.:

. corr mpg weight if disp<100 & price>10000
no observations
r(2000);

The capture command allows the loop to proceed by suppressing the "insufficient observations" errors and skipping such regressions. See

help capture

Below, it is possible to amend the loop in #2 so that it prints in the Excel sheet the condition and "insufficient observations" in case a condition does not identify sufficient observations in the sample. Otherwise, it will print the wanted information.

sysuse auto, clear
frame create wanted
frame wanted{
    set obs 100
    gen Variable1= ""
    gen Variable2= ""
    gen Conditions= ""
    gen Result=.
}
*LIST CONDITIONS
local condition1 regexm(make, "^AMC") & !foreign
local condition2 disp<100 & price>7000
local condition3 rep78==2 & inrange(headroom, 1, 5)

*C EQUALS NO. OF CONDITIONS BELOW
forval c= 1/3{
    capture noisily: corr mpg weight if `condition`c''
    if inlist(c(rc), 2000,2001){
        frame wanted: replace Variable1= "Insufficient Observations"  in `c'
        frame wanted: replace Conditions= `"`condition`c''"' in `c'
    }
    if c(rc)==0{
        frame wanted: replace Variable1= word("`:colnames r(C)'", 1)  in `c'  
        frame wanted: replace Variable2= word("`:colnames r(C)'", 2)  in `c'
        frame wanted: replace Conditions= `"`condition`c''"' in `c'
        frame wanted: replace Result= r(rho) in `c'
    }
}
frame change wanted
export excel using myfile.xls, replace keepcellfmt firstrow(var)

Res.:

If the above does not prove to be useful, provide a reproducible example using the dataex command so that we can diagnose the problem further.