Creating Lag Values (Abnormal Earnings)

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 17
Accepted Solution

Creating Lag Values (Abnormal Earnings)

Hi guys,

I'm trying to create a new variable called abnormal earnings which is (The difference between next year's and this year's earnings per share

(EPS), scaled by the fiscal year-end stock price). Therefore, what i need for company A is [EPS(2002) - EPS (2001)] / Stock Price (2001).

Q1) However, note that there are missing years (2004 for company A and 2005 for company B). Therefore, there will be no observations for those years. Is there anyway i could skip those years. For example, I do not want the code to calculate [EPS(2005) - EPS (2003)] / Stock Price (2003) just because year 2004 is missing for company A.

Q2) Also, there are some years (e.g 2008 for company B) where EPS or stock price data are missing. I want to skip those too.

I have over a thousand companies in my dataset and some companies have missing years such as this. Your help will be deeply appreciated. Thank You.

An example:

CompanyStock priceEPSYear
A20.22001
A2.150.242002
A2.160.262003
A2.190.272005
A2.40.292006
A2.60.252007
A2.660.242008
A1.80.182009
B132.12001
B13.112.62002
B13.62.92003
B143.12004
B162.52006
B183.42007
B14.152008
B114.22009

Accepted Solutions
Solution
‎08-16-2012 08:24 AM
Respected Advisor
Posts: 3,156

Re: Creating Lag Values (Abnormal Earnings)

Posted in reply to rilatotoro

You have many rules, so there is a chance that I have not really understand some of them. Please try the following and let us know if it helps:

data have;

input Company$    Stock_price    EPS    Year;

cards;

A    2    0.2    2001

A    2.15    0.24    2002

A    2.16    0.26    2003

A    2.19    0.27    2005

A    2.4    0.29    2006

A    2.6    0.25    2007

A    2.66    0.24    2008

A    1.8    0.18    2009

B    13    2.1    2001

B    13.11    2.6    2002

B    13.6    2.9    2003

B    14    3.1    2004

B    16    2.5    2006

B    18    3.4    2007

B    14.15    .    2008

B    11    4.2    2009

;

data want;

  set have;

    by company;

     array ss(1:3) _temporary_;

      if first.company or

         year-ss(3)>1  or

         missing(ss(2)+ss(1)+eps) then call missing(abnormal);

      else abnormal=(eps-ss(2))/ss(1);

     ss(1)=stock_price; ss(2)=eps; ss(3)=year;

run;

Proc print;run;

Haikuo

View solution in original post


All Replies
Solution
‎08-16-2012 08:24 AM
Respected Advisor
Posts: 3,156

Re: Creating Lag Values (Abnormal Earnings)

Posted in reply to rilatotoro

You have many rules, so there is a chance that I have not really understand some of them. Please try the following and let us know if it helps:

data have;

input Company$    Stock_price    EPS    Year;

cards;

A    2    0.2    2001

A    2.15    0.24    2002

A    2.16    0.26    2003

A    2.19    0.27    2005

A    2.4    0.29    2006

A    2.6    0.25    2007

A    2.66    0.24    2008

A    1.8    0.18    2009

B    13    2.1    2001

B    13.11    2.6    2002

B    13.6    2.9    2003

B    14    3.1    2004

B    16    2.5    2006

B    18    3.4    2007

B    14.15    .    2008

B    11    4.2    2009

;

data want;

  set have;

    by company;

     array ss(1:3) _temporary_;

      if first.company or

         year-ss(3)>1  or

         missing(ss(2)+ss(1)+eps) then call missing(abnormal);

      else abnormal=(eps-ss(2))/ss(1);

     ss(1)=stock_price; ss(2)=eps; ss(3)=year;

run;

Proc print;run;

Haikuo

Contributor
Posts: 30

Re: Creating Lag Values (Abnormal Earnings)

Posted in reply to rilatotoro

IMHO the simplest way would be to fill out observations for the missing years:

data have;

input company $ stock_price eps year;

cards;

A 2 0.2 2001

...

B 11 4.2 2009

;

run;

proc sql;

select min(a.year) into :minyear from have as a;

select max(a.year) into :maxyear from have as a;

quit;

proc sort data=have(keep=company) out=companies_list nodupkey;

by company;

quit;

data company_years;

set companies_list;

do year=&minyear to &maxyear;

output;

end;

run;

proc sql;

create table have_expanded as select

a.company, a.year,

b.stock_price, b.eps

from company_years as a full join have as b on (a.company=b.company and a.year=b.year)

order by a.company, a.year;

quit;

data want;

set have_expanded;

by company;

ab_earnings=dif(eps)/lag(stock_price);

if first.company then ab_earnings=.;

run;

This will give you an observation for company A in 2004, but all other variables in this observation will be missing. Value for ab_earnings in 2005 will be calculated as missing, because it relies on missing values from 2004.

From here, you can easily delete observations with missing values of ab_earnings if you don't want them showing up in the final outputs.

🔒 This topic is solved and locked.

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

Discussion stats
  • 2 replies
  • 259 views
  • 6 likes
  • 3 in conversation