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 |
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.
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.
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 |
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.
Hi,
As in my data their are cases of skipping a quarter in the middle, I use the other method.
Thanks any way,
Lior
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.
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 |
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.
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
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.