Dear all,

I have a question that I have been struggling with in the past few days. I have read the documentation, and although I see parts that can help me accomplish this, I cannot seem to put these together and create a command that does what I want. I will try to explain it the best that I can, and I hope that someone can share their knowledge.

I have the following database:
Code:
input str12 Firm int Year double WorkerID str78 Experience
"JE00BD3QJR55" 2014 31 "Management"                
"JE00BD3QJR55" 2014 23 "Employee"           
"JE00BD3QJR55" 2014 34 "Warehouse"                        
"JE00BD3QJR55" 2014 54 "Executive"
"JE00BD3QJR55" 2014 12 "Management"                                 
"JE00BD3QJR55" 2015 31 "Management"
"JE00BD3QJR55" 2015 23 "Employee"
"JE00BD3QJR55" 2015 34 "Warehouse"           
"JE00BD3QJR55" 2015 54 "Employee"           
"JE00BD3QJR55" 2015 12 "Management"           
"JE00BD3QJR55" 2015 64 "Executive"                                 
"KF25CI1FJR491" 2016 31 "Management"                        
"JE00BD3QJR55" 2016 23 "Employee"           
"JE00BD3QJR55" 2016 34 "Warehouse"           
"JE00BD3QJR55" 2016 54 "Executive"
"JE00BD3QJR55" 2016 12 "Management"           
end
I want to create a new variable that indicates which WorkerID has prior management or executive experience. Taking WorkerID 31 (which has management experience) as example, he/she would be assigned a 0 since there is no data prior to 2014 indicating the worker has management experience. In the year 2015 however, the WorkerID 31 would be assigned a 1, since previous management experience has been found in the year 2014. Therefore, I assume that I need to perform some type of textual analysis via strpos or strmatch to find the instances of 'Management' and 'Executive', and see if a WorkerID has had management/executive experience. Note that Worker ID 54 has had executive experience in the year 2014. However, in 2015 the worker has returned to an employee position. This person would then still be assigned a 1, since I want to capture if a Worker has had this experience prior to their current experience/role.

I then, want to sum the variables on firm-year level. So, in the case of JE00BD3QJR55-2014, a 0 would be assigned. In the case of JE00BD3QJR55-2015, however, a 3 would be assigned (because WorkerIDs 31, 54, and 12 had experience as Management and/or Executive in the year 2014). In the case of KF25CI1FJR491-2016, which is a different firm, a 1 would be assigned, since WorkerID 31 has been found previously in the data in a different firm with management experience.

I did my best to explain in detail what I want. I hope someone can share their knowledge on how to do this.

Thank you for your time reading this.

Best regards,
Ferry