Help using Base SAS procedures

code to add returns

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 96
Accepted Solution

code to add returns

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:

Tickerretdatebegindateenddate
A2.51/31/20044/1/20043/31/2005
A2.12/29/20044/1/20043/31/2005
A2.33/31/20044/1/20043/31/2005
A2.254/30/20044/1/20043/31/2005
A-2.35/31/20044/1/20043/31/2005
A2.26/30/20044/1/20043/31/2005
A2.67/31/20044/1/20043/31/2005
A2.28/31/20044/1/20043/31/2005
A2.49/30/20044/1/20043/31/2005
A2.310/31/20044/1/20043/31/2005
A2.2511/30/20044/1/20043/31/2005
A-2.312/31/20044/1/20043/31/2005
A2.21/31/20059/1/200510/31/2006
A2.62/28/20059/1/200510/31/2006
A2.23/31/20059/1/200510/31/2006
A2.64/30/20059/1/200510/31/2006
A2.555/31/20059/1/200510/31/2006
B2.51/31/20059/1/20058/31/2006
B2.12/28/20059/1/20058/31/2006
B2.33/31/20059/1/20058/31/2006
B2.254/30/20059/1/20058/31/2006
B-2.35/31/20059/1/20058/31/2006
B2.26/30/20059/1/20058/31/2006
B2.67/31/20059/1/20058/31/2006
B2.28/31/20059/1/20058/31/2006
B2.49/30/20059/1/20058/31/2006
B2.310/31/20059/1/20058/31/2006
B2.2511/30/20059/1/20058/31/2006
B-2.312/31/20059/1/20068/31/2006
B2.21/31/20069/1/20068/31/2007
B2.62/28/20069/1/20068/31/2007
B2.23/31/20069/1/20068/31/2007
B2.64/30/20069/1/20068/31/2007
B2.555/31/20069/1/20068/31/2007
B2.56/30/20069/1/20068/31/2007
B2.17/31/20069/1/20068/31/2007
B2.38/31/20069/1/20068/31/2007
B2.259/30/20069/1/20068/31/2007
B2.210/31/20069/1/20068/31/2007
B2.611/30/20069/1/20068/31/2007
B2.212/31/20069/1/20068/31/2007

I am trying to create an output as follows:

TickerretdatebegindateenddateSUMRET
A2.51/31/20044/1/20043/31/200518.6
A2.12/29/20044/1/20043/31/200518.6
A2.33/31/20044/1/20043/31/200518.6
A2.254/30/20044/1/20043/31/200518.6
A-2.35/31/20044/1/20043/31/200518.6
A2.26/30/20044/1/20043/31/200518.6
A2.67/31/20044/1/20043/31/200518.6
A2.28/31/20044/1/20043/31/200518.6
A2.49/30/20044/1/20043/31/200518.6
A2.310/31/20044/1/20043/31/200518.6
A2.2511/30/20044/1/20043/31/200518.6
A-2.312/31/20044/1/20043/31/200518.6
A2.21/31/20059/1/200510/31/2006
A2.62/28/20059/1/200510/31/2006
A2.23/31/20059/1/200510/31/2006
A2.64/30/20059/1/200510/31/2006
A2.555/31/20059/1/200510/31/2006
B2.51/31/20059/1/20058/31/200623.7
B2.12/28/20059/1/20058/31/200623.7
B2.33/31/20059/1/20058/31/200623.7
B2.254/30/20059/1/20058/31/200623.7
B-2.35/31/20059/1/20058/31/200623.7
B2.26/30/20059/1/20058/31/200623.7
B2.67/31/20059/1/20058/31/200623.7
B2.28/31/20059/1/20058/31/200623.7
B2.49/30/20059/1/20058/31/200623.7
B2.310/31/20059/1/20058/31/200623.7
B2.2511/30/20059/1/20058/31/200623.7
B-2.312/31/20059/1/20068/31/200623.7
B2.21/31/20069/1/20068/31/2007
B2.62/28/20069/1/20068/31/2007
B2.23/31/20069/1/20068/31/2007
B2.64/30/20069/1/20068/31/2007
B2.555/31/20069/1/20068/31/2007
B2.56/30/20069/1/20068/31/2007
B2.17/31/20069/1/20068/31/2007
B2.38/31/20069/1/20068/31/2007
B2.259/30/20069/1/20068/31/2007
B2.210/31/20069/1/20068/31/2007
B2.611/30/20069/1/20068/31/2007
B2.212/31/20069/1/20068/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.


Accepted Solutions
Solution
‎11-18-2013 04:41 PM
Respected Advisor
Posts: 4,022

Re: code to add returns

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;

View solution in original post


All Replies
Super User
Posts: 18,601

Re: code to add returns

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;

Solution
‎11-18-2013 04:41 PM
Respected Advisor
Posts: 4,022

Re: code to add returns

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;

Frequent Contributor
Frequent Contributor
Posts: 83

Re: code to add returns

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=_Smiley Happy 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;

🔒 This topic is solved and locked.

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

Discussion stats
  • 3 replies
  • 188 views
  • 6 likes
  • 4 in conversation