Help using Base SAS procedures

Code to delete firms

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 96
Accepted Solution

Code to delete firms

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.


Accepted Solutions
Solution
‎11-11-2013 11:06 PM
Respected Advisor
Posts: 3,124

Re: Code to delete firms

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

View solution in original post


All Replies
Solution
‎11-11-2013 11:06 PM
Respected Advisor
Posts: 3,124

Re: Code to delete firms

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

Frequent Contributor
Posts: 96

Re: Code to delete firms

Thank you.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 172 views
  • 0 likes
  • 2 in conversation