Stata. How to transform a dataset into pure panel data?

Question

I have done this many times with Excel and Java... This time I need to do it using Stata because it is more convenient to preserve variables' labels. How can I restructure dataset_1 into dataset_2 below?

I need to transform the following dataset_1:

into dataset_2:

I know one way, which is a little awkward... I mean, I could expand all the observations, then create variable obsNo, and then, rename variables...is there any better way?

Answer 1

Stata is wonderful at this sort of thing, it's a simple reshape. Your data is a little awkward, as the reshape command was designed to work with variables where the common part of the variable name (in your case, Wage) comes first. In the documentation for reshape, "Wage" would be the stub. The part following Wage is required to be numeric. If you first sort your variable names by

rename (raceWhiteWage raceBlackWage raceAsianWage) (Wage1 Wage2 Wage3)

Then you can do:

reshape long Wage, i(state year) j(race)

That should give you the output your are looking for. You will have a column labeled "race", with values of 1 for White, 2 for Black, and 3 for Asian.