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/
