code to create a new dataset

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 96
Accepted Solution

code to create a new dataset

Hi All,

I have a dataset with the following information: firm identifier (TIC), year, and CEO tenure. I want to create a new dataset where each firm CEO_tenure starts with 1 and is not less than 5. That is,

TIC     YEAR     CEO_tenure

A     2000     1

A     2001     2

A     2002     3

B     2000     1

B     2001     2

B     2002     3

B     2003     4

B     2004     5

B     2005     6

C     2000     4

C     2001     5

C     2002     6

C     2003     7

C     2004     8

C     2005     9

The desired output I am trying to get is:

B     2000     1

B     2001     2

B     2002     3

B     2003     4

B     2004     5

B     2005     6

A will not be in the new dataset because CEO_tenure for firm A is less than 5. C is not in the dataset because CEO_tenure does not start with 1. I would appreciate if someone would share a code with me.

Thank you,

Shalmali


Accepted Solutions
Solution
‎05-26-2014 09:13 PM
Super Contributor
Posts: 297

Re: code to create a new dataset

Because I am presently brushing up on my DOW loop skills I thought I would submit the following for critique.

DATA WANT (DROP=MINYRS MAXYRS);

  DO UNTIL (LAST.TIC);

  SET HAVE;

  BY TIC;

  MINYRS = MIN(CEO_TENURE,MINYRS);

  MAXYRS = MAX(CEO_TENURE,MINYRS);

  END;

  DO UNTIL (LAST.TIC);

  SET HAVE;

  BY TIC;

  IF MINYRS = 1 AND MAXYRS > 5 THEN OUTPUT;

  END;

RUN;

View solution in original post


All Replies
Super Contributor
Posts: 644

Re: code to create a new dataset

There are ways you can get the result in 1 step but a 2 step process is easer to understand

Proc SQL ;

     Create table take as

          Select TIC

          From   have

          Having count (*) >= 5

               and min (CEO_tenure) = 1

          Group by TIC

          ;


     Create table want as

          Select  hav.*

          From   have     hav

               ,     take     tak

          Where     hav.TIC     =     tak.TIC

          Order by  hav.TIC

               ,     CEO_tenure

          ;

Quit ;


[untested]


Richard

              


Respected Advisor
Posts: 4,919

Re: code to create a new dataset

Posted in reply to RichardinOz

Or you can let SAS do the two steps for you, You will get a Note about automatic remerging :

proc sql;

create table want as

select *

from have

group by TIC

having min(CEO_tenure)=1 and max(CEO_tenure)>5

order by TIC, YEAR;

quit;

PG

PG
Solution
‎05-26-2014 09:13 PM
Super Contributor
Posts: 297

Re: code to create a new dataset

Because I am presently brushing up on my DOW loop skills I thought I would submit the following for critique.

DATA WANT (DROP=MINYRS MAXYRS);

  DO UNTIL (LAST.TIC);

  SET HAVE;

  BY TIC;

  MINYRS = MIN(CEO_TENURE,MINYRS);

  MAXYRS = MAX(CEO_TENURE,MINYRS);

  END;

  DO UNTIL (LAST.TIC);

  SET HAVE;

  BY TIC;

  IF MINYRS = 1 AND MAXYRS > 5 THEN OUTPUT;

  END;

RUN;

🔒 This topic is solved and locked.

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

Discussion stats
  • 3 replies
  • 209 views
  • 7 likes
  • 4 in conversation