Creating a dummy conditional on some variables which have repeated values (Panel Data)

[Gio Bianchi]

Hello everyone, as the title says, I am having difficulties in understanding how to create a dummy variable based on panel data. My database gathers data about employees’ recruitments and layoffs. The scenario is the following:

• YEAR is a long variable referring to the observation year
• EMPLOYEE_ID is a float variable to anonymously identify a person who’s been hired
• COMPANY_ID is also an anonymizing float variable identifying a company
• HIRING_DATE is also a float, and it is displayed with time format
• DISMISSAL_DATE is float and displayed with time format
• OPEN is a dummy variable with value 1 if the hiring is based on an open-ended contract, and 0 in case of a fixed-term contract.

What I need to do is generating a dummy variable called “ELIGIBLE”, when a hiring date is present, that takes value:

• 1, in case an employee was either dismissed or NOT in possession of an open-ended contract in the SIX (6) months prior to hiring
• 0, if the worker had any open-ended contract during at least one day of the 6 months preceding the recruitment
• Missing, in case it is not possible to verify so, for instance in case the observation in question is the earliest hiring reported for that employee, or in case the observation relates to a dismissal only.

It is assumed that every hiring has been reported in the database (i.e., after a dismissal the employee is unemployed until a new hiring). If a dismissal date is not specified, it means that the employee has kept the job until the following hiring.
As an example of what I am trying to do:

EMPLOYEE_ID YEAR COMPANY_ID HIRING_DATE DISMISSAL_DATE OPEN ELIGIBLE

1 2016 1111 January 1st 2016 . 1 .
1 2018 1111 . June 1st 2018 1 .
1 2018 2222 July 2nd 2018 . 0 0
1 2019 2222 January 7th 2019 . 1 1

2 2016 4444 May 4th 2016 July 1st 2016 1 .
2 2016 5555 August 4th 2016 . 0 0
2 2017 9999 May 1st 2017 . 1 1

3 2011 9999 October 22nd 2011 . 1 .
3 2017 9999 . November 20th 2017 1 .
3 2017 3333 November 28th 2017 . 0 0


Thank you very much in advance for any helpful suggestion

[Gio Bianchi]

Update: a dummy variable has been created under the name of NOT_ELIGIBLE, which seemed to make things easier in the code, so values 0 and 1 are going to be assigned conversely than what I was discussing above.
Also, I apologize for not writing any code in the first post. Here you can find my idea.

Unfortunately, I get nothing as a result.

levelsof EMPLOYEE_ID, local(levels)
foreach l of local levels {
 
levelsof HIRING_DATE, local(date)
foreach a of local date {
{
 
forvalues t = 2019/2009 {
 
NOT_ELIGIBLE = 0 if EMPLOYEE_ID == `l' & HIRING_DATE==`a’ & YEAR ==`t’
 
forval i = `t’/2009 {
 
NOT_ELIGIBLE=1 if EMPLOYEE_ID == `l' & HIRING_DATE!= `a’ & YEAR ==`i’ & OPEN==1 & (((missing(DISMISSAL_DATE)& `i’<`t’)| ((!missing(DISMISSAL_DATE)& ((HIRING_DATE - DISMISSAL_DATE)<181)))
 
}
 
}
 
}
 
}

Thinking that the error could be due to the repetition of two "levelsof" loops, I have tried to use "foreach" instead (with the min and max values of HIRING_DATE), but besides potentially making execution time extremely longer, an error occours that says "invalid numlist has too many elements".

levelsof EMPLOYEE_ID, local(levels)
foreach l of local levels {
 
foreach a of num 18922/ 21556 {
 
forvalues t = 2019/2009 {
 
NOT_ELIGIBLE = 0 if EMPLOYEE_ID == `l' & HIRING_DATE==`a’ & YEAR ==`t’
 
forval i = `t’/2009 {
 
NOT_ELIGIBLE=1 if EMPLOYEE_ID == `l' & HIRING_DATE!= `a’ & YEAR ==`i’ & OPEN=1 & (((missing(DISMISSAL_DATE)& `i’<`t’)| ((!missing(DISMISSAL_DATE)& ((HIRING_DATE - DISMISSAL_DATE)<181)))
 
}
 
}
 
}
 
}

How can I loop levelsof twice?
Thank you very much