Calcite | Level 5

## Arithmetic calculation that involved data from different rows

Hello,

I'm trying to do an arithmetic calculation in which I calculate ΔEPS as  the current EPS minus the EPS  from the same quarter a year earlier.

For example at 31/03/2013 the ΔEPS = 20 - 5 = 15.

Any idea which kind of statements could execute this kind of calculation?

Thanks,

Lior

 Date EPS ΔEPS 31/03/2012 5 30/06/2012 12 30/09/2012 15 31/12/2012 17 31/03/2013 20 15 30/06/2013 22 10 30/09/2013 25 10 31/12/2013 27 10 31/03/2014 28 8 30/06/2014 31 9 30/09/2014 32 7 31/12/2014 35 8
1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Arithmetic calculation that involved data from different rows

In my approach change the ON instruction from
on a.date = intnx('qtr',b.date,4,'end');

to

on a.company_id = b.company_id

and a.date = intnx('qtr',b.date,4,'end');

You can add as many fields as need to match on the ON clause. There is a side-affect I should mention. Currently if the data were not sorted by company_id they are likely to come out that way.

Also, the data do not need to be sorted. So if a process appends a new quarter at the end of an existing data set with the result that the company and dates are out of order it doesn't matter.

9 REPLIES 9
PROC Star

## Re: Arithmetic calculation that involved data from different rows

Here's an easy solution that assumes you have only one observation within a quarter:

data want;

set have;

if qtr(date)=1 then delta_eps = eps - lag(eps);

else if qtr(date)=2 then delta_eps = eps - lag(eps);

else if qtr(date)=3 then delta_eps = eps - lag(eps);

else if qtr(date)=4 then delta_eps = eps - lag(eps);

run;

Unfortunately, understanding why it works requires digging into the complexities of the LAG function.

Good luck.

Calcite | Level 5

## Re: Arithmetic calculation that involved data from different rows

Hi, thanks for the quick unswer, but I forgot to add that my data set is more complicated as I have series of quarter_dates for each company. After one series is end new series started with the same dates but with new company number,

and I have to make sure that the calculation takes place only within the same company number (meaning the results are as in this table) .

is this statement approch this complicity?

 Company_no Date EPS ΔEPS 1 31/03/2012 5 1 30/06/2012 12 1 30/09/2012 15 1 31/12/2012 17 1 31/03/2013 20 15 1 30/06/2013 22 10 1 30/09/2013 25 10 1 31/12/2013 27 10 1 31/03/2014 28 8 1 30/06/2014 31 9 1 30/09/2014 32 7 1 31/12/2014 35 8 2 31/03/2012 30 2 30/06/2012 33 2 30/09/2012 37 2 31/12/2012 38 2 31/03/2013 40 10 2 30/06/2013 43 10 2 30/09/2013 50 13 2 31/12/2013 55 17 2 31/03/2014 56 16 2 30/06/2014 59 16 2 30/09/2014 61 11 2 31/12/2014 62 7
PROC Star

## Re: Arithmetic calculation that involved data from different rows

I figured that ... it's not really a big deal.  What is important:  the structure of the data.  For my approach to work, the data has to be sorted by Company_no Date, there has to be a single observation per company_no/Date (never multiple, and never skipping a quarter in the middle).  It's OK if the date ranges are different for each company.

In that case:

data want;

set have;

by company_no;

if first.company_no then counter=1;

else counter + 1;

if qtr(date)=1 then delta_eps = eps - lag(eps);

else if qtr(date)=2 then delta_eps = eps - lag(eps);

else if qtr(date)=3 then delta_eps = eps - lag(eps);

else if qtr(date)=4 then delta_eps = eps - lag(eps);

if counter <= 4 then delta_eps=.;

drop counter;

run;

Again, there are hidden complexities but the program as is should generate the right result.

Good luck.

Calcite | Level 5

## Re: Arithmetic calculation that involved data from different rows

Hi,

