BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
MAC1430
Pyrite | Level 9

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
;

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

20 REPLIES 20
Shmuel
Garnet | Level 18

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;

        

MAC1430
Pyrite | Level 9

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

Shmuel
Garnet | Level 18

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

I leave this question to others.

MAC1430
Pyrite | Level 9

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

Reeza
Super User

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

Reeza
Super User

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. 

MAC1430
Pyrite | Level 9

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;

Ksharp
Super User

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;
MAC1430
Pyrite | Level 9

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 

Shmuel
Garnet | Level 18

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;

        

 

Reeza
Super User

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

MAC1430
Pyrite | Level 9

Dear Shmuel,

 

Thanks for it. Another member already provided the solution.

 

Best,

 

Cheema

Reeza
Super User

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

MAC1430
Pyrite | Level 9
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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