07-30-2014 11:08 AM
I'm relatively new here and this is my first post on SAS forum. Hope I can get the help I need!
I'm doing a research project on index returns. I have daily data for a period of over 40 years. However, I do not have returns for Saturday and Sunday or any other holidays when stock market would be closed. I'm trying to convert this daily data into monthly using PROC EXPAND procedure. Here is my analysis:
- I used PROC EXPAND and found sum of the returns for each month. Subsequently, I calculated the same using excel (using pivot table). I found the results to be different. Is there any reason why this would happen?
- My data starts from January of 1973 until May 2014. When I used PROC EXPAND with missing values for Saturdays and Sundays, the resultant output didn't have any return value for January of 1973. So I tried to set all missing values to zero and voila, sum of return for January 1973 appeared. Is this a right approach?
proc expand data=lib.dly_1 from=day to=month;
- And also, I need to find Standard deviation for each month once converted from daily return using PROC EXPAND. Can I do this by moving standard deviation? Is there a way to do this using PROC EXPAND or any other procedure or way?
Appreciate your help! Thanks in advance!
07-30-2014 07:39 PM
My apologies. Here are my code and results:
data usd.dly_5(drop= lag_DTWEXM count lag_count numday);
if(dly_ret=".") then dly_ret=0;
proc expand data=usd.dly_5 from=day to=month out=usd.dly_6;
Result using PROC EXPAND:
yr date dly_ret
1973 JAN1973 -0.002940597
1973 FEB1973 -0.023633396
1973 MAR1973 0.0081171767
1973 APR1973 -0.001223039
1973 MAY1973 -0.006476482
1973 JUN1973 -0.00692127
1973 JUL1973 -0.00099385
1973 AUG1973 0.0200460595
1973 SEP1973 0.0001108501
1973 OCT1973 0.0011257383
1973 NOV1973 0.0123855573
1973 DEC1973 0.0024655859
Results using Excel:
I have attached the daily raw data for the year 1973. Please let me know if you need anything else. Appreciate your help.
07-31-2014 12:11 PM
So I apparently can't help much because my license for ETS is messed up and it'll take a while for IT to fix it.
That being said, an import of your data and proc means on it resulted in the same value in Excel and SAS of -0.003697452 for Jan 1973.
You haven't shown the rest of your code but my *GUESS* is that you haven't formatted the date in the way SAS expects it.
Try running a proc means on your data to see if the output matches your Excel.
This assumes that the date is a SAS date.
proc means data=input2 n sum;
format date monyy7.;
07-31-2014 02:38 PM
BTW I don't think you should sum returns over the month, I think they're multiplicative to get an effective monthly return.
The summation would underestimate the total return if invested for the whole month.