Notice: On March 31, it was **announced** that Statalist is moving from an email list to a **forum**. The old list will shut down at the end of May, and its replacement, **statalist.org** is already up and running.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

From |
Eric Booth <eric.a.booth@gmail.com> |

To |
statalist@hsphsun2.harvard.edu |

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 ebooth@ppri.tamu.edu +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/

**Follow-Ups**:**Re: st: store tabulate command results in excel***From:*Anisa Shyti <anisa.shyti@gmail.com>

**References**:**st: store tabulate command results in excel***From:*Anisa Shyti <anisa.shyti@gmail.com>

**Re: st: store tabulate command results in excel***From:*Eric Booth <eric.a.booth@gmail.com>

**Re: st: store tabulate command results in excel***From:*Anisa Shyti <anisa.shyti@gmail.com>

**Re: st: store tabulate command results in excel***From:*Eric Booth <eric.a.booth@gmail.com>

**Re: st: store tabulate command results in excel***From:*Eric Booth <eric.a.booth@gmail.com>

**Re: st: store tabulate command results in excel***From:*Anisa Shyti <anisa.shyti@gmail.com>

**Re: st: store tabulate command results in excel***From:*Anisa Shyti <anisa.shyti@gmail.com>

**Re: st: store tabulate command results in excel***From:*Eric Booth <eric.a.booth@gmail.com>

**Re: st: store tabulate command results in excel***From:*Anisa Shyti <anisa.shyti@gmail.com>

- Prev by Date:
**Re: st: margins and xtmixed** - Next by Date:
**Re: st: the use of foreach command in Stata** - Previous by thread:
**Re: st: store tabulate command results in excel** - Next by thread:
**Re: st: store tabulate command results in excel** - Index(es):