Hi.

EmpCode OfficeCode Year Sept_30 In Out Existing
1 11 2011-12 . .
1 12 2011-12 1 . 1 .
1 12 2012-13 . . . .
1 13 2013-14 . . . .
1 14 2013-14 1 1 . .
1 14 2014-15 1 . . 1
1 14 2015-16 1 . . 1

Description of the variables :

EmpCode , OfficeCode identify the employee and the office she works in.

Sept_30 takes a value 1 if the employee was in that office on 30th September of that year.

For example, employee 1 is at office codes 11 and 12 in 2011-12 but on 30 Sept, 2011, the employee is at office 12. Similarly on 30 Sept, 2013, 30 Sept, 2014, 30 Sept, 2015, the employee is at office 14.



Variables to be created:

I want to create the last three columns ( In, Out, Existing ) :


In takes a value 1 if the employee was in the same office on 30th Sept of last year as the one on this year 30th Sept.

Out takes a value 1 if the employee is in a particular office this year but leaves the next year .

Existing takes a value 1 if the employee was in the same office last year on 30 September as well.


How do I create the above three variables ?