chiaihuang Tracker
https://communities.sas.com/kntur85557/tracker
chiaihuang TrackerSat, 20 Jul 2024 12:08:50 GMT2024-07-20T12:08:50ZRe: Creating and summarizing monthly portfolios
https://communities.sas.com/t5/SAS-Programming/Creating-and-summarizing-monthly-portfolios/m-p/332070#M74709
<P>Sorry for the unclarity.</P><P>Quintile portfolios mean five portfolios based on the quintile variable which has values of 0-4. I do not need rebalancing or compounding, just simple returns. I'll put it this way, I have five groups labeled 0-4, and each group needs to be further subgrouped by year and month, and each month's equal-weighted returns and value-weighted returns are the simple average returns and market value (MV) weighted average returns, respectively, of the individual stocks that month. There is no need to consider starting value, because everything will be in terms of percentage returns. Also, they are monthly portfolios, so literally it means holding for the month only. Number of stocks in each portfolio depends on the data, they are not the same for each portfolio, but I'd like to include only portfolios have more than 10 stocks. The following is what the actual dataset looks like.</P><P> </P><TABLE><TBODY><TR><TD>Company</TD><TD>Year</TD><TD>Quarter</TD><TD>EPS</TD><TD>EarnRank</TD><TD>Date</TD><TD>date_pred</TD><TD>year_stated</TD><TD>month</TD><TD>MV</TD><TD>Return</TD><TD>retpercent</TD><TD>rank_er</TD></TR><TR><TD>X13194E</TD><TD>2009</TD><TD>4</TD><TD>-1.36</TD><TD>9.4</TD><TD>18484</TD><TD>18849</TD><TD>2009</TD><TD>8</TD><TD>5706.43</TD><TD>-0.04531</TD><TD>-4.53138</TD><TD>2</TD></TR><TR><TD>X13194E</TD><TD>2010</TD><TD>1</TD><TD>-4.54</TD><TD>8.6</TD><TD>18564</TD><TD>18929</TD><TD>2010</TD><TD>10</TD><TD>5819.21</TD><TD>0.007602</TD><TD>0.760184</TD><TD>2</TD></TR><TR><TD>X13194E</TD><TD>2010</TD><TD>2</TD><TD>4.86</TD><TD>11.6</TD><TD>18655</TD><TD>19020</TD><TD>2010</TD><TD>1</TD><TD>6811.63</TD><TD> </TD><TD> </TD><TD>2</TD></TR><TR><TD>X13194E</TD><TD>2010</TD><TD>3</TD><TD>11</TD><TD>15.8</TD><TD>18380</TD><TD>18745</TD><TD>2010</TD><TD>4</TD><TD>5864.32</TD><TD>-0.05186</TD><TD>-5.18571</TD><TD>2</TD></TR><TR><TD>X13194E</TD><TD>2010</TD><TD>4</TD><TD>4.34</TD><TD>7.2</TD><TD>18849</TD><TD>19214</TD><TD>2010</TD><TD>8</TD><TD>7014.63</TD><TD>0.016144</TD><TD>1.614413</TD><TD>2</TD></TR><TR><TD>X13194E</TD><TD>2011</TD><TD>1</TD><TD>-5.64</TD><TD>7.6</TD><TD>18928</TD><TD>19293</TD><TD>2011</TD><TD>10</TD><TD>7014.63</TD><TD>0.003276</TD><TD>0.327562</TD><TD>2</TD></TR><TR><TD>X13194E</TD><TD>2011</TD><TD>2</TD><TD>0.27</TD><TD>11.4</TD><TD>19022</TD><TD>19387</TD><TD>2011</TD><TD>1</TD><TD>7691.28</TD><TD>0.042808</TD><TD>4.280803</TD><TD>2</TD></TR><TR><TD>X13194E</TD><TD>2011</TD><TD>3</TD><TD>10.14</TD><TD>16</TD><TD>18745</TD><TD>19110</TD><TD>2011</TD><TD>4</TD><TD>7713.83</TD><TD>-0.01756</TD><TD>-1.75573</TD><TD>2</TD></TR><TR><TD>X13194E</TD><TD>2011</TD><TD>4</TD><TD>7.94</TD><TD>8.8</TD><TD>19214</TD><TD>19579</TD><TD>2011</TD><TD>8</TD><TD>8819.04</TD><TD>-0.03526</TD><TD>-3.52556</TD><TD>2</TD></TR><TR><TD>X13194E</TD><TD>2012</TD><TD>1</TD><TD>0.71</TD><TD>6.4</TD><TD>19296</TD><TD>19661</TD><TD>2012</TD><TD>10</TD><TD>9292.69</TD><TD>0.061905</TD><TD>6.190519</TD><TD>2</TD></TR><TR><TD>X13194E</TD><TD>2012</TD><TD>2</TD><TD>7.9</TD><TD>10</TD><TD>19388</TD><TD>19753</TD><TD>2012</TD><TD>1</TD><TD>9450.57</TD><TD>0.036804</TD><TD>3.6804</TD><TD>2</TD></TR><TR><TD>X13194E</TD><TD>2012</TD><TD>3</TD><TD>13.36</TD><TD>16.4</TD><TD>19110</TD><TD>19475</TD><TD>2012</TD><TD>4</TD><TD>9112.25</TD><TD>0.097753</TD><TD>9.775325</TD><TD>2</TD></TR><TR><TD>X13194E</TD><TD>2012</TD><TD>4</TD><TD>3.21</TD><TD>10.2</TD><TD>19578</TD><TD>19943</TD><TD>2012</TD><TD>8</TD><TD>9105.9</TD><TD>-0.0115</TD><TD>-1.14952</TD><TD>2</TD></TR><TR><TD>X13194E</TD><TD>2013</TD><TD>1</TD><TD>6.385</TD><TD>5.6</TD><TD>19659</TD><TD>20024</TD><TD>2013</TD><TD>10</TD><TD>9681.44</TD><TD>0.010461</TD><TD>1.04607</TD><TD>2</TD></TR><TR><TD>X13194E</TD><TD>2013</TD><TD>2</TD><TD>13.509</TD><TD>9.8</TD><TD>19753</TD><TD>20118</TD><TD>2013</TD><TD>1</TD><TD>9701.99</TD><TD>-0.03024</TD><TD>-3.02444</TD><TD>2</TD></TR><TR><TD>X13194E</TD><TD>2013</TD><TD>3</TD><TD>16.725</TD><TD>15.6</TD><TD>19474</TD><TD>19839</TD><TD>2013</TD><TD>4</TD><TD>9044.58</TD><TD>-0.01728</TD><TD>-1.72788</TD><TD>2</TD></TR><TR><TD>X13194E</TD><TD>2013</TD><TD>4</TD><TD>7.213</TD><TD>10</TD><TD>19942</TD><TD>20307</TD><TD>2013</TD><TD>8</TD><TD>10154.21</TD><TD>-0.02736</TD><TD>-2.73604</TD><TD>2</TD></TR><TR><TD>X13194E</TD><TD>2014</TD><TD>1</TD><TD>6.53</TD><TD>5.6</TD><TD>20023</TD><TD>20388</TD><TD>2014</TD><TD>10</TD><TD>10544.75</TD><TD>0.050631</TD><TD>5.063119</TD><TD>2</TD></TR><TR><TD>X13194E</TD><TD>2014</TD><TD>2</TD><TD>9.966</TD><TD>10.4</TD><TD>20117</TD><TD>20482</TD><TD>2014</TD><TD>1</TD><TD>9311.45</TD><TD>-0.1122</TD><TD>-11.2196</TD><TD>2</TD></TR><TR><TD>X13194E</TD><TD>2014</TD><TD>3</TD><TD>17.65</TD><TD>15.4</TD><TD>19841</TD><TD>20206</TD><TD>2014</TD><TD>4</TD><TD>10298.09</TD><TD>0.055309</TD><TD>5.530861</TD><TD>2</TD></TR><TR><TD>X13194E</TD><TD>2014</TD><TD>4</TD><TD>9.989</TD><TD>8</TD><TD>20310</TD><TD>20675</TD><TD>2014</TD><TD>8</TD><TD>9229.23</TD><TD>-0.0302</TD><TD>-3.02009</TD><TD>2</TD></TR><TR><TD>X13194E</TD><TD>2015</TD><TD>1</TD><TD>8.536</TD><TD>4.8</TD><TD>20390</TD><TD>20755</TD><TD>2015</TD><TD>10</TD><TD>9969.21</TD><TD>0.049979</TD><TD>4.997879</TD><TD>2</TD></TR></TBODY></TABLE><P> </P><P> </P><P> </P>Mon, 13 Feb 2017 05:33:52 GMThttps://communities.sas.com/t5/SAS-Programming/Creating-and-summarizing-monthly-portfolios/m-p/332070#M74709chiaihuang2017-02-13T05:33:52ZCreating and summarizing monthly portfolios
https://communities.sas.com/t5/SAS-Programming/Creating-and-summarizing-monthly-portfolios/m-p/332049#M74703
<P>Hi everyone,</P><P> </P><P>I have the following variables:</P><P>CompanyID | Year | Month | EarnRank | MV | Return | EarnRank Quintile (0-4)</P><P> </P><P>I wish to find the following:</P><P>1. The average monthly return from each earnrank quintile ie. for quintile 1, each month, buying the companies with EarnRank quintile=0</P><P>2. A portfolio of selling quintile 0 and buying quintile 4 each month</P><P>3. Additional limitation: only include months has more than 10 companies</P><P>4. I also need both equal weighted and value weighted portfolios</P><P> </P><P>I'm not sure if this explanation is clear enough, please let me know if it is not. Thank you all in advance.</P><P> </P><P>Henry</P>Mon, 13 Feb 2017 04:28:00 GMThttps://communities.sas.com/t5/SAS-Programming/Creating-and-summarizing-monthly-portfolios/m-p/332049#M74703chiaihuang2017-02-13T04:28:00Zgetting max and min value by group
https://communities.sas.com/t5/SAS-Programming/getting-max-and-min-value-by-group/m-p/324464#M72047
<P>Hi everyone,</P><P> </P><P>I have the following data:</P><P>Company ID | Year | Quarter | EPS | EPSRank</P><P> </P><P>I would like to create a new variable which is the difference between the max(EPSRank) and min(EPSRank) for each year. Can anyone help with this? Thanks in advance.</P><P> </P><P>Henry</P>Fri, 13 Jan 2017 06:00:20 GMThttps://communities.sas.com/t5/SAS-Programming/getting-max-and-min-value-by-group/m-p/324464#M72047chiaihuang2017-01-13T06:00:20Z