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.
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
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
Thank you.
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!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.