topic Re: How do I create variable to indicate a binary variable changes from 0 to 1, from year 1 to year in SAS Programming

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

tblock — Sun, 05 Mar 2023 18:56:26 GMT

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!

Re: How do I create variable to indicate a binary variable changes from 0 to 1, from year 1 to year

Astounding — Sun, 05 Mar 2023 21:39:57 GMT

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

Re: How do I create variable to indicate a binary variable changes from 0 to 1, from year 1 to year

FreelanceReinh — Sun, 05 Mar 2023 21:40:13 GMT

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.

Re: How do I create variable to indicate a binary variable changes from 0 to 1, from year 1 to year

Kurt_Bremser — Mon, 06 Mar 2023 10:55:55 GMT

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;