BookmarkSubscribeRSS Feed
hdg
Obsidian | Level 7 hdg
Obsidian | Level 7

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

    vardatedata
ann3/6/20122%
ann3/7/20123%
ann3/8/20124%
ann3/9/20125%
joe4/9/20126%
joe4/10/20127%
joe4/11/20128%
joe4/12/20129%
joe4/13/201210%
joe4/16/201210%
joe4/17/201210%
joe4/18/201210%
joe4/19/201210%
joe4/20/2012

10%

Output looks like

vardateweek ret
ann3/9/201214.75%
joe4/13/201226.20%
joe4/20/201214.73%

Thanks!

7 REPLIES 7
Linlin
Lapis Lazuli | Level 10

how did you get 14.75%?

art297
Opal | Level 21

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;

PGStats
Opal | Level 21

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
Haikuo
Onyx | Level 15

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

hdg
Obsidian | Level 7 hdg
Obsidian | Level 7

Sorry guys I messed up

it is

vardateweek ret
ann3/9/201214.75%
joe4/13/201246.87%
joe4/20/201261.05%

Thanks for all your help!!

Peter_C
Rhodochrosite | Level 12

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

;

hdg
Obsidian | Level 7 hdg
Obsidian | Level 7

yep i know i messed up the actual numbers are

it is

vardateweek ret
ann3/9/201214.75%
joe4/13/201246.87%
joe4/20/201261.05%

Thanks for all your help!!

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
  • 7 replies
  • 2204 views
  • 1 like
  • 6 in conversation