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!
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
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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.