<html><head><title>Return calculation for panel data structure</title>
<META http-equiv=Content-Type content="text/html; charset=windows-1251">
</head>
<body>
<span style=" font-family:'default'; font-size: 10pt;">Dear all,<br>
<br>
I have a very simple question and i haven't been able to code it out. It's a simple return calculation of a form:<br>
R(t)=(P(t)-P(t-1))/P(t-1) or the same as ratio of prices minus one. However it's in the panel data, where stocks belong to say a fund or some other grouping variable.<br>
<br>
<br>
</span><table bordercolor="#000000">
<tr>
<td width=69 height=19><span style=" font-family:'default'; font-size: 10pt;">ticker</td>
<td width=69 height=19><span style=" font-family:'default'; font-size: 10pt;">date_f</td>
<td width=69 height=19><br>
</td>
<td width=69 height=19><span style=" font-family:'default'; font-size: 10pt;">date</td>
<td width=69 height=19><span style=" font-family:'default'; font-size: 10pt;">prc</td>
<td width=69 height=19><span style=" font-family:'default'; font-size: 10pt;">iticker</td>
</tr>
<tr>
<td width=69 height=19><span style=" font-family:'default'; font-size: 10pt;">A</td>
<td width=69 height=19><span style=" font-family:'default'; font-size: 10pt;">20-Jun-03</td>
<td width=69 height=19><br>
</td>
<td width=69 height=19><span style=" font-family:'default'; font-size: 10pt;">15876</td>
<td width=69 height=19><span style=" font-family:'default'; font-size: 10pt;">19.06</td>
<td width=69 height=19><span style=" font-family:'default'; font-size: 10pt;">IXT</td>
</tr>
<tr>
<td width=69 height=19><span style=" font-family:'default'; font-size: 10pt;">A</td>
<td width=69 height=19><span style=" font-family:'default'; font-size: 10pt;">23-Jun-03</td>
<td width=69 height=19><br>
</td>
<td width=69 height=19><span style=" font-family:'default'; font-size: 10pt;">15879</td>
<td width=69 height=19><span style=" font-family:'default'; font-size: 10pt;">18.89</td>
<td width=69 height=19><span style=" font-family:'default'; font-size: 10pt;">IXT</td>
</tr>
<tr>
<td width=69 height=19><span style=" font-family:'default'; font-size: 10pt;">A</td>
<td width=69 height=19><span style=" font-family:'default'; font-size: 10pt;">24-Jun-03</td>
<td width=69 height=19><br>
</td>
<td width=69 height=19><span style=" font-family:'default'; font-size: 10pt;">15880</td>
<td width=69 height=19><span style=" font-family:'default'; font-size: 10pt;">18.75</td>
<td width=69 height=19><span style=" font-family:'default'; font-size: 10pt;">IXT</td>
</tr>
<tr>
<td width=69 height=19><span style=" font-family:'default'; font-size: 10pt;">A</td>
<td width=69 height=19><span style=" font-family:'default'; font-size: 10pt;">25-Jun-03</td>
<td width=69 height=19><br>
</td>
<td width=69 height=19><span style=" font-family:'default'; font-size: 10pt;">15881</td>
<td width=69 height=19><span style=" font-family:'default'; font-size: 10pt;">18.92</td>
<td width=69 height=19><span style=" font-family:'default'; font-size: 10pt;">IXT</td>
</tr>
<tr>
<td width=69 height=19><span style=" font-family:'default'; font-size: 10pt;">A</td>
<td width=69 height=19><span style=" font-family:'default'; font-size: 10pt;">26-Jun-03</td>
<td width=69 height=19><br>
</td>
<td width=69 height=19><span style=" font-family:'default'; font-size: 10pt;">15882</td>
<td width=69 height=19><span style=" font-family:'default'; font-size: 10pt;">19.35</td>
<td width=69 height=19><span style=" font-family:'default'; font-size: 10pt;">IXT</td>
</tr>
<tr>
<td width=69 height=19><span style=" font-family:'default'; font-size: 10pt;">A</td>
<td width=69 height=19><span style=" font-family:'default'; font-size: 10pt;">27-Jun-03</td>
<td width=69 height=19><br>
</td>
<td width=69 height=19><span style=" font-family:'default'; font-size: 10pt;">15883</td>
<td width=69 height=19><span style=" font-family:'default'; font-size: 10pt;">19.55</td>
<td width=69 height=19><span style=" font-family:'default'; font-size: 10pt;">IXT</td>
</tr>
<tr>
<td width=69 height=19><span style=" font-family:'default'; font-size: 10pt;">AA</td>
<td width=69 height=19><span style=" font-family:'default'; font-size: 10pt;">20-Jun-03</td>
<td width=69 height=19><br>
</td>
<td width=69 height=19><span style=" font-family:'default'; font-size: 10pt;">15876</td>
<td width=69 height=19><span style=" font-family:'default'; font-size: 10pt;">26.24</td>
<td width=69 height=19><span style=" font-family:'default'; font-size: 10pt;">IXB</td>
</tr>
<tr>
<td width=69 height=19><span style=" font-family:'default'; font-size: 10pt;">AA</td>
<td width=69 height=19><span style=" font-family:'default'; font-size: 10pt;">23-Jun-03</td>
<td width=69 height=19><br>
</td>
<td width=69 height=19><span style=" font-family:'default'; font-size: 10pt;">15879</td>
<td width=69 height=19><span style=" font-family:'default'; font-size: 10pt;">25.07</td>
<td width=69 height=19><span style=" font-family:'default'; font-size: 10pt;">IXB</td>
</tr>
<tr>
<td width=69 height=19><span style=" font-family:'default'; font-size: 10pt;">AA</td>
<td width=69 height=19><span style=" font-family:'default'; font-size: 10pt;">24-Jun-03</td>
<td width=69 height=19><br>
</td>
<td width=69 height=19><span style=" font-family:'default'; font-size: 10pt;">15880</td>
<td width=69 height=19><span style=" font-family:'default'; font-size: 10pt;">24.96</td>
<td width=69 height=19><span style=" font-family:'default'; font-size: 10pt;">IXB</td>
</tr>
<tr>
<td width=69 height=19><span style=" font-family:'default'; font-size: 10pt;">AA</td>
<td width=69 height=19><span style=" font-family:'default'; font-size: 10pt;">25-Jun-03</td>
<td width=69 height=19><br>
</td>
<td width=69 height=19><span style=" font-family:'default'; font-size: 10pt;">15881</td>
<td width=69 height=19><span style=" font-family:'default'; font-size: 10pt;">24.51</td>
<td width=69 height=19><span style=" font-family:'default'; font-size: 10pt;">IXB</td>
</tr>
<tr>
<td width=69 height=19><span style=" font-family:'default'; font-size: 10pt;">AA</td>
<td width=69 height=19><span style=" font-family:'default'; font-size: 10pt;">26-Jun-03</td>
<td width=69 height=19><br>
</td>
<td width=69 height=19><span style=" font-family:'default'; font-size: 10pt;">15882</td>
<td width=69 height=19><span style=" font-family:'default'; font-size: 10pt;">25.24</td>
<td width=69 height=19><span style=" font-family:'default'; font-size: 10pt;">IXB</td>
</tr>
<tr>
<td width=69 height=19><span style=" font-family:'default'; font-size: 10pt;">AA</td>
<td width=69 height=19><span style=" font-family:'default'; font-size: 10pt;">27-Jun-03</td>
<td width=69 height=19><br>
</td>
<td width=69 height=19><span style=" font-family:'default'; font-size: 10pt;">15883</td>
<td width=69 height=19><span style=" font-family:'default'; font-size: 10pt;">25.09</td>
<td width=69 height=19><span style=" font-family:'default'; font-size: 10pt;">IXB</td>
</tr>
<tr>
<td width=69 height=19><span style=" font-family:'default'; font-size: 10pt;">AA</td>
<td width=69 height=19><span style=" font-family:'default'; font-size: 10pt;">30-Jun-03</td>
<td width=69 height=19><br>
</td>
<td width=69 height=19><span style=" font-family:'default'; font-size: 10pt;">15886</td>
<td width=69 height=19><span style=" font-family:'default'; font-size: 10pt;">25.5</td>
<td width=69 height=19><span style=" font-family:'default'; font-size: 10pt;">IXB</td>
</tr>
</table>
<br><br>
<span style=" font-family:'default'; font-size: 10pt;">In SAS, I would do it this way:<br>
<br>
<span style=" color: #000080;"><b>data work.1</b><span style=" color: #000000;">; /* This Calcs the excluded index return for INDEX constructed with PRICES (PR)!!!! */<br>
set work.1;<br>
by ticker iticker date_f ; /*sorting by stock, fund and date */<br>
<br>
lag_prc=lag(prc); /* creating the lagged price variable<br>
<br>
if first.iticker then /* if the it's a first date for the grouped unit then.. */<br>
lag_prc= <span style=" color: #000080;"><b>. </b><span style=" color: #000000;">; /* setting the first lagged value to missing if the first date for the grouped unit */<br>
return = (prc - lag_prc)/ lag_prc;<br>
drop lag_prc;<br>
<span style=" color: #000080;"><b>run</b><span style=" color: #000000;">;<br>
<br>
The end result would be the same dataset with just one more column for returns for each stock, fund, date combination with the first return for the date set to missing.<br>
I tried Return.calculate type of funcs from performance analytics and other solutions using plyr or loops, but they are all too bulky.<br>
<br>
Any ideas how to accomplish this? Would appreciate any hints,<br>
<br>
Arsenio</body>