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]

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

To |
statalist@hsphsun2.harvard.edu |

Subject |
Re: st: store tabulate command results in excel |

Date |
Thu, 15 Mar 2012 18:26:06 -0500 |

<> On Mar 15, 2012, at 8:21 AM, Anisa Shyti wrote: > I tried your code in several ways, but it does not give the > information I want. [...] It works, but it's still a lot of manual > work, not free of errors. ... > I don't fully understand the code, but the > variables it ads do not count % of A according to the conditions I > need to specify. You say it doesnt work and then say it does work with some mysterious problems/bugs --I'm not sure what isn't working (or what you don't understand). Please be precise. 'feedback' is a new variable - I assume this is the same as 'condition' in your previous posts? > > "Row" in this case, refers to "rows" inside the questions (each > question has a set of rows - i and j - unbalanced panel). I'm not sure what "i" and "j" are here -- where are they in the data or output table? I get that "i" and "j" are common letter used to refer to elements in panel data - but I'm not sure how that applies to your data. You have one element - a variable called 'Row' that becomes the cross-variable in the tabulation output. My code does exactly that. So are these the correct conditions now? (1) nr means "number of respondents for each question (not overall for all questions)",nr does not indicate the #/frequency of "A" responses (2) % is the percent of all responses per Question/Condition/Row that are marked "A" It sounds like my code produces what you need except that I misunderstood "nr" to be "number of responses for A" instead of "number of respondents overall". I've modified my code accordingly below. These changes now include the number of respondents (which I calculate based on the number who answered "A" or "B" -- I don't see any missing values in your example. You'd need to account for those if they exist in your real data) instead of the frequency of "A" responses by adding the line: g nr = freq_A + freq_B You could also get the number of respondents for each Question/Condition/Row with: g nn = 1 bys `UoI': egen NR = count(nn) The % of "A" responses are calculated against the % of B responses per Question/Condition/Row -- if this % is not right, please explain. Finally, the structure of the table the code below creates is the nr next to the col % (not stacked on top of one another which is a new formatting convention you added to your latest message). If you absolutely need the stacking now, check Example 2 in thread for tips on reshaping your data: http://www.stata.com/statalist/archive/2012-03/msg00376.html - Eric ********************************! **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 = /// count(a2) if answer=="`u'" //changed 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 g nr = freq_A + freq_B //added **alternate g nn = 1 //added bys `UoI': egen NR = count(nn) //added assert nr == NR //added ** preserve bys `UoI': g i = 1==_n drop if i!=1 drop i freq_A freq_B user_id answer a2 //changed **aesthetics: //changed tostring pct_A, replace force use replace pct_A = pct_A + " %" rename pct_A pctA_Row reshape wide nr pctA_Row, /// i(questionid condition) j(row) export excel using "table.xlsx", /// replace first(variable) restore ********************************! __ Eric A. Booth Public Policy Research Institute Texas A&M University ebooth@ppri.tamu.edu +979.845.6754 * * 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/

**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>

**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: generate a date (plus 40 days from a start date)** - Next by Date:
**st: MGARCH DCC in panel settings - error?** - Previous by thread:
**Re: st: store tabulate command results in excel** - Next by thread:
**Re: st: RE: if, in using issues with ado files: syntax bug?** - Index(es):