to create new dataset from the existing dataset

Solved
Frequent Contributor
Posts: 96

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?

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

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.

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

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.

Posts: 3,167

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.