topic Re: Code to delete firms in SAS Procedures

Code to delete firms

shalmali — Tue, 12 Nov 2013 03:54:20 GMT

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.

Re: Code to delete firms

Haikuo — Tue, 12 Nov 2013 04:06:02 GMT

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

Re: Code to delete firms

shalmali — Tue, 12 Nov 2013 04:22:36 GMT

Thank you.