How do i split a stacked variable?

[Martin Borg]

I'm new to Stata, and I have a dataset of monthly stock returns over the last 10 years for a lot of companies, but all the data is stacked like:

month1	firm1	return
month2	firm1	return
...	...	...
month120	firm1
month1	firm2
...
month120	firm2
month1	firm3
...	...	...
and so on

Is there an easy way to reorder the data like:

date	firm1	firm2	...	...
month1	return	...	...	...
month2	return	...	...	...
...	...	...	...	...
month120	...	...	...	...

[Clyde Schechter]

Well, maybe, maybe not. If the values of the firm variable are also legal Stata variable names (contain only digits, letters, and underscore _ characters, and no embedded blanks) then it can be done. I assume that your months are in a variable called date, and your firms are in a variable called firm, and your returns are in a variable called return.

reshape wide return, i(date) j(firm) string
rename return* *

If the firm names are not legal Stata variable names (and my guess is that they aren't), you can approximate what you want by first running

replace firm = strtoname(firm, 1)

and then running the above.

Note: This code assumes, and will only run, if there is at most one observation for each combination of date and firm. If that is not the case, please post back with an example of your actual data for further advice.

All of that said, you probably shouldn't do this. If all you are looking to do is create a display that is friendly to human eyes, or perhaps some graphs, then fine. But if you plan to any analysis on this data, the wide layout you are asking for will only get in the way. The long layout you have is far more suitable for nearly all analysis purposes.

As an aside, the HTML table layouts you show are fine for giving a vague sense of what you want. But if your needs were more complicated and required somebody to experiment a bit with your data, these examples would have been unsuitable, and also would have been difficult to import into Stata. The helpful way to show example data is with the -dataex- command. Run -ssc install dataex- to install that command, and then run -help dataex- to see the simple instructions for using it. By using -dataex- you enable those who want to help you to easily import your data into Stata with a simple copy/paste operation and end up with a 100% faithful replica of what you are working with.

[Martin Borg]

Thanks.
You were right about the firm names not being legal variables, but I also had a variable with a number of the company, so the reshape wide command worked just fine without any additions when I dropped the names and used the numbers:

reshape wide ret, i(date) j(permno)