reshape quarterly data

[Simon Falck]

Dear all, I have a small question regarding reshaping a dataset from wide to long structure, and having some problems in getting the quarters and years correct.

My dataset considers quarterly unemployment data for a set of countries by sex, age and education groups, and look something like this,

id sex age edu y2011q4 y2012q1 y2012q2 y2012q3 y2012q4 y2013q1 y2013q2 y2013q3 y2013q4 y2014q1
114 0 -19 1 1 2 3 2 2 2 3 1 0 3
114 0 -19 2 0 0 0 0 0 0 0 0 0 0
114 0 -19 3 1 4 4 2 3 7 6 3 3 7
114 0 -19 4 0 0 0 0 0 0 0 0 0 0
114 0 -19 5 0 0 0 0 0 0 1 1 1 0
114 0 20-24 1 10 11 13 21 16 16 14 17 23 22
114 0 20-24 2 0 0 0 0 0 0 0 0 0 0
114 0 20-24 3 22 29 32 32 25 31 36 40 38 40
114 0 20-24 4 0 0 0 0 0 0 0 0 0 0
114 0 20-24 5 2 2 1 1 0 0 2 2 2 4
114 0 25-29 1 16 15 18 17 14 14 11 11 13 12
..and so on...

My aim is a dataset that look something like this,

id sex age edu year unempl
114 0 -19 1 2011q4 1 114
..and so on...


My question is how I get the reshape command to do this?

Thanks in advance,
/Simon

[Clyde Schechter]

So I think you want something like this:

reshape long y, i(id age sex edu) j(_quarter) string
gen quarter = quarterly(_quarter, "YQ")
format quarter %tq
drop _quarter
rename y unempl

A couple of notes: I created the variable with the name quarter rather than year as that seemed more descriptive of your actual situation. I include age sex and edu within the i() option of the reshape command because they vary within id (or, at least, age and edu do). Also, I note that the first several observations have a negative age: is that an error?

Hope this helps.

[Simon Falck]

Thanks Clyde, your script works very well.

Best,
//Simon