Hi,
I want a sample with firm identifier, dates, begin date, and end date. I want to create a new variable, SUMRET, that will be a sum of returns between begin date and end date. The subsample of my data looks as follows:
Ticker | ret | date | begindate | enddate |
A | 2.5 | 1/31/2004 | 4/1/2004 | 3/31/2005 |
A | 2.1 | 2/29/2004 | 4/1/2004 | 3/31/2005 |
A | 2.3 | 3/31/2004 | 4/1/2004 | 3/31/2005 |
A | 2.25 | 4/30/2004 | 4/1/2004 | 3/31/2005 |
A | -2.3 | 5/31/2004 | 4/1/2004 | 3/31/2005 |
A | 2.2 | 6/30/2004 | 4/1/2004 | 3/31/2005 |
A | 2.6 | 7/31/2004 | 4/1/2004 | 3/31/2005 |
A | 2.2 | 8/31/2004 | 4/1/2004 | 3/31/2005 |
A | 2.4 | 9/30/2004 | 4/1/2004 | 3/31/2005 |
A | 2.3 | 10/31/2004 | 4/1/2004 | 3/31/2005 |
A | 2.25 | 11/30/2004 | 4/1/2004 | 3/31/2005 |
A | -2.3 | 12/31/2004 | 4/1/2004 | 3/31/2005 |
A | 2.2 | 1/31/2005 | 9/1/2005 | 10/31/2006 |
A | 2.6 | 2/28/2005 | 9/1/2005 | 10/31/2006 |
A | 2.2 | 3/31/2005 | 9/1/2005 | 10/31/2006 |
A | 2.6 | 4/30/2005 | 9/1/2005 | 10/31/2006 |
A | 2.55 | 5/31/2005 | 9/1/2005 | 10/31/2006 |
B | 2.5 | 1/31/2005 | 9/1/2005 | 8/31/2006 |
B | 2.1 | 2/28/2005 | 9/1/2005 | 8/31/2006 |
B | 2.3 | 3/31/2005 | 9/1/2005 | 8/31/2006 |
B | 2.25 | 4/30/2005 | 9/1/2005 | 8/31/2006 |
B | -2.3 | 5/31/2005 | 9/1/2005 | 8/31/2006 |
B | 2.2 | 6/30/2005 | 9/1/2005 | 8/31/2006 |
B | 2.6 | 7/31/2005 | 9/1/2005 | 8/31/2006 |
B | 2.2 | 8/31/2005 | 9/1/2005 | 8/31/2006 |
B | 2.4 | 9/30/2005 | 9/1/2005 | 8/31/2006 |
B | 2.3 | 10/31/2005 | 9/1/2005 | 8/31/2006 |
B | 2.25 | 11/30/2005 | 9/1/2005 | 8/31/2006 |
B | -2.3 | 12/31/2005 | 9/1/2006 | 8/31/2006 |
B | 2.2 | 1/31/2006 | 9/1/2006 | 8/31/2007 |
B | 2.6 | 2/28/2006 | 9/1/2006 | 8/31/2007 |
B | 2.2 | 3/31/2006 | 9/1/2006 | 8/31/2007 |
B | 2.6 | 4/30/2006 | 9/1/2006 | 8/31/2007 |
B | 2.55 | 5/31/2006 | 9/1/2006 | 8/31/2007 |
B | 2.5 | 6/30/2006 | 9/1/2006 | 8/31/2007 |
B | 2.1 | 7/31/2006 | 9/1/2006 | 8/31/2007 |
B | 2.3 | 8/31/2006 | 9/1/2006 | 8/31/2007 |
B | 2.25 | 9/30/2006 | 9/1/2006 | 8/31/2007 |
B | 2.2 | 10/31/2006 | 9/1/2006 | 8/31/2007 |
B | 2.6 | 11/30/2006 | 9/1/2006 | 8/31/2007 |
B | 2.2 | 12/31/2006 | 9/1/2006 | 8/31/2007 |
I am trying to create an output as follows:
Ticker | ret | date | begindate | enddate | SUMRET |
A | 2.5 | 1/31/2004 | 4/1/2004 | 3/31/2005 | 18.6 |
A | 2.1 | 2/29/2004 | 4/1/2004 | 3/31/2005 | 18.6 |
A | 2.3 | 3/31/2004 | 4/1/2004 | 3/31/2005 | 18.6 |
A | 2.25 | 4/30/2004 | 4/1/2004 | 3/31/2005 | 18.6 |
A | -2.3 | 5/31/2004 | 4/1/2004 | 3/31/2005 | 18.6 |
A | 2.2 | 6/30/2004 | 4/1/2004 | 3/31/2005 | 18.6 |
A | 2.6 | 7/31/2004 | 4/1/2004 | 3/31/2005 | 18.6 |
A | 2.2 | 8/31/2004 | 4/1/2004 | 3/31/2005 | 18.6 |
A | 2.4 | 9/30/2004 | 4/1/2004 | 3/31/2005 | 18.6 |
A | 2.3 | 10/31/2004 | 4/1/2004 | 3/31/2005 | 18.6 |
A | 2.25 | 11/30/2004 | 4/1/2004 | 3/31/2005 | 18.6 |
A | -2.3 | 12/31/2004 | 4/1/2004 | 3/31/2005 | 18.6 |
A | 2.2 | 1/31/2005 | 9/1/2005 | 10/31/2006 | |
A | 2.6 | 2/28/2005 | 9/1/2005 | 10/31/2006 | |
A | 2.2 | 3/31/2005 | 9/1/2005 | 10/31/2006 | |
A | 2.6 | 4/30/2005 | 9/1/2005 | 10/31/2006 | |
A | 2.55 | 5/31/2005 | 9/1/2005 | 10/31/2006 | |
B | 2.5 | 1/31/2005 | 9/1/2005 | 8/31/2006 | 23.7 |
B | 2.1 | 2/28/2005 | 9/1/2005 | 8/31/2006 | 23.7 |
B | 2.3 | 3/31/2005 | 9/1/2005 | 8/31/2006 | 23.7 |
B | 2.25 | 4/30/2005 | 9/1/2005 | 8/31/2006 | 23.7 |
B | -2.3 | 5/31/2005 | 9/1/2005 | 8/31/2006 | 23.7 |
B | 2.2 | 6/30/2005 | 9/1/2005 | 8/31/2006 | 23.7 |
B | 2.6 | 7/31/2005 | 9/1/2005 | 8/31/2006 | 23.7 |
B | 2.2 | 8/31/2005 | 9/1/2005 | 8/31/2006 | 23.7 |
B | 2.4 | 9/30/2005 | 9/1/2005 | 8/31/2006 | 23.7 |
B | 2.3 | 10/31/2005 | 9/1/2005 | 8/31/2006 | 23.7 |
B | 2.25 | 11/30/2005 | 9/1/2005 | 8/31/2006 | 23.7 |
B | -2.3 | 12/31/2005 | 9/1/2006 | 8/31/2006 | 23.7 |
B | 2.2 | 1/31/2006 | 9/1/2006 | 8/31/2007 | |
B | 2.6 | 2/28/2006 | 9/1/2006 | 8/31/2007 | |
B | 2.2 | 3/31/2006 | 9/1/2006 | 8/31/2007 | |
B | 2.6 | 4/30/2006 | 9/1/2006 | 8/31/2007 | |
B | 2.55 | 5/31/2006 | 9/1/2006 | 8/31/2007 | |
B | 2.5 | 6/30/2006 | 9/1/2006 | 8/31/2007 | |
B | 2.1 | 7/31/2006 | 9/1/2006 | 8/31/2007 | |
B | 2.3 | 8/31/2006 | 9/1/2006 | 8/31/2007 | |
B | 2.25 | 9/30/2006 | 9/1/2006 | 8/31/2007 | |
B | 2.2 | 10/31/2006 | 9/1/2006 | 8/31/2007 | |
B | 2.6 | 11/30/2006 | 9/1/2006 | 8/31/2007 | |
B | 2.2 | 12/31/2006 | 9/1/2006 | 8/31/2007 |
The value of SUMRET for firm A for year 2004 is calculated by adding ret from period 04/30/2004 till 03/31/2005. This is because the begin date is 04/1/2004 and end period is 03/31/2005. Similarly the SUMRET for firm B for 2005 is calculated by adding ret from the period 09/30/2005 till 08/31/2006. This is because the begin date is 09/1/2005 and end period is 08/31/2006.
Thanks.
Does the code below give you what you're after?
proc sql;
create table want as
select
Ticker, ret, date, begindate, enddate,
sum(ret) as sum_ret
from have
group by ticker, year(date), begindate, enddate
order by ticker, date, begindate, enddate
;
quit;
Something like this to help get you started.
You may need to modify the group by clause, this is untested.
proc sql;
create table want as
select a.*, sum(b.ret) as sum_ret
from have as a
left join have as b
on a.ticker=b.ticker
and b.date between a.begdate and a.enddate
group by ticker, date, begdate, enddate;
quit;
Does the code below give you what you're after?
proc sql;
create table want as
select
Ticker, ret, date, begindate, enddate,
sum(ret) as sum_ret
from have
group by ticker, year(date), begindate, enddate
order by ticker, date, begindate, enddate
;
quit;
Hi Shalmali
How about the following code?
proc sort data=have;
by Ticker begindate enddate;
run;
proc summary data=have missing nway;
by Ticker begindate enddate;
var ret ;
output out=sumret (drop=_:) sum=;
run;
proc sort data=sumret(rename=(ret=sumret));
by Ticker begindate enddate;
run;
data want ;
merge have(in=a) sumret(in=b);
by Ticker begindate enddate;
if a;
run;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.