As in my data their are cases of skipping a quarter in the middle, I use the other method.

Thanks any way,

Lior

Super User

## Re: Arithmetic calculation that involved data from different rows

Or an approach that explicitly matches date quarters (IF they are all end of quarter values)

proc sql;

create table want as

select a.*, (a.eps - b.eps) as deltaeps

from have as a left join have as b

on a.date = intnx('qtr',b.date,4,'end');

quit;

This has a minor advantage of not calculating in case a quarter is missing for some reason, or possibly has a date that is not the end of a quarter.

Calcite | Level 5

## Re: Arithmetic calculation that involved data from different rows

Hi, thanks for the quick unswer, but I forgot to add that my data set is more complicated as I have series of quarter_dates for each company. After one series is end new series started with the same dates but with new company number,

and I have to make sure that the calculation takes place only within the same company number (meaning the results are as in this table) .

is this statement approch this complicity?

 Company_no Date EPS ΔEPS 1 31/03/2012 5 1 30/06/2012 12 1 30/09/2012 15 1 31/12/2012 17 1 31/03/2013 20 15 1 30/06/2013 22 10 1 30/09/2013 25 10 1 31/12/2013 27 10 1 31/03/2014 28 8 1 30/06/2014 31 9 1 30/09/2014 32 7 1 31/12/2014 35 8 2 31/03/2012 30 2 30/06/2012 33 2 30/09/2012 37 2 31/12/2012 38 2 31/03/2013 40 10 2 30/06/2013 43 10 2 30/09/2013 50 13 2 31/12/2013 55 17 2 31/03/2014 56 16 2 30/06/2014 59 16 2 30/09/2014 61 11 2 31/12/2014 62 7
Super User

## Re: Arithmetic calculation that involved data from different rows

In my approach change the ON instruction from
on a.date = intnx('qtr',b.date,4,'end');

to

on a.company_id = b.company_id

and a.date = intnx('qtr',b.date,4,'end');

You can add as many fields as need to match on the ON clause. There is a side-affect I should mention. Currently if the data were not sorted by company_id they are likely to come out that way.

Also, the data do not need to be sorted. So if a process appends a new quarter at the end of an existing data set with the result that the company and dates are out of order it doesn't matter.

Calcite | Level 5

## Re: Arithmetic calculation that involved data from different rows

thank you very much, its work great exept in companies that doesn't have a calender quarters (meaning instead of 31/03/2012, 30/06/2012, 30/09/2012 31/12/2012 their quarters are for example: 28/02/2012, 31/05/2012, 31/08/2012, 20/11/2012).

but most of my data are calender so its work fine to me.

thanks again,

Lior

Onyx | Level 15

## Re: Arithmetic calculation that involved data from different rows

As a different Proc SQL approach:

data have;

infile cards dlm='09'x;

input Company_no     Date:ddmmyy10.  EPS;

format date ddmmyy10.;

cards;

1    31/03/2012 5

1    30/06/2012 12

1    30/09/2012 15

1    31/12/2012 17

1    31/03/2013 20   15

1    30/06/2013 22   10

1    30/09/2013 25   10

1    31/12/2013 27   10

1    31/03/2014 28   8

1    30/06/2014 31   9

1    30/09/2014 32   7

1    31/12/2014 35   8

2    31/03/2012 30

2    30/06/2012 33

2    30/09/2012 37

2    31/12/2012 38

2    31/03/2013 40   10

2    30/06/2013 43   10

2    30/09/2013 50   13

2    31/12/2013 55   17

2    31/03/2014 56   16

2    30/06/2014 59   16

2    30/09/2014 61   11

2    31/12/2014 62   7

;

proc sql;

create table want as

select *, (select a.eps-eps from have where a.company_no=company_no and intck('qtr',date, a.date)=4) as Delta_EPS

from have a;

quit;

Haikuo

Discussion stats
• 9 replies
• 1276 views
• 6 likes
• 4 in conversation