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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

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

3 REPLIES 3
Reeza
Super User

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
Opal | Level 21

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

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

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
  • 3 replies
  • 875 views
  • 6 likes
  • 4 in conversation