Need code to create a new sample from the original sample

[shalmali]

Dear All,

I need your help in creating a new sample out of my current sample. My original sample has information about the firm (tic), year, and CEO. The sample period range from 2003 to 2013. I am trying to create a new sample that meets the following requirements:

1) the sample period should be from 2005 to 2012.

2) include only those firms that changed CEO only once and ceo should be changed between 2007 and 2010.

FOr instance if my original sample is as follows:

TIc year ceo

A 2003 Tim

A 2004 Tim

A 2005 Tim

A 2006 Tim

A 2007 Tim

A 2008 John

A 2009 John

A 2010 John

A 2011 John

A 2012 John

A 2013 John

B 2003 Lin

B 2004 Lin

B 2005 Lin

B 2006 Sam

B 2007 Sam

B 2008 Sam

B 2009 Sam

B 2010 Sam

B 2011 Sam

B 2012 Sam

B 2013 Sam

C 2003 Dan

C 2004 Dan

C 2005 Dan

C 2006 Dan

C 2007 Dan

C 2008 Dan

C 2009 Dan

C 2010 Dan

C 2011 Jack

C 2012 Jack

C 2013 Jack

D 2003 Ali

D 2004 Ali

D 2005 Ali

D 2006 Ali

D 2007 Bill

D 2008 Bill

D 2009 Bill

D 2010 Frank

D 2011 Frank

D 2012 Frank

D 2013 Frank

IN the above example, A, B, and C has only one CEO change. However CEO was changed for firm B in 2006 and for firm C in 2011. So I dont want to keep B and C in my new sample. Firm D has two ceo change. So I want to exclude it too. In other words, my new sample should be

A 2005 Tim

A 2006 Tim

A 2007 Tim

A 2008 john

A 2009 John

A 2010 John

A 2011 John

A 2012 John

I would appreciate if someone can share  the code with me that will help me create the desired sample.

THank you

[Haikuo · Accepted solution]

data have;

input TIc $ year ceo$;

cards;

A 2003 Tim

A 2004 Tim

A 2005 Tim

A 2006 Tim

A 2007 Tim

A 2008 John

A 2009 John

A 2010 John

A 2011 John

A 2012 John

A 2013 John

B 2003 Lin

B 2004 Lin

B 2005 Lin

B 2006 Sam

B 2007 Sam

B 2008 Sam

B 2009 Sam

B 2010 Sam

B 2011 Sam

B 2012 Sam

B 2013 Sam

C 2003 Dan

C 2004 Dan

C 2005 Dan

C 2006 Dan

C 2007 Dan

C 2008 Dan

C 2009 Dan

C 2010 Dan

C 2011 Jack

C 2012 Jack

C 2013 Jack

D 2003 Ali

D 2004 Ali

D 2005 Ali

D 2006 Ali

D 2007 Bill

D 2008 Bill

D 2009 Bill

D 2010 Frank

D 2011 Frank

D 2012 Frank

D 2013 Frank

;

proc sql;

  create table want as 

  select * from have (where = (year between 2005 and 2012))

WHERE TIC IN (select TIC from have (where = (year between 2007 and 2010))

group by tic

having count(distinct ceo)=2)

group by tic

having count(distinct ceo)=2

ORDER BY TIC, YEAR

;

QUIT;

Haikuo

[shalmali]

Thanks a lot for your prompt reply.