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!
how did you get 14.75%?
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;
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
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
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!!
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
;
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!!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.