how can I transfer the logistic regression outcome to an excel sheet?

[Amin Saad]

Dear Members,

How can I transfer the output of this regression model to an excel sheet? I am using Stata 12

foreach var in debakey smoking_hx illdruguse dm htn hyperlipidemia cva_hx cad_hx chf_hx cancer_hx ///
chemorx radiorx liverdisease_hx neurologicaldisorder_hx aneurysm_hx copd_hx asthma_hx dialysis cabg_hx pvds_hx avs_hx evartevar_hx ///
chestpain neckjawpain backpain syncope seizure altermenstatus extremityneurosym dyspnea abdpain renal_insufficiency ///
pe_bp_diff pe_aortic_regurg cardiac_tamponade pe_asym_pulse abnorm_ekg abd_pain vasc_consult aorticgrafttype ///
great_vessels innominate left_carotic lsa cerebral_activity_monitor mep_sep_monitor hca_used ///
coronary_art_impl cabg_ascend_repai pericardial_effusion coagulopathy rupture new_onset_perm_hd visceral_necrosis paralysis__paraparesis ///
limbloss_gangrene inhospdeath complications second_op sec_procedure intaopdeath post_op_chf post_op_arrythmia post_op_dvt_pe postop_pneumonia po_sens_disturb ///
po_stroke po_weak_extreme po_paral_extreme po_delirium po_sepsis po_sens_disturb csf_drain_used wound_infection po_bleeding second_op sec_procedure tracheostomy vasc_consult {
tab `var' malperfusion, col chi2 exact
logistic malperfusion `var'
logistic malperfusion i.race age female i.`var'

I really appreciate your help.
Amin

[Clyde Schechter]

With -logistic-, Stata returns a matrix, r(table). You can get that matrix with -matrix M = r(table)-, and then use -putexcel- to export M. See -help putexcel- if you are not familiar with that command. You will need to do some coding inside your loop to calculate where in your worksheet you want to put the results of each regression.

[Ariel Karlinsky]

you can also you estout/esttab. see examples and manual here:
http://repec.org/bocode/e/estout/esttab.html

[Amin Saad]

Could you please help me with the code? the results are like 134 pages long and all what I want is to place the numbers in an excel sheet. I'll organize them later on.
I would really appreciate it.

[Jack Gibson]

With -logistic-, Stata returns a matrix, r(table). You can get that matrix with -matrix M = r(table)-, and then use -putexcel- to export M. See -help putexcel- if you are not familiar with that command. You will need to do some coding inside your loop to calculate where in your worksheet you want to put the results of each regression.

I don't think putexcel is available in Stata 12...

One (rather kludgy) alternative is to keep dumping the contents of r(table) into a csv file. Something like this should do the job:

tempfile reg1
tempfile reg2
tempfile maindata

save `maindata', replace
local counter1=0

foreach var in  debakey smoking_hx illdruguse dm htn hyperlipidemia cva_hx cad_hx chf_hx cancer_hx ///
chemorx radiorx liverdisease_hx neurologicaldisorder_hx aneurysm_hx copd_hx asthma_hx dialysis cabg_hx pvds_hx avs_hx evartevar_hx ///
chestpain neckjawpain backpain syncope seizure altermenstatus extremityneurosym dyspnea abdpain renal_insufficiency ///
pe_bp_diff pe_aortic_regurg cardiac_tamponade pe_asym_pulse abnorm_ekg abd_pain vasc_consult aorticgrafttype ///
great_vessels innominate left_carotic lsa cerebral_activity_monitor mep_sep_monitor hca_used ///
coronary_art_impl cabg_ascend_repai pericardial_effusion coagulopathy rupture new_onset_perm_hd visceral_necrosis paralysis__paraparesis ///
limbloss_gangrene inhospdeath complications second_op sec_procedure intaopdeath post_op_chf post_op_arrythmia post_op_dvt_pe postop_pneumonia po_sens_disturb ///
po_stroke po_weak_extreme po_paral_extreme po_delirium po_sepsis po_sens_disturb csf_drain_used wound_infection po_bleeding second_op sec_procedure tracheostomy vasc_consult {

    local regc1 "logistic malperfusion i.`var'"
    local regc2 "logistic malperfusion i.race age female i.`var'"

    forvalues curreg=2(-1)1 {
        use `maindata', clear // load the data
        `regc`curreg'' // run the regression command

        matrix M = r(table) // extract the results table
        matrix MTransposed=M' // transpose columns and rows so it looks more like the onscreen output

        local rownames : rowfullnames MTransposed // extract the row labels
        clear // start a fresh dataset
        svmat MTransposed, names(col) // empty the results into it
    
        // put the row labels into a variable
        gen rowname=""
        local counter=1
        foreach rown of local rownames {
            replace rowname="`rown'" in `counter'
            local counter=`counter'+1            
        } 
        order rowname

        local x=_N+5 // put some empty rows after the results
        set obs `x' // so there are gaps between tables in the final output file


        save `reg`curreg'', replace
        append using `reg2'
    }
    // join everything together
    if `counter1'==0 {
        save output, replace    
    }     
    else {
        append using output
        save output, replace
    }
    local counter1=`counter1'+1
}
outsheet using output.csv, replace

[Clyde Schechter]

Jack Gibson Yes, you are right. I should have checked that first. -putexcel- first appeared in version 13.

[Amin Saad]

Jack Gibson First of all thanks a lot for your input. This is how the data was generated. Is it possible to include the 2x2 table and the p value of the chi2/t-test. Also, is it possible to have each value in a seperate column/cell?
I am really really very greatful
Amin
Clyde Schechter your input is highly appreciated as well.

[Jack Gibson]

Amin Saad Not quite sure what you mean by "is it possible to have each value in a separate column/cell" -- each value should already be in its own cell.

It looks like you've tried to include a picture or something below your post, but it's not showing up here. Can you try uploading it as an attachment instead?

[Amin Saad]

Jack Gibson I have attached a copy of the excel sheet

[Amin Saad]

I also tried to do this for a different set of variables. tempfile reg1
tempfile reg2
tempfile maindata2

save `maindata2', replace
local counter1=0

foreach var in race bmi_stage pe_blood_pressure dissect_cause dissect_extent asa_class op_technique valve cannulation_sites proximal_anastamosis distal_anastamosis ///
po_renal_dys dcdisp spinal_malperfusion dcdisp {
tab `var' malperfusion, col chi2

local regc1 "logistic malperfusion i.`var'"
local regc2 "logistic malperfusion i.race age female i.`var'"

forvalues curreg=2(-1)1 {
use `maindata2', clear // load the data
`regc`curreg'' // run the regression command

matrix M = r(table) // extract the results table
matrix MTransposed=M' // transpose columns and rows so it looks more like the onscreen output

local rownames : rowfullnames MTransposed // extract the row labels
clear // start a fresh dataset
svmat MTransposed, names(col) // empty the results into it

// put the row labels into a variable
gen rowname=""
local counter=1
foreach rown of local rownames {
replace rowname="`rown'" in `counter'
local counter=`counter'+1
}
order rowname

local x=_N+5 // put some empty rows after the results
set obs `x' // so there are gaps between tables in the final output file

save `reg`curreg'', replace
append using `reg2'
}
// join everything together
if `counter1'==0 {
save output, replace
}
else {
append using output
save output, replace
}
local counter1=`counter1'+1
}
outsheet using output2.csv, replace


I am getting this error after it runs the analysis for the first variable "race"


number of observations will be reset to 4
Press any key to continue, or Break to abort
obs was 0, now 4
(4 missing values generated)
rowname was str1 now str20
(1 real change made)
(1 real change made)
(1 real change made)
(1 real change made)
obs was 4, now 9
(note: file C:\Users\amin_\AppData\Local\Temp\ST_04000001.tmp not found)
file C:\Users\amin_\AppData\Local\Temp\ST_04000001.tmp saved
file output.dta saved
variable bmi_stage not found
r(111);

end of do-file

r(111);

[Jack Gibson]

To fix the first issue, try changing the outsheet command to:

outsheet using output.csv, comma replace

[Amin Saad]

To fix the first issue, try changing the outsheet command to:

outsheet using output.csv, comma replace

genius!!

[Jack Gibson]

The second problem (bmi_stage not found) is happening because your tab command needs to be in the forvalues{} loop.

The best place for it is in between these two commands:

use `maindata2', clear // load the data
`regc`curreg'' // run the regression command

I'll have a think about how to get the output of the tab command to appear in the output file and get back to you. It's possible, but a bit fiddly.

[Amin Saad]

Jack Gibson Seriously thanks for all of your help. I am a student and i'm learning Stata by my own. I wasn't able to figure this out. I hope you can help me with the following questions.

1) so right now what is missing is the 2x2 table and the results of the of the chi2/t-test. I'll wait for your response to that.

2) How would the code be different when doing the variance equality test, t-test of equal and unequal variance? (part 3 of the code below)

3) is there a way to combine all the three different codes so that I can generate one output file? (Using your codes, I was able to generate two separate output files the first and second part of the analysis (output and output2)

***** Run chi-squares and logistic regressions to get odds ratios to predict malperfusion *****
foreach var in debakey smoking_hx illdruguse dm htn hyperlipidemia cva_hx cad_hx chf_hx cancer_hx ///
chemorx radiorx liverdisease_hx neurologicaldisorder_hx aneurysm_hx copd_hx asthma_hx dialysis cabg_hx pvds_hx avs_hx evartevar_hx ///
chestpain neckjawpain backpain syncope seizure altermenstatus extremityneurosym dyspnea abdpain renal_insufficiency ///
pe_bp_diff pe_aortic_regurg cardiac_tamponade pe_asym_pulse abnorm_ekg abd_pain vasc_consult aorticgrafttype ///
great_vessels innominate left_carotic lsa cerebral_activity_monitor mep_sep_monitor hca_used ///
coronary_art_impl cabg_ascend_repai pericardial_effusion coagulopathy rupture new_onset_perm_hd visceral_necrosis paralysis__paraparesis ///
limbloss_gangrene inhospdeath complications second_op sec_procedure intaopdeath post_op_chf post_op_arrythmia post_op_dvt_pe postop_pneumonia po_sens_disturb ///
po_stroke po_weak_extreme po_paral_extreme po_delirium po_sepsis po_sens_disturb csf_drain_used wound_infection po_bleeding second_op sec_procedure tracheostomy vasc_consult {
tab `var' malperfusion, col chi2 exact
logistic malperfusion `var'
logistic malperfusion i.race age female i.`var'
}

*** Logistics and chi-squares for variables with more than 2 categories -

foreach var in race bmi_stage pe_blood_pressure dissect_cause dissect_extent asa_class op_technique valve cannulation_sites proximal_anastamosis distal_anastamosis ///
po_renal_dys dcdisp spinal_malperfusion dcdisp {
tab `var' malperfusion, col chi2
logistic malperfusion i.`var'
logistic malperfusion i.race age female i.`var'
}


*** Logistics and t-test for continuous variables:

foreach var in age weight_kg height_cm bmi ef dissect_extent egfr als_present ast_present albumin_admit pt_sec__admit aptt_sec__admit ///
wbc_admit hb_admit hct_admit platelets cd_admit bun_admit fibrinogen_admit troponin ph postup_high_wbc postop_high_k ///
postop_lactate graft_diameter cirulatory_arrest_time total_pump_time tot_aortic_clamp_time cooling_tem tot_op_time intraop_urine_ouptut ///
ebl_ml colloid_ml prbc cell_saver platelets0 ffp cryo crystalloids los_days po_highest_cr cr_dc maxdiaascendaorta maxidiamarch maxidiamdescendingaorta {
di "Variance equality test for `var'"
sdtest `var', by(malperfusion)
di "T-test assuming equal variances for `var'"
ttest `var', by(malperfusion)
di "T-test assuming unequal variances for `var'"
ttest `var', by(malperfusion) unequal
logistic malperfusion `var'
logistic malperfusion i.race age female `var'
}

[Jack Gibson]

Amin.

1. The code below will add the frequencies, the chi2 and the p-value to the right of the output table. Values will appear in the order dictated by the category numbers -- unfortunately adding the column or row labels, or the column percentages would be a lot more complicated...
2. As I mentioned before, the error happens because you put a tab command early on in the foreach loop before the main dataset gets reloaded. See the code below for a safe place to run it.
3. This code is a bit of a hack, and it's pretty complicated. It's not a standard procedure, and you're not going to be able to coerce it into doing everything you want. For the veq and the t-tests, you'll be better off just doing them separately and saving the results into log files. You're probably only interested in the final p-values in each case, so you're not actually saving yourself any work by having to copy/paste them from one excel sheet to another vs just copying them across from the logs. Alternatively, look into using the esttab add-on as someone else suggested previously...

Taking a step back for a moment, are you really sure you need to be generating all these results? You're doing an awful lot of analyses on an awful lot of variables; this looks like data dredging to me. You should really discuss with your supervisor whether all of this is really necessary...

Anyway, amended code below is the best I can come up with. Hope it helps...

tempfile reg1
tempfile reg2
tempfile maindata

save `maindata', replace
local counter1=0

foreach var in debakey smoking_hx illdruguse dm htn hyperlipidemia cva_hx cad_hx chf_hx cancer_hx ///
chemorx radiorx liverdisease_hx neurologicaldisorder_hx aneurysm_hx copd_hx asthma_hx dialysis cabg_hx pvds_hx avs_hx evartevar_hx ///
chestpain neckjawpain backpain syncope seizure altermenstatus extremityneurosym dyspnea abdpain renal_insufficiency ///
pe_bp_diff pe_aortic_regurg cardiac_tamponade pe_asym_pulse abnorm_ekg abd_pain vasc_consult aorticgrafttype ///
great_vessels innominate left_carotic lsa cerebral_activity_monitor mep_sep_monitor hca_used ///
coronary_art_impl cabg_ascend_repai pericardial_effusion coagulopathy rupture new_onset_perm_hd visceral_necrosis paralysis__paraparesis ///
limbloss_gangrene inhospdeath complications second_op sec_procedure intaopdeath post_op_chf post_op_arrythmia post_op_dvt_pe postop_pneumonia po_sens_disturb ///
po_stroke po_weak_extreme po_paral_extreme po_delirium po_sepsis po_sens_disturb csf_drain_used wound_infection po_bleeding second_op sec_procedure tracheostomy vasc_consult {
 

    local regc1 "logistic malperfusion i.`var'"
    local regc2 "logistic malperfusion i.race age female i.`var'"

    forvalues curreg=2(-1)1 {
        use `maindata', clear // load the data
        tab `var' malperfusion, chi2 matcell(X)
        local chi2val=r(chi2)
        local chi2p=r(p)
        
        `regc`curreg'' // run the regression command

        matrix M = r(table) // extract the results table
        matrix MTransposed=M' // transpose columns and rows so it looks more like the onscreen output

        local rownames : rowfullnames MTransposed // extract the row labels
        clear // start a fresh dataset
        svmat MTransposed, names(col) // empty the results into it

        // put the row labels into a variable
        gen rowname=""
        local counter=1
        foreach rown of local rownames {
            replace rowname="`rown'" in `counter'
            local counter=`counter'+1
        }
        order rowname

        local x=_N+5 // put some empty rows after the results
        set obs `x' // so there are gaps between tables in the final output file
        

        save `reg`curreg'', replace
                
        append using `reg2'
        
        // add in the crosstab results
        svmat X
        gen chi2=`chi2val' in 1
        gen chi2p=`chi2p' in 1
        
        }
        // join everything together
        if `counter1'==0 {
            save output, replace
        }
        else {
        append using output
        save output, replace
        }
    local counter1=`counter1'+1
}
outsheet using output.csv, comma replace