turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- SAS Procedures
- /
- Proc Expand

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-30-2014 11:08 AM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to karthikmax

07-30-2014 11:52 AM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Reeza

07-30-2014 07:39 PM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to karthikmax

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

class date;

var dly_ret;

run;

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to karthikmax

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.