How to generate a dummy variable =1 if the waitress in a particular hotel is serving as a receptionist for another hotel/hotels same year.

[Mo Hos]

I would like to create a dummy variable (equals 1 and 0 otherwise) if the waitress in a particular hotel is serving as a receptionist for another hotel/hotels in the same year. I am only interested in waitresses who are serving as a receptionist in other hotels (if any) and not vice versa. Note that waitresses can not take more than one receptionist position but waitresses can take multiple waitresses positions in other hotels in the same year. All staff are allocated with a unique ID regardless of being a waitress or receptionist. To identify observations with the same staff_ID and year, I have generated a new ID (ID_generated) (this is generated via excel using the CONCATENATE command. If you know how to do it in STATA, please let me know). I have a large number of observations and I am wondering if there are any commands to create such dummy variables.

In the following example, you can notice that in 2009, the staff No. 124665 served as a waitress in a hotel and a receptionist in another hotel in the same year (I would like to identify this as 1):

Year | Staff ID | Hotel_ID | ID_generated | Waitress | Receptionist
2009 | 124665 | 23453 | 2009124665 ______1________ 0
2010 | 436455 | 34534 | 2010436455______ 0 ________1
2002 | 543645 | 37684 | 2002543645______ 0 ________1
2009 | 124665 | 32478 | 2009124665______ 0________1
2009 | 979987 | 34545 | 2009979987______ 0 ________1
2013 | 097077 | 34556 | 2013097077______ 1_________0

[Wouter Wakker]

There may be a more efficient way, but this should work.

bysort staff_ID year: egen aux1 = max(Waitress)
bysort staff_ID year: egen aux2 = max(Receptionist)
gen wanted = aux1 & aux2

[Andrew Musau]

Wouter Wakker's solution is the natural way to think about this problem, but it will not lead to the desired result if a waitress is also a receptionist in the same hotel. The following exploits the sorting order.

gen tag=-Receptionist
bys staff_ID year (Hotel_ID Waitress tag): egen wanted= max(Waitress==1 & tag[_n+1]==-1|tag==-1 & Waitress[_n+1]==1)

[Mo Hos]

@Wouter Wakker. Can you briefly explain the code, please?

[Mo Hos]

@Andrew Musau. In my sample, the waitress can not serve in the same hotel as a receptionist. Additionally, the waitresses can not take more than one receptionist position but waitresses can take multiple waitresses positions in other hotels in the same year. Therefore, a single employee in a particular hotel can not be a waitress and receptionist at the same time.

[Andrew Musau]

bys staff_ID year (Hotel_ID Waitress tag)

First define groups by staff_ID and year, and within these groups, sort by Hotel_ID, Waitress and tag. Because Stata sorts the smallest numbers first, e.g., 0 before 1 and -1 before 0, I specify the condition

(Waitress==1 & tag[_n+1]==-1|tag==-1 & Waitress[_n+1]==1)

If the observations below and above have the order "Waitress=1 & tag=-1", assign a value of 1 and 0 otherwise. Because I assume combinations of staff_ID-Hotel_ID per individual define different hotels, and taking into account how Stata sorts numbers, if I observe the defined order, then it means that the individual is a waitress in one hotel and a receptionist in another. Finally, the -max()- function of egen will assign a value of 1 to all observations in that group if the condition is met and 0 if not.

See https://www.stata.com/support/faqs/d...ble-recording/ for an excellent elaboration.

[Andrew Musau]

@Andrew Musau. In my sample, the waitress can not serve in the same hotel as a receptionist. Additionally, the waitresses can not take more than one receptionist position but waitresses can take multiple waitresses positions in other hotels in the same year. Therefore, a single employee in a particular hotel can not be a waitress and receptionist at the same time.

Then Wouter Wakker's solution works perfectly.