## Creating Lag Values (Abnormal Earnings)

Solved
Occasional Contributor
Posts: 17

# 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:

 Company Stock price EPS Year 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

Accepted Solutions
Solution
‎08-16-2012 08:24 AM
Posts: 3,167

## Re: Creating Lag Values (Abnormal Earnings)

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

All Replies
Solution
‎08-16-2012 08:24 AM
Posts: 3,167

## Re: Creating Lag Values (Abnormal Earnings)

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)

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.