loops with many statistical tests and exporting results to Excel

[Muhammad Anees]

Hello
I want to run group comparison using basic inference using t-test and others methods mentioned in the code.
When I use only one test at a time in the loop, it works fine as shown in first segment. But when I make the test over the loop, it works fine till producing results but fail to write to post_file so I cant export the results readily to Excel.
The two code, working example and not working example, is attached below. Your comments and suggestions on second part is requested so it works and be able to export the results to Excel.
Thanks in advance.

************************************************** *******************
****Example does work
************************************************** *******************
clear
use "data.dta", clear
tempname anees_ttest
tempfile ttests
postfile `anees_ttest' str12 name t_statistic using `ttests'
foreach var of varlist Names go here {
ttest `var', by(group)
post `anees_ttest' ("`var'") (r(t))
}
postclose `anees_ttest'
clear
use `ttests'
list
export excel using "E:\Stata\export.xls", firstrow(variables) replace
************************************************** *******************
****Example does not working
************************************************** *******************
clear
use "data.dta", clear
tempname anees_ttest
tempfile ttests
postfile `anees_ttest' str12 name t_statistic shapiro_wilk rank_rum pearson_chi2 fisher_chi2 using `ttests'
foreach var of varlist Names go here {
ttest `var', by(group)
swilk `var'
ranksum `var', by(group)
median `var', by(group)
median `var', by(group) exact
post `anees_ttest' ("`var'") (r(t)) (r(W)) (r(z)) (r(chi2)) (r(chi_cc))
}
postclose `anees_ttest'
clear
use `ttests'
list
export excel using "E:\Stata\export.xls", firstrow(variables) replace
************************************************** *******************
****Example Ends
************************************************** *******************

Regards
---------------------------
Muhammad Anees

Assistant Professor
COMSATS Institute of Information Technology
Near Officers Colony, Kamra Road
Attock 43600, Pakistan
elearning(.)aneconomist(.)com

[Clyde Schechter]

The problem is that the results returned in r() are volatile: they disappear as soon as another command returning new r() results is run. So, for example, when you run the -swilk- command, all of the r() results that -ttest- computed cease to exist. Similarly the subsequent commands. By the time you get down to your -post- command, all of those r() results are gone except the output of the last -median- command. One solution is to save the needed r() results in local macros after each command and then reference those local macros in your -post- command.

[Muhammad Anees]

Hello Clyde,

Sorry for delay in checking in.

