How do I create variable to indicate a binary variable changes from 0 to 1, from year 1 to year 2?

[tblock]

How do I create variable to indicate a binary variable changes from 0 to 1, from year 1 to year 2 in SAS 9.4?   My binary variable of interest is manager_fem = 1 if manger is a female and 0 otherwise.

Here is the data I have.

Company ID     Year     manager_fem

1234                   1              0

1234                   2              0

1234                   3              1

1234                   4              1

4567                   1              0

4567                   2              1

4567                   3              1

4567                   4              1

Here is the data I want.

Company ID     Year     manager_fem       fem-change

1234                   1              0                      0

1234                   2              0                      0

1234                   3              1                      1

1234                   4              1                      0

4567                   1              0                      0

4567                   2              1                      1

4567                   3              1                      0

4567                   4              1                      0

Thank you in advance for any help you provide!

[Astounding]

Why should Company 1234 have a 1?  The change occurs in year 3, not in year 2.

What if there is more than one change (see below)?  What would you want as the result here?

Company ID     Year     manager_fem

1234                   1              0

1234                   2              0

1234                   3              1

1234                   4              1

4567                   1              0

4567                   2              1

4567                   3              0

4567                   4              1

[FreelanceReinh]

Hello @tblock and welcome to the SAS Support Communities!

If your input dataset is sorted by company ID and year (like your sample data), you can use the DIF function to detect an increase of manager_fem by 1 (assuming a numeric variable). This implies a change from 0 to 1 for a binary variable. An additional check (using BY-group processing) is necessary to avoid an incorrect fem_change=1 if manager_fem=1 in the first year and manager_fem=0 in the last year of the previous company:

data have;
input Company_ID Year manager_fem;
cards;
1234 1 0
1234 2 0
1234 3 1
1234 4 1
4567 1 0
4567 2 1
4567 3 1
4567 4 1
;

data want;
set have;
by company_id year;
fem_change=(dif(manager_fem)=1 & ~first.company_id);
run;

Note that variable fem_change is assigned a Boolean value: 1 if the increase was detected, but not (~) in the first observation of a company, and 0 otherwise.

A more defensive definition of fem_change would be

fem_change=(lag(manager_fem)=0 & manager_fem=1 & ~first.company_id);

checking explicitly that manager_fem changed from 0 to 1.

Either way, missing values of manager_fem (in the current or previous year) would imply fem_change=0. With additional code fem_change could be set to missing in some of these situations.

[Kurt_Bremser]

A slightly different take:

data want;
set have;
by company_id;
fem_change = manager_fem ne lag(manager_fem);
if first.company_id then fem_change = 0;
run;