Bookmark and Share

Notice: On March 31, it was announced that Statalist is moving from an email list to a forum. The old list will shut down on April 23, and its replacement, statalist.org is already up and running.


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

Re: st: store tabulate command results in excel


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/


© Copyright 1996–2014 StataCorp LP   |   Terms of use   |   Privacy   |   Contact us   |   Site index