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


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