Fluorite | Level 6

## Daily returns to Weekly returns

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

1 ACCEPTED SOLUTION

Accepted Solutions
Tourmaline | Level 20

## Re: Daily returns to Weekly returns

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

12 REPLIES 12
Super User

## Re: Daily returns to Weekly returns

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

Fluorite | Level 6

## Re: Daily returns to Weekly returns

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
Tourmaline | Level 20

## Re: Daily returns to Weekly returns

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

Fluorite | Level 6

## Re: Daily returns to Weekly returns

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?
Super User

## Re: Daily returns to Weekly returns

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.

Fluorite | Level 6

## Re: Daily returns to Weekly returns

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

## Re: Daily returns to Weekly returns

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?

Tourmaline | Level 20

## Re: Daily returns to Weekly returns

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

Fluorite | Level 6

## Re: Daily returns to Weekly returns

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?

Fluorite | Level 6

## Re: Daily returns to Weekly returns

Never mind I think I got it working :slightly_smiling_face:

Garnet | Level 18

## Re: Daily returns to Weekly returns

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;``````
Fluorite | Level 6

## Re: Daily returns to Weekly returns

Alright thanks, I'll try that
Discussion stats
• 12 replies
• 2584 views
• 0 likes
• 4 in conversation