I have tried this but still it saves the last to the post keeping the rest of the code intact.
************************************************** *********
postfile `anees_ttest' str12 name t_statistic using `ttests'
foreach var of varlist Names go here {
ttest `var', by(group)
local tstat (r(t))
swilk `var'
local swilk (r(W))
ranksum `var', by(group)
local rankstat (r(z))
median `var', by(group)
local pearson (r(chi2))
median `var', by(group)
local fisher (r(chi_cc))
post `anees_ttest' ("`var'") ("`tstat'") ("`swilk'") ("`rankstat'") ("`pearson'") ("`fisher'")

Can you help me out by showing a step or two in the last macro component? I am sure you would do.

Thanks.

[Clyde Schechter]

Looking more carefully at what you're doing, it seems that your -postfile- declaration is not consistent with the rest of your code. Your -postfile- declaration sets up a file that has two variables: a string variable called name and a float variable called t_statistic. It is not set to receive a whole bunch of different statistics. In addition statements like -local tstat (r(t))- will cause the string (r(t)) to be stored in local macro tstat, but what you need is the actual value of r(t). So all of your -local- commands are wrong.

I am not entirely clear on what you are actually getting. The code you have shown above will not run at all: the inconsistency between the -postfile- declaration and the -post- command will cause Stata to stop with an error message once it reaches that -post- command. So I do not know what you mean when you say " it saves the last to the post keeping the rest of the code intact." In fact, that code will save nothing at all.

So I think you want something more like this:

postfile `anees_ttest' str12 name t_statistic swilk rankstat pearson fisher using `ttests'
foreach var of varlist Names go here {
     ttest `var', by(group)
     local tstat = r(t) // NOTE: local tstat `r(t)' WILL ALSO WORK, BUT NOT local tstat (r(t))
     swilk `var'
     local swilk = r(W)
     ranksum `var', by(group)
     local rankstat = r(z)
     median `var', by(group) // NOTE NO NEED TO RUN -median- TWICE
     local pearson = r(chi2)
     local fisher = r(chi_cc)
     // IN COMMAND BELOW, NOTE NO QUOTES AROUND NUMERIC VARIABLES
     post `anees_ttest' ("`var'") (`tstat') (`swilk') (`rankstat') (`pearson') (`fisher')
}

If this does not give you what you want, next time show us the exact code you ran and the exact output Stata produced in response. Do this by pasting into code blocks on this editor: do not attach screen shots (which are rarely readable), and do not manually re-type the code or output as minor differences could be crucial.

[Muhammad Anees]

Hello Clyde,

Many thanks for your suggestions. After some trials and tweaking your code (as it was not working with the median command to produce two locals, not sure why? ), but I used the following and got the results into a single file. Just sharing the code that worked. Many thanks again and I accept your suggestions as a new year gift.

**********Code starts
use "E:\Dropbox\anees_data12.dta", clear
tempname anees_ttest
tempfile ttests
postfile `anees_ttest' str12 name t_statistic swilk rankstat pearson fisher using `ttests'
foreach var of varlist variables_here {
ttest `var', by(group)
local tstat = r(t)
swilk `var'
local swilk = r(W)
ranksum `var', by(group)
local rankstat = r(z)
median `var', by(group)
local pearson = r(chi2)
median `var', by(group) exact
local fisher = r(chi2_cc)
post `anees_ttest' ("`var'") (`tstat') (`swilk') (`rankstat') (`pearson') (`fisher')
}
postclose `anees_ttest'
export excel using "E:\Dropbox\Stata to Excel\Compiled Results.xls", firstrow(variables) replace
***********Code ended

I would like to close this thread as I got the code worked charmly after your suggestion.

Best Regards
Muhammad Anees
Assistant Professor/Consultant Econometrician

[Konrad Zdeb]

Hi Muhammed,

You may be willing to have a look at this post on Stata Daily, on saving stored results as scalars. My initial approach would be to move r() values to a matrix as soon as possible, beef it up with all the figures you are interested in exporting and then, whenever completed, export the matrix, as suggested in this article. You would have to emebed a primitive count somewhere to save the values in different rows but it may be worth a hassle. In effect, you would have a one neat object that can be easily referenced on exported whenever desired.

[Muhammad Anees]

Hello Konrad,

Yes, I agree. It might have worked.

For the current objective to export stored results went well.

I deeply value your intuite solution.

Anees

[Clyde Schechter]

The problem with using a matrix to hold the results is that you can't store strings in Stata matrices. (You can in Mata, but then you have to learn Mata to do this. And even in Mata, you can't mix string and numeric elements.) Since Anees wants to put the variable name in the first column of his results, and the other columns are statistics, if he used a matrix for the statistics he would still need some other object to hold the variable names, and he would have to have it indexed compatibly with the matrix. Altogether it's more work to manage those two objects. The virtue of a postfile is that it can hold anything a data set can hold. So that seems the simplest approach in this situation.

[Nadia Koyratty]

Hello, I am using Stata V16 and I have used a similar code for looping t-tests and ranksum tests for multiple variables as below. However, I keep getting an error message when I try to export. Can anyone help with this please?


tempname aim3_ttest_ranksum
tempfile aim3
postfile `aim3_ttest_ranksum' str12 name t_statistic swilk rankstat pearson using `aim3'

foreach var of varlist hhsize parity mom_age phs gna pts pss epds mse m12ch_dds m18ch_dds diff_dds {

ttest `var', by (mdd_uptake)
local tstat = r(t)
swilk `var'
local swilk = r(W)
ranksum `var', by (mdd_uptake)
local rankstat = r(z)
median `var', by(mdd_uptake)
local pearson = r(chi2)

post `aim3_ttest_ranksum' ("`var'") (`tstat') (`swilk') (`rankstat') (`pearson')
}
postclose `aim3_ttest_ranksum'

export excel using "C:\Users\Nadia\Box Sync\dissertation\aim3_ttest_ranksum.xls", firstrow(variables) replace

This last line gives the following error message: too many or no variables specified r(198);

[Clyde Schechter]

Well, to be honest, I can't really say why you are getting that particular error message. But apart from that, there is a gaping logic error here. I imagine that what you want to export to Excel is the results of the ttest anad ranksum tests, right? But the code you have written attempts to export the original data set that was loaded before those tests were done. Important fact: -postclose- does not cause the postfile to be loaded into memory. It just closes the file on disk. To bring those results into memory, you have to explicitly -use `aim3_ttest_ranksum'- first. So put that command in before your -export excel- command. If you're still getting an error message after that, then post back and show example data using the -dataex- command, as well as the exact error message you are getting.

If you are running version 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.