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
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
[email protected]
+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/