Daily returns to Weekly returns

[Antoine44]

Hi everyone, 

I'm trying to get weekly returns (Monday to Friday) from my daily returns data set, for each week and each company over my sample period. Can anyone help me with that? (see example below of my data set with two companies, just for one week). Hope that's enough, let me know if you need more info. Thanks! 

PERMNO                    DATE                     RET

10158                     28/04/2014               -0.01

10158                     29/04/2014               -0.006 

10158                     30/04/2014                0.006

10158                     01/05/2014               -0.009          

10158                     02/05/2014                0.002  

10234                     28/04/2014                -0.02

10234                     29/04/2014                -0.005 

10234                     30/04/2014                0.001

10234                     01/05/2014               -0.007          

10234                     02/05/2014                0.003  

[Reeza]

Rolling weekly or just weekly? Assuming you're doing multiplicative accumulation?
Can you show what you want as your output?

[Antoine44]

Just weekly 🙂
So for 10158 I should get -0.01698. That's (-0.01+1)*(-0.006+1)*(0.006+1)*(-0.009+1)*(0.002+1) - 1
For 10234 I should get -0.02785. That's (-0.02+)1*(-0.005+1)*(0.001+1)*(-0.007+1)*(0.003+1) - 1

[ChrisNZ]

Like this (Assuming all dates must be used since that's the data provided)?

data WANT;
  set HAVE;
  by PERMNO notsorted;
  retain CUMUL;
  if first.PERMNO then CUMUL=1+RET;
  else CUMUL=CUMUL*(1+RET);
  if last.PERMNO then  do;
    CUMUL=CUMUL-1; 
    output;
  end;  
  drop DATE RET;
run; 

 

PERMNO	CUMUL
10158	-0.016983
10234	-0.027850

[Antoine44]

Sorry, that works for my example but I just realised it won't work (I think) for what I actually have. Like this:

PERMNO DATE RET Weekly Ret

10158 28/04/2014 -0.01
10158 29/04/2014 -0.006
10158 30/04/2014 0.006
10158 01/05/2014 -0.009
10158 02/05/2014 0.002 -0.16983
10158 05/05/2014 -0.04
10158 06/05/2014 -0.006
10158 07/05/2014 0.002
10158 08/05/2014 -0.003
10158 09/05/2014 0.005 -0.04195
10234 28/04/2014 -0.02
10234 29/04/2014 -0.005
10234 30/04/2014 0.001
10234 01/05/2014 -0.007
10234 02/05/2014 0.003 -0.027850

So I've added an extra week for 10158 cause for each company I've got multiple consecutive weeks. Hope that makes sense?

[Reeza]

Do you want calendar weeks or just 7 days? Does what is a week for one PermNo be the same week for another PERMNO or can they use different days because say they start on different dates? I think you'll need to expand your example data. 

[Antoine44]

Maybe I can share my data set to show you what I mean. Can I share my dataset or maybe screenshot would do?

[Antoine44]

I have attached a text file with one company only. Each week is assigned a number and I just need the weekly returns for each one of them. I have other companies too with returns in the same weeks as the one attached so it needs by week and by permno and by year as well cause you'll find the same number assigned to a  week every year. Does this help?

[ChrisNZ · Accepted solution]

Just a small modification.

data WANT;
  set HAVE;
  by PERMNO notsorted; 
  retain CUMUL;
  if first.PERMNO or weekday(DATE)=2 then CUMUL=1+RET;
  else CUMUL=CUMUL*(1+RET);
  if last.PERMNO or weekday(DATE)=6 then do;
    CUMUL=CUMUL-1; 
    output;
  end;  
  drop RET;
run;

PERMNO	DATE	CUMUL
10158	19845	-0.016983
10158	19852	-0.041954
10234	19845	-0.027850

[Antoine44]

Hi Chris,

Thanks for that, that works most of the time. However, I just noticed that it doesn't work when the week starts on weekday 3 (because day 2 would've been a public holiday).
Any chance you know how to get around that?
Thanks for your help!

[Antoine44]

Never mind I think I got it working :slightly_smiling_face:

Thanks again for your help!

[Shmuel]

Assuming data is sorted by company and date try next code:

data temp; 
 set have;
    by premno date;
        retain week_count 0  week_total 0;
        if first.premno then do;  week_count = 0; week_total = 0; end;

        if weekday(date) = 1  /* assuming monday */
            then  do; week_count + 1; week_total = 0; end;
       week_total = week_total + ret; 
run;
data want;
  set temp;
   by premno week_count;
      if   last.wee_count;
run;

[Antoine44]

Alright thanks, I'll try that