topic Re: Daily returns to Weekly returns in SAS Programming

Daily returns to Weekly returns

Antoine44 — Wed, 01 Apr 2020 03:07:35 GMT

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

Re: Daily returns to Weekly returns

Reeza — Wed, 01 Apr 2020 03:18:19 GMT

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

Re: Daily returns to Weekly returns

Antoine44 — Wed, 01 Apr 2020 03:28:39 GMT

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

Re: Daily returns to Weekly returns

ChrisNZ — Wed, 01 Apr 2020 03:43:29 GMT

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

Re: Daily returns to Weekly returns

Antoine44 — Wed, 01 Apr 2020 04:01:26 GMT

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?

Re: Daily returns to Weekly returns

Reeza — Wed, 01 Apr 2020 04:38:47 GMT

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.

Re: Daily returns to Weekly returns

Antoine44 — Wed, 01 Apr 2020 05:17:14 GMT

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

Re: Daily returns to Weekly returns

Antoine44 — Wed, 01 Apr 2020 05:29:10 GMT

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?

Re: Daily returns to Weekly returns

Shmuel — Wed, 01 Apr 2020 06:06:04 GMT

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;

Re: Daily returns to Weekly returns

Antoine44 — Wed, 01 Apr 2020 06:12:09 GMT

Alright thanks, I'll try that

Re: Daily returns to Weekly returns

ChrisNZ — Wed, 01 Apr 2020 10:12:06 GMT

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

Re: Daily returns to Weekly returns

Antoine44 — Sun, 05 Jul 2020 02:23:43 GMT

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!

Re: Daily returns to Weekly returns

Antoine44 — Sun, 05 Jul 2020 02:37:00 GMT

Never mind I think I got it working :slightly_smiling_face:

Thanks again for your help!