DATA Step, Macro, Functions and more

Average return based on daily returns from past six months

Accepted Solution Solved
Reply
Contributor
Posts: 57
Accepted Solution

Average return based on daily returns from past six months

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
;


Accepted Solutions
Solution
‎10-22-2016 06:45 PM
Super User
Posts: 9,671

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

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;

View solution in original post


All Replies
Trusted Advisor
Posts: 1,372

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

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;

        

Contributor
Posts: 57

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

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

Trusted Advisor
Posts: 1,372

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

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

I leave this question to others.

Contributor
Posts: 57

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

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

Super User
Posts: 17,784

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


cheema 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. 

Super User
Posts: 17,784

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

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. 

Contributor
Posts: 57

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

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;

Solution
‎10-22-2016 06:45 PM
Super User
Posts: 9,671

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

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;
Contributor
Posts: 57

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

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 

Trusted Advisor
Posts: 1,372

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

[ Edited ]

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;

        

 

Super User
Posts: 17,784

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

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

Contributor
Posts: 57

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

Dear Shmuel,

 

Thanks for it. Another member already provided the solution.

 

Best,

 

Cheema

Super User
Posts: 17,784

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

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

Contributor
Posts: 57

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

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
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 20 replies
  • 570 views
  • 2 likes
  • 5 in conversation