Creating a column that tells whether a director is a board industry expert

[Ties]

Hi all,

I use data from BoardEx and Compustat to construct a variable called board industry expertise: "defined as a director is an industry expert if the firm where he/she is a board member shares the same two-digit SIC code with at least one firm in his/her employment history". So I merged the employment data from BoardEx with the SIC codes from Compustat, and transformed the 4 digit SIC codes (industry code) into 2 digit codes. I'm trying to create a column that has a value Yes or No per director, per year.

So when the sic2 code of a director is similar to the sic2 code of his/her previous employment for a different gvkey (firm identifier), the value of Yes (or true) should appear. I tried the following, but this does not give me the right values. Any ideas? Very much appreciated!

data_expertise21 <- data_expertise20 %>%
  group_by(DirectorID) %>%
  arrange(AnnualReportDate) %>%
  mutate(industry_expert %in% any(sic2 = lag(sic2) & !is.na(lag(sic2)))) %>%
  ungroup()

Below is a part of the dataset

data <- data.frame(
  AnnualReportDate = c(1999, 2004, 2010, 1999, 1999, 1999, 1999, 2000, 1999, 1999, 1999, 2011, 2001, 2000, 2000, 2009, 2006, 2007, 2011, 2012, 2013, 2016, 1999, 1999, 1999, 2004, 2006, 1999, 2007),
  gvkey = c(24197, 122143, 184256, 8902, 2137, 2526, 5903, 7980, 120134, 61685, 134031, 126554, 63525, 4839, 2005, 3243, 174130, 12788, 6268, 14438, 2403, 8972, 3310, 9483, 3231, 25536, 4352, 10443, 126734),
  DirectorID = c(16, 16, 16, 66, 264, 264, 264, 264, 277, 277, 277, 282, 332, 334, 338, 338, 356, 356, 356, 356, 356, 356, 397, 397, 400, 400, 415, 418, 432),
  sic2 = c(35, 11, 34, 90, 13, 90, 11, 95, 72, 72, 72, 26, 72, 11, 20, 99, 13, 51, 85, 80, 34, 12, 72, 63, 20, 40, 74, 14, 72)

[technocrat]

@Ties

sic2 = lag(sic2)

Do you mean

==

the equality operator?

[Ties]

Thank you for the response! I had to tweak a little bit more, but now ended up with the correct outcomes. Kind regards, Ties

data_expertise <- data_expertise %>%
  arrange(DirectorID, AnnualReportDate, gvkey) %>%
  group_by(DirectorID) %>%
  mutate(industry_expert = ifelse(sic2 == lag(sic2) & !is.na(lag(sic2)), TRUE, FALSE)) %>%
  ungroup()

^{Created on 2023-05-20 with reprex v2.0.2}