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!!
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.
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.