Help using Base SAS procedures

weekly returns

Reply
Frequent Contributor
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

    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!

Super Contributor
Posts: 1,636

Re: weekly returns

how did you get 14.75%?

PROC Star
Posts: 7,357

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=_Smiley Happy sum=;

run;

Respected Advisor
Posts: 4,641

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
Respected Advisor
Posts: 3,124

Re: weekly returns

Your numbers are not correct in your output:

data have;

input var$    date :mmddyy10.    data Smiley Tongueercent10.;

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=_Smiley Happy;

  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
Frequent Contributor
Posts: 79

Re: weekly returns

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

Valued Guide
Posts: 2,174

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
Frequent Contributor
Posts: 79

Re: weekly returns

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

Ask a Question
Discussion stats
  • 7 replies
  • 916 views
  • 1 like
  • 6 in conversation