Arithmetic calculation that involved data from different rows

[lior]

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

[Astounding]

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.

[lior]

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

[Astounding]

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.

[lior]

Hi,

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

Thanks any way,

Lior

[ballardw]

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.

[lior]

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

[ballardw · Accepted solution]

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.

[lior]

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

[Haikuo]

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