code to add returns

[shalmali]

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.

[Reeza]

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;

[Patrick · Accepted solution]

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;

[Mit]

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;