Re: st: from spell data (with overlaps) to panel data

[Nick Cox <njcoxstata@gmail.com>]

Interesting question.

I am not convinced that the -reshape- implied is in your own best
interests, as further reduction and analysis would then imply
operations across rows.

Getting part of the way there, as here

clear
input workerID str5 Start str5 End Jobid str3 Job_feature1 str3 Job_feature2
1 2Jan 30Dec 55385 Yes No
1 5Jun 2Aug 3254 No No
2 3Apr 5Oct 9936 Yes Yes
2 7Jul 31Jul 39995 Yes Yes
end

gen date1 = daily(Start + "2013", "DMY")
gen date2 = daily(End + "2013", "DMY")
gen q1 = qofd(date1)
gen q2 = qofd(date2)
expand q2 - q1 + 1
bysort workerID Jobid (q1): replace q1 = q1[_n-1] + 1 if _n > 1
replace date1 = max(date1, dofq(q1))
replace date2 = min(date2, dofq(q1 + 1) - 1)
format date? %td

might allow more to be done more easily.

edit
Nick
njcoxstata@gmail.com


On 17 February 2014 15:40,  <altruist81@gmx.de> wrote:
> Dear Statalist Members,
>
> I have a dataset for a given year (e.g.  1999) where I observe job/employment spells for workers. As you may expect, some workers have multiple spells during a year, some of them even overlap. I want to create a quarterly panel dataset (i.e. in the end there should be 4 observations per worker since the data covers one year). I would know how to do this if there was no overlaps of spells  - but the overlaps complicate matters as I want to keep the information of the second jobs too. I understand that using -spell2panel- is no option as I would lose information... the spells contain qualitative information and I wouldn't know how to combine these information into new spells by means of (sum) or (mean).
>
> To illustrate my aim:
>
> This is the original data....
>
> workerID        Start   End     Jobid   Job_feature1    Job_feature2
> 1       2nd January     30th December   55385   Yes     No
> 1       5th June        2nd August      3254    No      No
> 2       3rd April       5th October     9936    Yes     Yes
> 2       7th July        31st July       39995   Yes     Yes
>
> This is what I would like the data to look like....
>
> workerID        Quarter Working (yes/no)        1st Job Start   1st Job end     1st Jobid       1st Job Feature1        1st Job Feature2        2nd Job Start   2nd Job end     2nd Jobid       2nd Job Feature1        2nd Job Feature2        Multiple jobs (yes/no)
> 1       1       1       2nd January     31st March      55385   Yes     No      -       -       -       -       -       0
> 1       2       1       1st April       30th June       55385   Yes     No      5th June        30th June       3254    No      No      1
> 1       3       1       1st July        30st September  55385   Yes     No      1st July        2nd August      3254    No      No      1
> 1       4       1       1st October     30th December   55385   Yes     No      -       -       -       -       -       0
> 2       1       0       -       -       -       -       -       -       -       -       -       -       0
> 2       2       1       3rd April       30th of June    9936    Yes     Yes     -       -       -       -       -       0
> 2       3       1       1st July        30th September  9936    Yes     Yes     7th July        31st July       39995   Yes     Yes     1
> 2       4       1       1st October     5th October     9936    Yes     Yes     -       -       -       -       -       0
>
>
> Do you have any suggestions how to use stata to make that transformation?
>
> Many thanks in advance!
>
> 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/

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