topic Re: Arithmetic calculation that involved data from different rows in SAS Programming

Arithmetic calculation that involved data from different rows

lior — Thu, 04 Dec 2014 19:09:56 GMT

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

Re: Arithmetic calculation that involved data from different rows

Astounding — Thu, 04 Dec 2014 19:35:15 GMT

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.

Re: Arithmetic calculation that involved data from different rows

ballardw — Thu, 04 Dec 2014 21:08:13 GMT

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.

Re: Arithmetic calculation that involved data from different rows

lior — Thu, 04 Dec 2014 21:54:57 GMT

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

Re: Arithmetic calculation that involved data from different rows

lior — Thu, 04 Dec 2014 21:55:37 GMT

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

Re: Arithmetic calculation that involved data from different rows

Astounding — Thu, 04 Dec 2014 22:09:33 GMT

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.

Re: Arithmetic calculation that involved data from different rows

ballardw — Thu, 04 Dec 2014 22:33:00 GMT

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

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.

Re: Arithmetic calculation that involved data from different rows

lior — Fri, 05 Dec 2014 00:16:52 GMT

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

Re: Arithmetic calculation that involved data from different rows

lior — Fri, 05 Dec 2014 00:18:30 GMT

Hi,

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

Thanks any way,

Lior

Re: Arithmetic calculation that involved data from different rows

Haikuo — Fri, 05 Dec 2014 04:38:58 GMT

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