Proc Expand

[karthikmax]

Hello all,

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;

     convert dly_ret\observed=total;

     id date;

     by yr;

     run;

- 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!

Karthik

[Reeza]

You should post some sample code and data that result in the different numbers in SAS vs Excel.

[karthikmax]

Reeza,

My apologies. Here are my code and results:

Code:

data usd.dly_5(drop= lag_DTWEXM count lag_count numday);

set usd.dly_2;

if(dly_ret=".") then dly_ret=0;

run;

proc expand data=usd.dly_5 from=day to=month out=usd.dly_6;

convert dly_ret/observed=total;

id date;

by yr;

run;

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:

mth/yr              dly_ret

11973	-0.003697452
21973	-0.022745714
31973	0.008243129
41973	-0.000343374
51973	-0.010353998
61973	-0.010218709
71973	0.000427683
81973	0.019562443
91973	-0.001659921
101973	0.002176381
111973	0.014772061
121973	0.003643333

I have attached the daily raw data for the year 1973. Please let me know if you need anything else. Appreciate your help.

Karthik

[Reeza]

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

class date;

var dly_ret;

run;

[Reeza]

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.