Compounding daily returns to get weekly returns

Reply
New Contributor
Posts: 3

Compounding daily returns to get weekly returns

Hello everyone

I'm having trouble compounding daily returns into weekly returns from Wednesday to Wednesday. I plan to use these weekly returns for an event study.

My data is for numerous countries and they all have different start dates. My data set looks like the following:

pic1.png

pic 2.png

If anyone can help guide me in the right direction, I will be ever grateful

Thanks in advance

Respected Advisor
Posts: 4,606

Re: Compounding daily returns to get weekly returns

Please make your data usable for members who want to work on your question. Screenshots can't be read with SAS. - PG

PG
New Contributor
Posts: 3

Re: Compounding daily returns to get weekly returns

Hi

I have attached my data in excel format along with the following code to read it in sas:

proc import datafile="C:\Users\user\Desktop\data\code\returns.xlsx" out = work.returns DBMS=excel2007 replace;

sheet='returns';

getnames=yes;

run;

I have been fighting with proc sql and the intck function but keep getting weird results.

Thank you

Respected Advisor
Posts: 4,606

Re: Compounding daily returns to get weekly returns

This forum doesn't like Excel documents. Try compressing (zip) your Excel files before posting or transform them into text (CSV) format.

PG
Esteemed Advisor
Posts: 6,648

Re: Compounding daily returns to get weekly returns

First, sort by date.

Then, run a data step like that:

data int1;

set returns;

by date;

retain cur_week 1;

if first.date and weekday(date) = 4 then cur_week + 1;

run;

Now you have your week numbers.

Sort by country and weeknumber, and do a proc summary by country and weeknumber.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 44

Re: Compounding daily returns to get weekly returns

You don't need to do sort if you are using proc sql:

proc sql;

  create table want as

  select distinct

         intnx('Week1.4',date,1) format=date10. as wednesday,

         country,

         sum(return) as return_sum,

         mean(return) as return_mean,

         n(return)    as return_n

  from   have

  group by country, intnx('week1.4',date,1);

quit;

Add -1 after date if you want that wednesday should be in current week not in "next" week.

New Contributor
Posts: 3

Re: Compounding daily returns to get weekly returns

Thanks for you help everyone, I really appreciate it.

PGstats - When I tried to upload the file in csv format and compressed (zip), I received the following message: The content type of this attachment is not allowed.

KurtBremser - I used your code and it correctly selects the returns for each week. Thanks.

I adapted the code slightly to try and get cumulative returns (in bold) using the following code:

proc sql;

  create table out as

  select distinct

        intnx('Week1.4',date,1) format=date10. as wednesday,

        country,

        sum(return) as return_sum,

        exp(sum(log(1+return))) - 1 as cum_return,

        n(return)    as return_n

  from  returns

  group by country, intnx('week1.4',date,1);

quit;

However, for some weeks I get some extremely high calculations for cum_return. Does anyone know how to calculate cumulative returns correctly for each week please?

Thanks again for all your help

Esteemed Advisor
Posts: 6,648

Re: Compounding daily returns to get weekly returns

Since

exp(sum(log(1+return)))

is actually a multiplication in disguise, some consecutive values of, say, 2.something (see line 6210 or 6224) can easily render something like 2 ** 7 for a week.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 7 replies
  • 1084 views
  • 1 like
  • 4 in conversation