Notice: On April 23, 2014, Statalist moved from an email list to a forum, based at statalist.org.
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
Re: st: store tabulate command results in excel
From
Eric Booth <[email protected]>
To
[email protected]
Subject
Re: st: store tabulate command results in excel
Date
Wed, 14 Mar 2012 15:14:01 -0500
<>
Hi Anisa:
Just to be clear then, you want the freq and Row % of answer A (vs. B) for each 'Row' (the variable 'Row', not the row element of a table) within each Condition within each Question.
You can get this table with the code example below.
Caveat emptor: This assumes you have Stata version 12 (which you did not specify) -- if you have previous versions, you should change the -export- commands (to something like -outsheet-). Also, the code builds the elements of the table manually - this could probably be shortened - but you should be able to better adapt this code if the table still isnt formatted quite right.
Finally, you indicate that this whole exercise is to reduce the complexity of the dataset. You might consider a graph command to inspect the freq/% of A's in each answer/'Row'/condition/question - it may make patterns in this data more clear (since there are so many nested elements in your tables).
********************************!
**watch for wrapping below:
clear
inp user_id questionid row str2 answer str12 condition
1 1 1 "A" "- Feedback 1"
1 1 2 "A" "- Feedback 1"
1 1 3 "A" "- Feedback 1"
1 1 4 "B" "- Feedback 1"
1 1 5 "B" "- Feedback 1"
1 1 6 "B" "- Feedback 1"
1 1 7 "B" "- Feedback 1"
1 1 8 "B" "- Feedback 1"
1 1 9 "B" "- Feedback 1"
1 1 10 "B" "- Feedback 1"
1 1 11 "B" "- Feedback 1"
2 1 1 "A" "- Feedback 1"
2 1 2 "A" "- Feedback 1"
2 1 3 "A" "- Feedback 1"
2 1 4 "A" "- Feedback 1"
2 1 5 "A" "- Feedback 1"
2 1 6 "A" "- Feedback 1"
2 1 7 "A" "- Feedback 1"
2 1 8 "A" "- Feedback 1"
2 1 9 "A" "- Feedback 1"
2 1 10 "A" "- Feedback 1"
2 1 11 "A" "- Feedback 1"
2 2 1 "A" "- Feedback 1"
2 2 2 "A" "- Feedback 1"
2 2 3 "A" "- Feedback 1"
2 2 4 "A" "- Feedback 1"
2 2 5 "A" "- Feedback 1"
2 2 6 "A" "- Feedback 1"
2 2 7 "A" "- Feedback 1"
2 2 8 "A" "- Feedback 1"
2 2 9 "A" "- Feedback 1"
2 2 10 "A" "- Feedback 1"
2 2 11 "A" "- Feedback 1"
2 2 12 "A" "- Feedback 1"
2 2 13 "A" "- Feedback 1"
2 2 14 "A" "- Feedback 1"
2 2 15 "A" "- Feedback 1"
2 2 16 "A" "- Feedback 1"
2 2 17 "A" "- Feedback 1"
2 2 18 "A" "- Feedback 1"
2 2 19 "A" "- Feedback 1"
2 2 20 "A" "- Feedback 1"
2 2 21 "A" "- Feedback 1"
2 2 22 "A" "- Feedback 1"
2 2 23 "A" "- Feedback 1"
2 2 24 "A" "- Feedback 1"
2 2 25 "A" "- Feedback 1"
2 2 26 "A" "- Feedback 1"
12 1 1 "B" "- Feedback 2"
12 1 2 "B" "- Feedback 2"
12 1 3 "B" "- Feedback 2"
12 1 4 "B" "- Feedback 2"
12 1 5 "B" "- Feedback 2"
12 1 6 "B" "- Feedback 2"
12 1 7 "B" "- Feedback 2"
12 1 8 "B" "- Feedback 2"
12 1 9 "B" "- Feedback 2"
12 1 10 "B" "- Feedback 2"
12 1 11 "B" "- Feedback 2"
12 2 1 "B" "- Feedback 2"
12 2 2 "B" "- Feedback 2"
12 2 3 "B" "- Feedback 2"
12 2 4 "B" "- Feedback 2"
12 2 5 "B" "- Feedback 2"
12 2 6 "B" "- Feedback 2"
12 2 7 "B" "- Feedback 2"
12 2 8 "B" "- Feedback 2"
12 2 9 "B" "- Feedback 2"
12 2 10 "B" "- Feedback 2"
12 2 11 "B" "- Feedback 2"
12 2 12 "B" "- Feedback 2"
12 2 13 "B" "- Feedback 2"
12 2 14 "B" "- Feedback 2"
12 2 15 "B" "- Feedback 2"
12 2 16 "B" "- Feedback 2"
12 2 17 "B" "- Feedback 2"
12 2 18 "B" "- Feedback 2"
12 2 19 "B" "- Feedback 2"
12 2 20 "B" "- Feedback 2"
12 2 21 "B" "- Feedback 2"
12 2 22 "B" "- Feedback 2"
12 2 23 "B" "- Feedback 2"
12 2 24 "B" "- Feedback 2"
12 2 25 "B" "- Feedback 2"
12 2 26 "A" "- Feedback 2"
14 12 1 "A" "- Feedback 2"
14 12 2 "A" "- Feedback 2"
14 12 3 "A" "- Feedback 2"
14 12 4 "A" "- Feedback 2"
14 12 5 "A" "- Feedback 2"
14 12 6 "A" "- Feedback 2"
14 12 7 "A" "- Feedback 2"
14 12 8 "A" "- Feedback 2"
14 12 9 "A" "- Feedback 2"
14 12 10 "A" "- Feedback 2"
14 12 11 "A" "- Feedback 2"
14 12 12 "A" "- Feedback 2"
14 12 13 "A" "- Feedback 2"
14 12 14 "B" "- Feedback 2"
14 12 15 "B" "- Feedback 2"
14 12 16 "B" "- Feedback 2"
14 12 17 "B" "- Feedback 2"
14 12 18 "B" "- Feedback 2"
14 12 19 "B" "- Feedback 2"
14 12 20 "B" "- Feedback 2"
14 12 21 "B" "- Feedback 2"
14 12 22 "B" "- Feedback 2"
14 12 23 "B" "- Feedback 2"
14 12 24 "B" "- Feedback 2"
14 12 25 "B" "- Feedback 2"
14 12 26 "B" "- Feedback 2"
end
loc UoI `"questionid condition row"' //unit of interest
encode answer, g(a2)
foreach u in A B {
bys `UoI' : egen freq_`u'2 = ///
total(a2) if answer=="`u'"
bys `UoI': egen freq_`u' = max(freq_`u'2)
drop freq_`u'2
recode freq_`u' (.=0)
}
g pct_A = freq_A/(freq_A + freq_B)*100
preserve
bys `UoI': g i = 1==_n
drop if i!=1
drop i freq_B user_id answer a2
**aesthetics:
tostring pct_A, replace force use
replace pct_A = pct_A + " %"
rename freq_A freqA_Row
rename pct_A pctA_Row
reshape wide freqA_Row pctA_Row, ///
i(questionid condition) j(row)
export excel using "table.xlsx", ///
replace first(variable)
restore
********************************!
- Eric
__
Eric A. Booth
Public Policy Research Institute
Texas A&M University
[email protected]
+979.845.6754
On Mar 13, 2012, at 11:34 AM, Anisa Shyti wrote:
> Hi Eric,
>
> Apologies for the variable mismatch - feedback and condition are the
> same - that's accurate. Below you find the exact structure of my
> data. I have the row as an existing variable already, since questions
> have subquestions (in rows):
>
> I need a nested structure of the tab to refine the analysis, to see
> for each row the % A and B and the evolution on answers according to
> Condition.
>
> The structure of the output needs to be something like:
>
> QuestionID i
> ...........................row1........row2.........row3........row4........................rowN
> Feedback 1........ nr (%)A.....nr(%)A.....nr(%)A......nr(%)A................
> Feedback 2........ nr (%)A.....nr(%)A.....nr(%)A......nr(%)A................
> Feedback 3........ nr (%)A.....nr(%)A.....nr(%)A......nr(%)A................
>
> It's like zooming in in each of the previous cells (instead of having
> the total nr and % of A/B answers).
>
> Is this more clear?
>
> I tried what you suggest, but it generates Condition+row without
> keeping the distinction by user and questionID. How can I compose
> Condtion+Row given the structure of my data, preserving the rest of
> the information?
>
*
* For searches and help try:
* http://www.stata.com/help.cgi?search
* http://www.stata.com/support/statalist/faq
* http://www.ats.ucla.edu/stat/stata/