I am working on a survey data from a small group of people (n=281). Among this group of people there are subgroups, identified by characteristics such as race, education level, income source etc. Subgroups can be mutually exclusive binary or categorical groups. For example, race (black, white, others categorical), education (with GED/without GED binary). There are about 35 subgroups.
All survey question selections are binary yes and no. There are 6 question sets, with 15~20 binary question selections in each question set. For example: question set income source has 15 question selections (q1 source of income from self-employment Y/N; q2 source of income from full-time employment Y/N, q3 source of income from governmental assistance Y/N etc.). Each person can check multiple responses in each question set.
I want to look at the associations between subgroups, and their answer to each of the question selections. Tests used include plain chi-square, exact, or mtest in mrtab. For instance: subgroup X (X=0 such as without GED, X=1 those with GED), compare the proportion (in a sense, prevalence) who answered yes to Y question (Y=0 no my income is not from self-employment, Y=1 yes I have income that is from self-employment). Basically 2 by 2 table.
Because of the volume, I exported them to excel files, separated into different sheets by subgroups or sheets by question sets. Originally it worked out fine. But recently after I added a few codes, stata started to crash at times, or stopped suddenly with error r(603) "workbook filename.xlsx could not be loaded". It can occur at different timing after putexcels. But next time it might ran through the same code non-stop. Since it did not happen previously I think there must be problems with my code. I don't think Internet connection is a problem. I use Stata/MP14.2 on a distal server.
Code:
/*locals for question sets QS1 to QS6, each with binary Y/N question selections*/
local QS1 q1 q2 q3 q4 q5 q6 q7 [...] q12 q13 q14 q15 /*for clarity, use qx variables to represent original var names which contained no numbers*/
local QS2 a1 a2 a3 [...] a18 a19 a20
local QS3 b1 b2 b3 [...] b17 b18 b19
local QS4 c1 c2 c3 [...] c17 c18 c19
local QS5 d1 d2 d3 [...] d17 d18 d19
local QS6 e1 e2 e3 [...] e17 e18 e19
/*subgroups were replaced by A B C D E, here for example 5 binary groups.
ex: A==0 means no GED, A==1 means with GED diploma; B==0 no kids B==1 with kids etc.*/
foreach subgroups in A B C D E {
foreach list in QS1 QS2 QS3 QS4 QS5 QS6 {
putexcel set "date_questions.xlsx", sheet(`subgroups'_`list') modify
/*the mrtab displays table with numbers who answered yes to all question selections in one question set, by subgroups*/
mrtab ``list'',by(`subgroups') include col chi2 mtest(bonferroni) title(`list') width(50)
return list
matrix Am = r(responses)
matrix Bm = r(cases)
matrix Cm = r(mchi2)
putexcel L19= matrix(Am),names
putexcel L55= matrix(Bm),names
putexcel P19= matrix(Cm),names
putexcel L19= "`subgroups'"
putexcel L55= "`subgroups'"
putexcel P19= "`subgroups' chi square"
/*Then local numbers for each subgroups, and how many of them selected yes to the question selection Q in a question set (ex. percentage checked yes to q2 in QS1 question set)
s0 is the number of people of subgroup==0, s1 is numbers of people in subgroup==1; s0x1 how many among subgroup==0 who answered yes to question selection Q*/
local row=20
foreach Q in ``list''{
forval s=0/1{
count if `subgroups'==`s'
local s`s'=r(N)
forval x=0/1{
count if `subgroups'==`s' & `Q'==`x'
local s`s'x`x'= r(N)
}
}
/*Then local proportion/prevalence separately for subgroup==0 and subgroup==1 who answered yes to question selection Q, and how they should be displayed in excel*/
local pv0: di `s0x1' " (" %5.2f ((`s0x1')/`s0')*100 "%)"
local pv1: di `s1x1' " (" %5.2f ((`s1x1')/`s1')*100 "%)"
local prvr= ((`s1x1')/`s1')/((`s0x1')/`s0')
local prvrdisp: di %5.2f `prvr'
/*Obtain the chi square statistics, significance, and use macro to represent the format I want them to be in excel. Different significance can have different asterisks.*/
tab `subgroups' `Q',chi exact
if r(p)<0.001{
local p001: di "p<0.001**"
putexcel I`row'=("`p001'")
}
else if r(p)<0.01{
local p01: di %5.3f r(p) "*"
putexcel I`row'=("`p01'")
}
else{
local p: di %5.3f r(p)
putexcel I`row'=("`p'")
}
if r(p_exact)<0.001{
local pex001: di %5.3f r(p_exact) "**"
putexcel J`row'=("`pex001'")
}
else if r(p_exact)<0.01{
local pex01: di %5.3f r(p_exact) "*"
putexcel J`row'=("`pex01'")
}
else{
local pex: di %5.3f r(p_exact)
putexcel J`row'=("`pex'")
}
local varlabel: var label `Q'
putexcel A`row'=("`Q'")
putexcel B`row'=("`varlabel'")
putexcel C`row'=("`s0x1'")
putexcel D`row'=("`pv0'")
putexcel E`row'=("`s1x1'")
putexcel F`row'=("`pv1'")
putexcel G`row'=("`prvrdisp'")
putexcel H`row'=(r(chi2))
local ++row
}
putexcel A19=("`list'")
putexcel B19=("`list' Label")
putexcel C19=("`subgroups'==0")
putexcel D19=("Prevalence Among `subgroups'==0")
putexcel E19=("`subgroups'==1")
putexcel F19=("Prevalence Among `subgroups'==1")
putexcel G19=("Prevalence Ratio")
putexcel H19=("Chi-square")
putexcel I19=("Chi-square Test")
putexcel J19=("Fisher's Exact Test")
}
}
I apologize if these are confusing! Each type of the variable (categorical, coded binary 1 and 2 etc.) has one different code set. After I break some of the loops the same thing still happened (crash or could not be loaded...). Is it that I should not use too many macros or putexcels? I really need advice... Thank you so much for the time and patience!!!
*Codes I downloaded by searching:
mrtab (by Ben Jann, Hilde Schaeper)
*Without this forum, Stata manual or Stata journal I can never put these together..
0 Response to Macros & loops, Putexcel and crash
Post a Comment