topic Re: weekly returns in SAS Procedures

weekly returns

hdg — Wed, 11 Apr 2012 21:53:22 GMT

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!

Re: weekly returns

Linlin — Wed, 11 Apr 2012 22:15:35 GMT

how did you get 14.75%?

Re: weekly returns

art297 — Wed, 11 Apr 2012 22:33:46 GMT

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;

Re: weekly returns

PGStats — Wed, 11 Apr 2012 22:50:39 GMT

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%

Re: weekly returns

Haikuo — Wed, 11 Apr 2012 22:54:07 GMT

Your numbers are not correct in your output:

data have;

input var$ date :mmddyy10. data :percent10.;

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

Re: weekly returns

hdg — Thu, 12 Apr 2012 18:41:44 GMT

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%

Thanks for all your help!!

Re: weekly returns

Peter_C — Thu, 12 Apr 2012 20:43:21 GMT

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

;

Re: weekly returns

hdg — Thu, 12 Apr 2012 20:49:58 GMT

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%

Thanks for all your help!!