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;

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?

Karthik

Super User
Posts: 23,700

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

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.00369745 21973 -0.0227457 31973 0.00824313 41973 -0.000343374 51973 -0.010354 61973 -0.0102187 71973 0.000427683 81973 0.0195624 91973 -0.00165992 101973 0.00217638 111973 0.0147721 121973 0.00364333

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

Karthik

Super User
Posts: 23,700

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

run;

Super User
Posts: 23,700