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:
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;
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;
Many thanks!!!!
That's just awesome. It works so well.
Thanks a lot!
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.