topic Re: Average return based on daily returns from past six months in SAS Programming

Average return based on daily returns from past six months

MAC1430 — Sat, 22 Oct 2016 08:10:51 GMT

Hi Everyone,

At the beginning of each month t, I want to estimate average return based on daily returns from past six (t-6 to t-1) months. There will be almost 20-25 daily observations in a month, but I am keeping very few in sample for teh convenience. The original data is around 20 million observations. Please find the SAS data below. Based on this data, I estimate average return on 20100701 and 20100802 for stock ID 1 and stock ID 2. The result should be 0.006358 for stock 1 on 20100701 and 0.00679 on 20100802. The result should be 0.01401 for stock 1 on 20100701 and 0.008157 on 20100802.

Thanks a lot for your help.

Best,

Cheema

data have;
infile cards expandtabs truncover;
input stock date : yymmdd8. ret ;
format date yymmdd8.;
cards;
1 20100107 0.04135
1 20100126 -0.02544
1 20100129 0.03384
1 20100201 0.01089
1 20100202 0.02621
1 20100226 0.01001
1 20100301 -0.0132
1 20100330 -0.0181
1 20100331 -0.00308
1 20100401 0.05394
1 20100429 -0.01077
1 20100430 0.0031
1 20100503 0.00931
1 20100521 0
1 20100531 -0.01427
1 20100601 -0.02718
1 20100617 0.05019
1 20100630 -0.01236
1 20100701 0
1 20100728 0.01193
1 20100730 0.01621
1 20100802 -0.0174
2 20100107 0.06069
2 20100126 -0.06219
2 20100129 0.01989
2 20100201 -0.04551
2 20100202 0.04768
2 20100226 -0.05462
2 20100301 0.02613
2 20100330 -0.04155
2 20100331 0.04336
2 20100401 -0.00536
2 20100429 -0.02561
2 20100430 -0.0166
2 20100503 0.00563
2 20100521 0
2 20100531 0.01119
2 20100601 0
2 20100617 0.01521
2 20100630 0.27384
2 20100701 0.09519
2 20100728 -0.12793
2 20100730 -0.04815
2 20100802 -0.02235
run
;

Re: Average return based on daily returns from past six months

Shmuel — Sat, 22 Oct 2016 09:29:02 GMT

Check next code:

