Order the variables

[ha1331]

Hi everyone,

I have a data sample like this:

Firm  Year        IPO_year 

A       2000       0

A       2001       1

A       2002       0

A       2003       0

B       2007       0

B       2008       1

B       2009       0

C       2013       0

C       2014       0

I want to count the number of years after IPO for each firm, so what I want to get is another column like this:

Firm  Year        IPO_year       Year_After_IPO

A       2000       0                     0

A       2001       1                     1

A       2002       0                     2

A       2003       0                     3

B       2007       0                     0

B       2008       1                     1

B       2009       0                     2

C       2013       0                     0

C       2014       0                     0

(All the years before IPO will be zero).

I have tried this code: 

data X;

    set Y;

by Firm;

if IPO_year = 1 then d+1;

    run;

but this code is not reset for new firm. Could any one can help me with this?

Thank you very much in advance!

[PaigeMiller · Accepted solution]

You can use if first.firm to reset the calculations to zero, as shown below.

data want;
	set have;
	by firm;
	retain ipo_flag;
	if ipo_year=1 then ipo_flag=1;
	if first.firm then do;
		year_after_ipo=0;
		ipo_flag=0;
	end;
	if ipo_flag then year_after_ipo+1;
    drop ipo_flag;
run;

[ha1331]

Many thanks!!!!

That's just awesome. It works so well.

Thanks a lot!

[Tom]

Your first two IF statements are out of order.  It only works for the sample data because no firm has IPO in the first year.

[ha1331]

Right. Thanks for noticing me about that.

I checked the data and changed the order of the two if statements. It works well after that.