BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
lior
Calcite | Level 5

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
1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

9 REPLIES 9
Astounding
PROC Star

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
Calcite | Level 5

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
Astounding
PROC Star

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
Calcite | Level 5

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
Super User

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
Calcite | Level 5

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
ballardw
Super User

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
Calcite | Level 5

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
Onyx | Level 15

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

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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