Identification of waitresses who previously worked in particular roles

[Mo Hos]

I would like to define a waitress, and not receptionist, (identified by employee ID and a dummy variable for her/his position) as an industry specialist (equal to 1) if the firm where s/he is a "waitress" shares the same industry code (identified by two digits) with at least one firm in his employment history as a "receptionist". A worker in the year 2004, for example, would be an industry specialist if s/he worked in a firm that shares the same industry code prior to 2004 (and of course not after). Therefore, the employee identified as "1" is an industry specialist in "2001" in "firm2" (and not industry expertise prior to 2001) as shown in the following table:

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte emp_id str6 company_id int year byte ind_code byte(waitress receptionist)
1 "firm1"  1997 20 1 0
1 "firm1"  1998 20 1 0
1 "firm1"  2000 20 1 0
1 "firm5"  1997 10 0 1
1 "firm5"  1998 10 0 1
1 "firm5"  2000 10 0 1
1 "firm5"  2002 10 0 1
1 "firm3"  2004 30 1 0
1 "firm5"  2004 30 0 1
1 "firm2"  2001 10 1 0
1 "firm5"  2001 10 0 1
1 "firm2"  2002 10 1 0
1 "firm4"  2008 10 1 0
1 "firm1"  2009 20 0 1
1 "firm2"  2009 10 0 1
2 "firm7"  2003 40 1 0
2 "firm6"  2007 20 0 1
2 "firm5"  2009 20 1 0
2 "firm8"  2009 40 1 0
2 "firm7"  2009 40 1 0
2 "firm7"  2010 40 1 0
2 "firm6"  2012 20 1 0
2 "firm6"  2013 20 1 0
2 "firm9"  2013 20 0 1
3 "firm11" 2001 50 1 0
3 "firm12" 2001 50 0 1
3 "firm13" 2002 50 1 0
3 "firm10" 2002 50 1 0
3 "firm9"  2006 20 0 1
3 "firm8"  2007 40 1 0
4 "firm13" 1999 30 0 1
4 "firm13" 2000 30 0 1
4 "firm12" 2001 30 1 0
4 "firm14" 2001 30 1 0
5 "firm14" 1997 20 0 1
5 "firm15" 1999 40 1 0
5 "firm18" 2001 20 1 0
5 "firm17" 2006 20 0 1
5 "firm16" 2007 40 1 0
end

I have tried the following code but this code identifies industry specialists despite their position (i.e. waitresses or receptionists). Again, I am only interested in waitresses who worked as receptionists in the same industry before.

bysort employeeID IndustryCode (Year): gen tag = companyId != companyId[_n-1] & _n > 1
bysort employeeID IndustryCode (Year): gen wanted = sum(tag) >= 1

In short, the following should be identified as industry specialists:

 1 "firm2"  2001 10 1 0
1 "firm4"  2008 10 1 0
2 "firm5"  2009 20 1 0
2 "firm6"  2012 20 1 0
2 "firm6"  2013 20 1 0
3 "firm13" 2002 50 1 0
3 "firm10" 2002 50 1 0
4 "firm12" 2001 30 1 0
4 "firm14" 2001 30 1 0
5 "firm18" 2001 20 1 0

[Phil Bromiley]

In your second generate statement, you can simply add another if condition so it's sum of tag equal one or something.
bysort emp_id ind_code (year): gen wanted = sum(tag==1)

I'm not sure this is exactly what you want, but it should show you how to get there.

[Mo Hos]

Thanks for your response.

This is my original code:

bysort emp_id ind_code (year): gen tag = company_id != company_id[_n-1] & _n > 1
bysort emp_id ind_code (year): gen wanted = sum(tag) >= 1

This is your suggested code:

bysort emp_id ind_code (year): gen tag = company_id != company_id[_n-1] & _n > 1
bysort emp_id ind_code (year): gen wanted = sum(tag==1) >= 1

I added this as I am only interested in waitresses:

replace wanted=0 if wanted==1 & receptionist==1

I have encountered two issues:

Firstly, the following should not be regarded as industry specialists just because they are working as waitresses and specialists in the same year and industry. I am only interested in the years before the current year and not the current year to identify industry specialists.

1 "firm3"  2004 30 1 0
3 "firm11"  2001 50 1 0

Secondly, I do not understand why the following are regarded as industry specialists even though they did not work as receptionists in the same industry before

2 "firm8"  2009 40 1 0
2 "firm7"  2009 40 1 0
2 "firm7"  2010 40 1 0
5 "firm16"  2007 40 1 0

[Mo Hos]

I am still waiting for suggestions.

[Wouter Wakker]

This requires rangestat from SSC. Note that it doesn't take into account whether a waitress worked as a receptionist before in the same firm or in a different firm.

* ssc install rangestat // remove asterisk if not already installed

// Tag if employee was receptionist in the same industry before
rangestat (max) recep_before=receptionist, by(emp_id ind_code) i(year . -1)
recode recep_before miss = 0

// Wanted variable
gen wanted = recep_before & waitress

[Mo Hos]

Thank you very much @Wouter Wakker. The code is working perfectly. I appreciate your help.