Stata The Stata listserver
[Date Prev][Date Next][Thread Prev][Thread Next][Date index][Thread index]

st: RE: pivot capability in Stata?


From   "Nick Cox" <[email protected]>
To   <[email protected]>
Subject   st: RE: pivot capability in Stata?
Date   Thu, 31 Jul 2003 09:43:27 +0100

Howard <[email protected]> 

> As an inexperienced user trying to wean himself from EXCEL, 
> I have a chart in the form:
> 
> date    clinic    count
> 1/1/93    A     2
> 1/2/93    B     3
> 1/2/93    A     1
> 1/2/93    C     4
> 1/5/93    C     3
> 1/6/93    A     1
> 1/7/93    B     2
> 1/8/93    B     4
> 1/8/93    A     3
> 1/10/93  B     2
> 
> and would like to replace my (saved) dataset with:
> 
> date        A       B       C       Grand Total
> 1/1/93    2        0        0        2
> 1/2/93    1        3        4        8
> 1/3/93    0        0        0        0
> 1/4/93    0        0        0        0
> 1/5/93    0        0        3        3
> 1/6/93    1        0        0        1
> 1/7/93    0        2        0        2
> 1/8/93    3        4        0        7
> 1/9/93    0        0        0        0
> 1/10/93  0        2        0        2
> Total      7        11       7      25
> 
> (Assume many more columns, and notice the included dates 
> with no activity.)
> I'd consider using working on the listed output of
> 
> tabulate date clinic [freq=count], or a similar use of xtab,
> 
> but my table is very long, and I'm wondering if there is a 
> clean way to do
> this.
> Aside from 'contract', are there other commands that 
> replace the dataset
> with a transformed one?

I have Excel installed, but I have hardly ever used it, 
and I don't know what pivot tables are. I am more 
familiar with Stata. 

You need -reshape-, however. 

. reshape wide count, i(date) j(clinic) string
. renpfix count 
. mvencode A-C , mv(0)
. egen grandtotal = rsum(A-C) 

It isn't customary to put a line of totals 
within a Stata dataset. Any number of commands, 
e.g. -tabstat-, will produce those for you. 

On -reshape- see the manual entry [R] reshape 
and an FAQ with extra notes and explanation 
http://www.stata.com/support/faqs/data/reshape3.html

Nick 
[email protected] 

*
*   For searches and help try:
*   http://www.stata.com/support/faqs/res/findit.html
*   http://www.stata.com/support/statalist/faq
*   http://www.ats.ucla.edu/stat/stata/



© Copyright 1996–2024 StataCorp LLC   |   Terms of use   |   Privacy   |   Contact us   |   What's new   |   Site index