BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
shalmali
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Haikuo
Onyx | Level 15

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

2 REPLIES 2
Haikuo
Onyx | Level 15

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
Calcite | Level 5

Thank you.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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