Pivot Table in Stata

[Sean Fiedler]

I would like to make a pivot table, meaning show sums of one variable, where rows show values of another variable and columns show values of another variable.

Example Input:


time X1 X2

Jan2015 30 Group1
Jan2015 20 Group1
Jan2015 200 Group2

Feb2015 15 Group1
Feb2015 150 Group2
Feb2015 120 Group2



Desired Output (sum of X1, rows are time, columns are X2):

Group1 Group2
Jan2015 50 200
Feb2015 15 270




I also need the output to be another dataset, the result will be too large to copy from the output window.

I have tried reading many other posts but could not find an answer. Thanks in advance.

[Clyde Schechter]

I think you want:

table time x2, c(sum x1) replace

[Nick Cox]

For export as a new dataset, check out collapse

[Clyde Schechter]

Actually, the code in #2 will not create a data set of the kind you want, although it will print that kind of table in the Results window. You would need to follow it up with a -reshape-. Since -table- doesn't do that for you, it is better to follow Nick's advice and use -collapse- (followed by -reshape-).

So:

collapse (sum) cell = X1, by(time X2)
reshape wide cell, i(time) j(X2) string
rename cell* *

Note: The feasibility of this approach hinges crucially on the values of X2 (Group1 and Group2) being legal Stata variable names. If that is not the case in your real data, then what you seek cannot, strictly speaking, be done, and you may have to modify the values of X2 to make them legal as Stata variable names. For that purpose, see the help for -strtoname()-.

[Sean Fiedler]

Works perfectly Clyde, thank you! (and Nick)