DATA Step, Macro, Functions and more

Arithmetic calculation that involved data from different rows

Accepted Solution Solved
Reply
Contributor
Posts: 38
Accepted Solution

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

DateEPSΔEPS
31/03/20125
30/06/201212
30/09/201215
31/12/201217
31/03/20132015
30/06/20132210
30/09/20132510
31/12/20132710
31/03/2014288
30/06/2014319
30/09/2014327
31/12/2014358

Accepted Solutions
Solution
‎12-04-2014 05:33 PM
Super User
Posts: 11,343

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.

View solution in original post


All Replies
Super User
Posts: 5,498

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.

Contributor
Posts: 38

Re: Arithmetic calculation that involved data from different rows

Posted in reply to Astounding

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_noDateEPSΔEPS
131/03/20125
130/06/201212
130/09/201215
131/12/201217
131/03/20132015
130/06/20132210
130/09/20132510
131/12/20132710
131/03/2014288
130/06/2014319
130/09/2014327
131/12/2014358
231/03/201230
230/06/201233
230/09/201237
231/12/201238
231/03/20134010
230/06/20134310
230/09/20135013
231/12/20135517
231/03/20145616
230/06/20145916
230/09/20146111
231/12/2014627
Super User
Posts: 5,498

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.

Contributor
Posts: 38

Re: Arithmetic calculation that involved data from different rows

Posted in reply to Astounding

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
Posts: 11,343

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.

Contributor
Posts: 38

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_noDateEPSΔEPS
131/03/20125
130/06/201212
130/09/201215
131/12/201217
131/03/20132015
130/06/20132210
130/09/20132510
131/12/20132710
131/03/2014288
130/06/2014319
130/09/2014327
131/12/2014358
231/03/201230
230/06/201233
230/09/201237
231/12/201238
231/03/20134010
230/06/20134310
230/09/20135013
231/12/20135517
231/03/20145616
230/06/20145916
230/09/20146111
231/12/2014627
Solution
‎12-04-2014 05:33 PM
Super User
Posts: 11,343

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.

Contributor
Posts: 38

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

Respected Advisor
Posts: 3,156

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

🔒 This topic is solved and locked.

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

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