BookmarkSubscribeRSS Feed
tblock
Calcite | Level 5

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!

3 REPLIES 3
Astounding
PROC Star

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
Jade | Level 19

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.

SAS Innovate 2025: Call for Content

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!

Submit your idea!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 696 views
  • 0 likes
  • 4 in conversation