Help using Base SAS procedures

Proc Expand

New Contributor
Posts: 2

Proc Expand

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;


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


Super User
Posts: 17,794

Re: Proc Expand

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

New Contributor
Posts: 2

Re: Proc Expand


My apologies. Here are my code and results:


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

set usd.dly_2;

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


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

convert dly_ret/observed=total;

id date;

by yr;


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


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


Super User
Posts: 17,794

Re: Proc Expand

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;


Super User
Posts: 17,794

Re: Proc Expand

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.

Ask a Question
Discussion stats
  • 4 replies
  • 2 in conversation