proc means data=have (where=(date between (todaty() - day(today() -1) to

intnx('month',today(),-6) ) NWAY;

class stock;

var ret;

output out=want mean=;

format mean 8.7;

run;

Re: Average return based on daily returns from past six months

MAC1430 — Sat, 22 Oct 2016 09:38:22 GMT

Hi Shmuel,

Sorry, I am looking for average returns over a period of 50 years. Therefore, I need a macro code that can estimate and roll it forward. I use some code to estimate it, but that is not very efficient.

Best,

Cheema

Re: Average return based on daily returns from past six months

Reeza — Sat, 22 Oct 2016 09:43:14 GMT

1. Make sure to convert your date to a SAS date.

2. Look into PROC EXPAND, specifically the CONVERT statement. The examples in documentation is helpful.

3. Or look as possibly using an array method: https://gist.github.com/statgeek/27e23c015eae7953eff2

You have daily data but talk about 6 month average. You'll have to be clear in your definition as 'months' is not a standard period.

Re: Average return based on daily returns from past six months

Shmuel — Sat, 22 Oct 2016 09:43:18 GMT

I'm not expert with statistics. Are you looking for forecast ?

I leave this question to others.

Re: Average return based on daily returns from past six months

Reeza — Sat, 22 Oct 2016 09:44:54 GMT

@MAC1430 wrote:

Hi Shmuel,

Sorry, I am looking for average returns over a period of 50 years. Therefore, I need a macro code that can estimate and roll it forward. I use some code to estimate it, but that is not very efficient.

Best,

Cheema

You DO NOT need a macro. There's nothing here that requires one and you should be aiming for a single pass of your data.

Re: Average return based on daily returns from past six months

MAC1430 — Sat, 22 Oct 2016 10:13:53 GMT

Dear Reeza,

Thanks for your reply. I want to estimate average returns based on daily returns in past six months. Hope it is clear now. I will really appreciate your help. I will try to edit the date problem, if there is edit option. Please find the SAS data below in case if the original once can not be edited. I hope this date is fine.

data have;
infile cards expandtabs truncover;
input stock date : yymmdd8. ret ;
format date yymmddn8.;
cards;
1 20100107 0.04135
1 20100126 -0.02544
1 20100129 0.03384
1 20100201 0.01089
1 20100202 0.02621
1 20100226 0.01001
1 20100301 -0.0132
1 20100330 -0.0181
1 20100331 -0.00308
1 20100401 0.05394
1 20100429 -0.01077
1 20100430 0.0031
1 20100503 0.00931
1 20100521 0
1 20100531 -0.01427
1 20100601 -0.02718
1 20100617 0.05019
1 20100630 -0.01236
1 20100701 0
1 20100728 0.01193
1 20100730 0.01621
1 20100802 -0.0174
2 20100107 0.06069
2 20100126 -0.06219
2 20100129 0.01989
2 20100201 -0.04551
2 20100202 0.04768
2 20100226 -0.05462
2 20100301 0.02613
2 20100330 -0.04155
2 20100331 0.04336
2 20100401 -0.00536
2 20100429 -0.02561
2 20100430 -0.0166
2 20100503 0.00563
2 20100521 0
2 20100531 0.01119
2 20100601 0
2 20100617 0.01521
2 20100630 0.27384
2 20100701 0.09519
2 20100728 -0.12793
2 20100730 -0.04815
2 20100802 -0.02235
run;

Re: Average return based on daily returns from past six months

MAC1430 — Sat, 22 Oct 2016 10:16:16 GMT

Dear Shmuel,

Thanks, It is a rolling average using daily returns from past six months. I have 5000 stocks and 50 years data

Best,

Cheema

Re: Average return based on daily returns from past six months

Ksharp — Sat, 22 Oct 2016 11:11:28 GMT

You have a big table ,therefore it is not good for SQL.

Also assuming data has been sorted by stock .


data have;
infile cards  truncover;
input stock date : yymmdd10. ret ;
format date yymmdd10.;
cards;
1 20100107 0.04135
1 20100126 -0.02544
1 20100129 0.03384
1 20100201 0.01089
1 20100202 0.02621
1 20100226 0.01001
1 20100301 -0.0132
1 20100330 -0.0181
1 20100331 -0.00308
1 20100401 0.05394
1 20100429 -0.01077
1 20100430 0.0031
1 20100503 0.00931
1 20100521 0
1 20100531 -0.01427
1 20100601 -0.02718
1 20100617 0.05019
1 20100630 -0.01236
1 20100701 0
1 20100728 0.01193
1 20100730 0.01621
1 20100802 -0.0174
2 20100107 0.06069
2 20100126 -0.06219
2 20100129 0.01989
2 20100201 -0.04551
2 20100202 0.04768
2 20100226 -0.05462
2 20100301 0.02613
2 20100330 -0.04155
2 20100331 0.04336
2 20100401 -0.00536
2 20100429 -0.02561
2 20100430 -0.0166
2 20100503 0.00563
2 20100521 0
2 20100531 0.01119
2 20100601 0
2 20100617 0.01521
2 20100630 0.27384
2 20100701 0.09519
2 20100728 -0.12793
2 20100730 -0.04815
2 20100802 -0.02235
;
run;
data want;
 if _n_=1 then do;
  if 0 then set have(rename=(ret=_ret));
  declare hash h();
  h.definekey('date');
  h.definedata('_ret');
  h.definedone();
 end;
 
 do until(last.stock);
  set have;
  by stock;
  _ret=ret;h.add();
 end;
 do until(last.stock);
  set have;
  by stock;
  sum=0;n=0;
  do k=intnx('month',date,-6,'b') to intnx('month',date,-1,'e');
   if h.find(key:k)=0 then do;sum+_ret;n+1;end;
  end;
  avg_ret=divide(sum,n);
  output;
 end;
h.clear();
drop sum n k _ret;
run;

Re: Average return based on daily returns from past six months

MAC1430 — Sat, 22 Oct 2016 11:27:06 GMT

Dear Ksharp,

Thanks a lot for it. Is it possible that it will start estimating average return from 7th month? For example, in our case it will estimate first return in July based on the daily returns from January to June. Second, is it possible that it estimate the average return on the first available day of the month only, other days can be shown as just missing.

Thanks again,

Best,

Cheema

Re: Average return based on daily returns from past six months

Shmuel — Sat, 22 Oct 2016 13:20:12 GMT

You can use same code that I posted with slight change for any period you want:

%let start = 2016JAN01; /* or any other date to start */

%let upto = 2016JUN30; /* or any other date to end */

proc means data=have (where=(date between "&start"d to "&upto"d ) ) NWAY;

class stock;

var ret;

output out=want mean=;

format mean 8.7;

run;

Re: Average return based on daily returns from past six months

Reeza — Sat, 22 Oct 2016 22:47:26 GMT

@Shmuel The OP wants a moving average which can't be accomplished in PROC means but can be in Proc EXPAND.

Re: Average return based on daily returns from past six months

MAC1430 — Sat, 22 Oct 2016 22:47:57 GMT

Dear Shmuel,

Thanks for it. Another member already provided the solution.

Best,

Cheema

Re: Average return based on daily returns from past six months

Reeza — Sat, 22 Oct 2016 23:00:32 GMT

You do realize that since your 6 month terms aren't consistent the validity of your results are questionable?

Re: Average return based on daily returns from past six months

MAC1430 — Sat, 22 Oct 2016 23:09:57 GMT

Hi Reeza,

Thanks for your concern. Basically, I estimate average return each month for each stock based on its daily returns in past six months.

Best,

Cheema

Re: Average return based on daily returns from past six months

Reeza — Sat, 22 Oct 2016 23:21:35 GMT

And it doesn't matter if that's 120, 125, or 130 days?

Re: Average return based on daily returns from past six months

MAC1430 — Sat, 22 Oct 2016 23:25:15 GMT

Yes, as long as the returns are from past six months. The frequency is not important.

Re: Average return based on daily returns from past six months

Ksharp — Sun, 23 Oct 2016 03:23:13 GMT

Sure. Also assuming table has been sorted by Stock,Date.


data have;
infile cards  truncover;
input stock date : yymmdd10. ret ;
format date yymmdd10.;
cards;
1 20100107 0.04135
1 20100126 -0.02544
1 20100129 0.03384
1 20100201 0.01089
1 20100202 0.02621
1 20100226 0.01001
1 20100301 -0.0132
1 20100330 -0.0181
1 20100331 -0.00308
1 20100401 0.05394
1 20100429 -0.01077
1 20100430 0.0031
1 20100503 0.00931
1 20100521 0
1 20100531 -0.01427
1 20100601 -0.02718
1 20100617 0.05019
1 20100630 -0.01236
1 20100701 0
1 20100728 0.01193
1 20100730 0.01621
1 20100802 -0.0174
2 20100107 0.06069
2 20100126 -0.06219
2 20100129 0.01989
2 20100201 -0.04551
2 20100202 0.04768
2 20100226 -0.05462
2 20100301 0.02613
2 20100330 -0.04155
2 20100331 0.04336
2 20100401 -0.00536
2 20100429 -0.02561
2 20100430 -0.0166
2 20100503 0.00563
2 20100521 0
2 20100531 0.01119
2 20100601 0
2 20100617 0.01521
2 20100630 0.27384
2 20100701 0.09519
2 20100728 -0.12793
2 20100730 -0.04815
2 20100802 -0.02235
;
run;
data want;
 if _n_=1 then do;
  if 0 then set have(rename=(ret=_ret));
  declare hash h();
  h.definekey('date');
  h.definedata('_ret');
  h.definedone();
 end;
 
 do until(last.stock);
  set have;
  by stock;
  _ret=ret;h.add();
 end;
 do until(last.stock);
  set have;
  by stock;
  avg_ret=.;
  month=month(date);
  if lag(month) ne month then do;
    sum=0;n=0;
    do k=intnx('month',date,-6,'b') to intnx('month',date,-1,'e');
     if h.find(key:k)=0 then do;sum+_ret;n+1;end;
    end;
    avg_ret=divide(sum,n);
  end;
  output;
 end;
h.clear();
drop sum n k _ret month;
run;

Re: Average return based on daily returns from past six months

MAC1430 — Sun, 23 Oct 2016 04:09:20 GMT

Thanks a lot Ksharp, really appreciated 🙂

Re: Average return based on daily returns from past six months

Kyojik — Thu, 15 Jun 2017 21:50:28 GMT

Hello Dear. I am trying to use these codes and they work perfect for the purpose they are here but i have some issues.
1) it is calculating even if last six months are not available. is it possible to calculate only for cases in which there are previous six months available?
2) I want to keep this get only first value of month for each month in each month.
thanks for posting these.