st: transforming data in one column into multiple columns

["Ben Hoen" <bhoen@lbl.gov>]

Hello statalisters,

(Thank you in advance.  I really do not know what I would do without this
list...)

I suspect this is an easy one, but I could not find it online:

I have this syntax which produces a simple table with multiple groups in a
single column:

*------------------
sysuse sp500, replace
sort date
keep in 1/6
expand 4
sort date
egen group=fill(2 3 4 7 2 3 4 7)
g day=day(date)
drop in 11/11
drop in 21/21
keep group day open
order group day open
sort group day
list

I would like to transform this data into a new table that looks like this:

day	2	      3	      4	      7
2	1320.28	1320.28	1320.28	1320.28
3	1283.27	1283.27	1283.27	1283.27
4	1347.56	1347.56		      1347.56
5	1333.34	1333.34	1333.34	1333.34
8	1298.35	1298.35	1298.35	1298.35
9	1295.86		      1295.86	1295.86

The two most important aspects of this are:  

1)The group names are retained.  Question: Could the syntax accommodate if
the group was identified by a label, e.g., "Group A", and label was to be
used as in the column name?

2) The syntax can deal with the fact that some groups might not be fully
populated across the days, as you can see from the two dropped cases above
("11/11" and "21/21").

Any ideas? 


Ben


Ben Hoen
LBNL
Office: 845-758-1896
Cell: 718-812-7589
bhoen@lbl.gov



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