st: transform data from spell format into ordinary panel data

[altruist81@gmx.de]

Dear Statalist Users,
 
the title pretty much describes my problem:

I have a data set that contains persons and their employment episodes in the following format which I'm used to call "spell format " (not sure if thats a common expression (?). It is structured as follows:


Person-ID | Emploment-Episode-ID | start | end | Income | sector? | hrsperweek ...

Any person can have multiple emploment spells, each with start, end, income, hoursperweek worked and a bunch of more variables. Moreover, the durations of the employment states can vary across and within persons.

The date is not in a typical day-month-year format,  but represented by a number that represents the time elapsed since 1970/01/01.


It looks like this then:

1 1 502 503 3.500 € public sector 42 hrsperweek
1 2 504 604 3.900 € public sector 42 hrsperweek

2 1 502 555 2.200 € private sector 20 hrsperweek
2 2 556 600 4.000 € private sector 42 hrsperweek
2 3 601 604 4.500 € private sector 40 hrsperweek

3 1 550 553 1.500 € self-employed 60 hrspwerweek


I hope you can see that not necessarily the whole time period is covered, there can be gaps in which persons have been unemployed or studying or whatever.

I would like to transform this data into something like a standard balanced panel dataset which gives me the state for every person in every month over the whole period (in this example the period 502-604). In particular it should look like this:

Month | Person-ID | Emploment-Episode-ID | Income | sector? | hrsperweek ...

In the end it shold be a HUGE data file looking like this:

502 1 1 ...
502 2 1 ...
502 3 -
503 1 1 ...
503 2 1 ...
503 3 -
504 1 2 ...
504 2 1 ...
504 3 -

and so on.

 
I looked into statas survival capabilities, but am not sure if those are really helpful here.
 
Can anyone tell me how to approach my problem??
 
Many thanks!
 
Darjusch Tafreschi
 
 

*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/faqs/resources/statalist-faq/
*   http://www.ats.ucla.edu/stat/stata/