## weekly returns

Frequent Contributor
Posts: 79

# weekly returns

Dear all,

This is a little complicated

I would like to compute the cumulative returns by week for each variable. Each week is considered from Monday to Friday. So say I have a dataset  and I want to compute the weekly return for each var f

Each Friday I want the weekly Cumulative return computed

In the above example ann has only 4 days, but I would like to compute the return for that Friday using 4 day data.

My Input

 var date data ann 3/6/2012 2% ann 3/7/2012 3% ann 3/8/2012 4% ann 3/9/2012 5% joe 4/9/2012 6% joe 4/10/2012 7% joe 4/11/2012 8% joe 4/12/2012 9% joe 4/13/2012 10% joe 4/16/2012 10% joe 4/17/2012 10% joe 4/18/2012 10% joe 4/19/2012 10% joe 4/20/2012 10%

Output looks like

 var date week ret ann 3/9/2012 14.75% joe 4/13/2012 26.20% joe 4/20/2012 14.73%

Thanks!

Super Contributor
Posts: 1,636

## Re: weekly returns

how did you get 14.75%?

PROC Star
Posts: 8,164

## Re: weekly returns

I, too, don't understand where your numbers come from in your want example.  However, something like the following (I think) will do what you explained:

data temp;

set have;

format endofweek mmddyy10.;

if 2 le weekday(date) le 6;

endofweek = INTNX( 'WEEK', date, 0, 'E' )-1;

run;

proc summary data=temp (drop=date

rename=(endofweek=date))

nway;

var data;

class var date;

output out=want (drop=_ sum=;

run;

Posts: 5,535

## Re: weekly returns

If those are compounded daily interests, then the weekly return on fridays should be :

data test;
infile datalines n=3;
input name \$ / day :mmddyy. / pct;
datalines;
ann
3/6/2012
2
ann
3/7/2012
3
ann
3/8/2012
4
ann
3/9/2012
5
joe
4/9/2012
6
joe
4/10/2012
7
joe
4/11/2012
8
joe
4/12/2012
9
joe
4/13/2012
10
joe
4/16/2012
10
joe
4/17/2012
10
joe
4/18/2012
10
joe
4/19/2012
10
joe
4/20/2012
10
;

proc sql;
create table weekrtn as
select name, intnx("week.7", day, 0, "end") as week format=mmddyy10.,
exp(sum(log(1+pct/100))) - 1 as weekPct format=percentn8.2
from test
group by name, calculated week;
select * from weekRtn;
quit;

name            week   weekPct

-------------------------------------------

ann       03/09/2012   14.73%

joe        04/13/2012   46.87%

joe        04/20/2012   61.05%

PG

PG
Posts: 3,167

## Re: weekly returns

data have;

input var\$    date :mmddyy10.    data ercent10.;

format date mmddyy10.;

_id=cats(var,week(date));

cards;

ann    3/6/2012    2%

ann    3/7/2012    3%

ann    3/8/2012    4%

ann    3/9/2012    5%

joe    4/9/2012    6%

joe    4/10/2012    7%

joe    4/11/2012    8%

joe    4/12/2012    9%

joe    4/13/2012    10%

joe    4/16/2012    10%

joe    4/17/2012    10%

joe    4/18/2012    10%

joe    4/19/2012    10%

joe    4/20/2012    10%

;

data want (drop=_;

do until (last._id);

set have;

by _id;

if first._id then _total=1;

_total=_total*(1+data);

end;

Week_ret=_total-1;

run;

proc print;run;

Haikuo

Frequent Contributor
Posts: 79

## Re: weekly returns

Sorry guys I messed up

it is

 var date week ret ann 3/9/2012 14.75% joe 4/13/2012 46.87% joe 4/20/2012 61.05%

Valued Guide
Posts: 2,191

## Re: weekly returns

Hdg/

is your problem really described as

total  DATA for the latest Monday-Friday, each Friday (ignoring any returns on a Saturday or Sunday) ?

select var

,  intnx( 'week.7', date, 0, 'end' ) format= weekdate.  as friday

,  sum( data )                       format= percent8.2 as total_data

from your.data

where weekday( date) between 2 and 6

group by  var, friday

;

guess it cannot be because my total % values are different

;

Frequent Contributor
Posts: 79

## Re: weekly returns

yep i know i messed up the actual numbers are

it is

 var date week ret ann 3/9/2012 14.75% joe 4/13/2012 46.87% joe 4/20/2012 61.05%