to create new dataset from the existing dataset

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 96
Accepted Solution

to create new dataset from the existing dataset


Hi All,

I have a question. I want to create a  new dataset and I want to include only those firms that has TENURE starting from 0 and there is no missing year. For instance, the subset of my data looks as follows:

Firm Year  TENURE

A     2000     1

A     2001     2

A     2002     3

A     2004     4

A     2005     5

A     2006     6

AA     2000     0

AA     2001     1

AA    2002     2

AA     2004     3

AA     2005     4

AA     2006     5

B     2000     0

B     2001     1

B     2005     5

B     2006     6

I want my output as follows:

AA     2000     0

AA     2001     1

AA    2002     2

AA     2004     3

AA     2005     4

AA     2006     5

Can someone please provide me with the code?

Thank you for your time.


Accepted Solutions
Solution
‎04-01-2013 09:25 PM
Super User
Posts: 5,099

Re: to create new dataset from the existing dataset

OK, here's one way.  It assumes you have already sorted by FIRM TENURE:

data want;

  keep_me='Y':

  tenure_check=-1;

  do until (last.firm);

     set have;

     by firm tenure;

     tenure_check + 1;

     if tenure ne tenure_check then keep_me='N';

  end;

  do until (last.firm);

     set have;

     by firm tenure;

     if keep_me='Y' then output;

  end;

  drop keep_me tenure_check;

run;

It also assumes that you typed in the years incorrectly for firm AA, since there is actually a gap in the years.  In actuality, I assumed that TENURE and YEAR go hand in hand and should increment together.

Good luck.

View solution in original post


All Replies
Solution
‎04-01-2013 09:25 PM
Super User
Posts: 5,099

Re: to create new dataset from the existing dataset

OK, here's one way.  It assumes you have already sorted by FIRM TENURE:

data want;

  keep_me='Y':

  tenure_check=-1;

  do until (last.firm);

     set have;

     by firm tenure;

     tenure_check + 1;

     if tenure ne tenure_check then keep_me='N';

  end;

  do until (last.firm);

     set have;

     by firm tenure;

     if keep_me='Y' then output;

  end;

  drop keep_me tenure_check;

run;

It also assumes that you typed in the years incorrectly for firm AA, since there is actually a gap in the years.  In actuality, I assumed that TENURE and YEAR go hand in hand and should increment together.

Good luck.

Frequent Contributor
Posts: 96

Re: to create new dataset from the existing dataset

Thanks a lot for your prompt response. The code works.

Respected Advisor
Posts: 3,124

Re: to create new dataset from the existing dataset

If you don't mind using Proc SQL:

proc sql;

create table want as

    select * from have

   group by firm

     having min(tenure)=0 and range(tenure)+1=count(distinct tenure); quit;

Haikuo

🔒 This topic is solved and locked.

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

Discussion stats
  • 3 replies
  • 256 views
  • 3 likes
  • 3 in conversation