Code to delete firms

[shalmali]

Hi I have a data set and would like to remove certain firms. The sample has firm identifier, year , and exec_fullname. I want to create a new sample where there is no executive change between 2004 and 2012.  In other words, my final sample should start from year 2004 and there should not be any change in the executive.

For instance, a subset of my data looks as follows:

Tic       Year     Exec_fullname

A         2003    Mr. John

A         2004    Mr. John

A         2005    Mr. John

A         2006    Mr. John

A         2007    Mr. John

A         2008    Mr. John

A         2009    Mr. John

A         2010    Mr. John

A         2011    Mr. John

A         2012    Mr. John

B          2002    Mr. Smith

B          2003    Mr. Smith

B          2004    Mr. Smith

B          2005    Mr. Smith

B          2006    Mr. Smith

B          2007    Mr. Smith

B          2008    Mr. Thompson

B          2009    Mr. Thompson

B          2010    Mr. Thompson

B          2011    Mr. Thompson

B          2012    Mr. Thompson

Output

Tic       Year     Exec_fullname

A         2004    Mr. John

A         2005    Mr. John

A         2006    Mr. John

A         2007    Mr. John

A         2008    Mr. John

A         2009    Mr. John

A         2010    Mr. John

A         2011    Mr. John

A         2012    Mr. John

I would appreciate if someone provide me with the code.

Thank you.

[Haikuo · Accepted solution]

Proc SQL seems straightforward here:

data have;

  input Tic :$      Year     Exec_fullname :&$20.;

cards;

A         2003    Mr. John

A         2004    Mr. John

A         2005    Mr. John

A         2006    Mr. John

A         2007    Mr. John

A         2008    Mr. John

A         2009    Mr. John

A         2010    Mr. John

A         2011    Mr. John

A         2012    Mr. John

B          2002    Mr. Smith

B          2003    Mr. Smith

B          2004    Mr. Smith

B          2005    Mr. Smith

B          2006    Mr. Smith

B          2007    Mr. Smith

B          2008    Mr. Thompson

B          2009    Mr. Thompson

B          2010    Mr. Thompson

B          2011    Mr. Thompson

B          2012    Mr. Thompson

;

proc sql;

create table want as

   select * from have

    where year between 2004 and 2012

  group by tic

  having count (distinct Exec_fullname) =1

  order by tic, year

;

quit;

Haikuo

[shalmali]

Thank you.