About bera00

bera00 · ‎02-28-2018

Thank you so much this method works for my data

bera00 · ‎02-28-2018

Thank you for your resonse. this method works

bera00 · ‎02-27-2018

Hello all, I would appreciate your help because I am a bit stuck in the sas programming. I have a data that involves the holdings composition of different funds (fund_id) at each quarter (stock_id , number and prices) For each fund_id : I would like to know for each stock held by a fund_id at each quarter , if the stock existed on a previous quarter or not (i.e. if the fund held the same stock in a previous quarter). If it is the case ( i.e. if the stock existed on a previous quarter for the same fund), I should recuperate the number and price of that stock in the previous quarter and creates for them new variables. If it's not the case I do nothing or I put zeros for the newly created variables. my variables are as follow ( what I have): date fund_id stock_id number price 3/31/2005 A 8916108 500 45 3/31/2005 A 226406106 20000 13.73 3/31/2005 A 25243Q205 9000 56.9 3/31/2005 A 292505104 15000 70.42 3/31/2005 A 633067103 4000 43.305 3/31/2005 A 767204100 1500 129.75 3/31/2005 A 874039100 700 32 3/31/2005 A 5458954 900 85 3/31/2005 A 874039100 1000 100 6/30/2005 A 25243Q205 2000 120 6/30/2005 A 292505104 500 150 6/30/2005 A 87403645 400 12 6/30/2005 A 645646 3000 11 6/30/2005 A 5454545 1200 13 3/31/2005 B 633067103 5000 43.305 3/31/2005 B 767204100 1500 129.75 3/31/2005 B 874039100 700 15 3/31/2005 B 77778 900 20 3/31/2005 B 121212 1000 40 3/31/2005 B 131313 2000 88 6/30/2005 B 44456 3200 45 6/30/2005 B 767204100 10000 36 6/30/2005 B 874039100 3333 100 6/30/2005 B 466666 20 14 I posted an excel sample data What I want : date fund_id stock_id number price number1 price1 3/31/2005 A 8916108 500 45 0 0 3/31/2005 A 226406106 20000 13.73 0 0 3/31/2005 A 25243Q205 9000 56.9 0 0 3/31/2005 A 292505104 15000 70.42 0 0 3/31/2005 A 633067103 4000 43.305 0 0 3/31/2005 A 767204100 1500 129.75 0 0 3/31/2005 A 874039100 700 32 0 0 3/31/2005 A 5458954 900 85 0 0 3/31/2005 A 874039100 1000 100 0 0 6/30/2005 A 25243Q205 2000 120 9000 56.9 6/30/2005 A 292505104 500 150 15000 70.42 6/30/2005 A 87403645 400 12 0 0 6/30/2005 A 645646 3000 11 0 0 6/30/2005 A 5454545 1200 13 0 0 3/31/2005 B 633067103 5000 43.305 0 0 3/31/2005 B 767204100 1500 129.75 0 0 3/31/2005 B 874039100 700 15 0 0 3/31/2005 B 77778 900 20 0 0 3/31/2005 B 121212 1000 40 0 0 3/31/2005 B 131313 2000 88 0 0 Thank you in advance for your precious help.

bera00 · ‎02-27-2018

Hello all, I would appreciate your help because I am a bit stuck in the sas programming. I have a data that involves the holdings composition of different funds (fund_id) at each quarter (stock_id , number and prices) For each fund_id : I would like to know for each stock held by a fund_id at each quarter , if the stock existed on a previous quarter or not (i.e. if the fund held the same stock in a previous quarter). If it is the case ( i.e. if the stock existed on a previous quarter for the same fund), I should recuperate the number and price of that stock in the previous quarter and creates for them new variables. If it's not the case I do nothing or I put zeros for the newly created variables. my variables are as follow ( what I have): date fund_id stock_id number price 3/31/2005 A 8916108 500 45 3/31/2005 A 226406106 20000 13.73 3/31/2005 A 25243Q205 9000 56.9 3/31/2005 A 292505104 15000 70.42 3/31/2005 A 633067103 4000 43.305 3/31/2005 A 767204100 1500 129.75 3/31/2005 A 874039100 700 32 3/31/2005 A 5458954 900 85 3/31/2005 A 874039100 1000 100 6/30/2005 A 25243Q205 2000 120 6/30/2005 A 292505104 500 150 6/30/2005 A 87403645 400 12 6/30/2005 A 645646 3000 11 6/30/2005 A 5454545 1200 13 3/31/2005 B 633067103 5000 43.305 3/31/2005 B 767204100 1500 129.75 3/31/2005 B 874039100 700 15 3/31/2005 B 77778 900 20 3/31/2005 B 121212 1000 40 3/31/2005 B 131313 2000 88 6/30/2005 B 44456 3200 45 6/30/2005 B 767204100 10000 36 6/30/2005 B 874039100 3333 100 6/30/2005 B 466666 20 14 I posted an excel sample data What I want : date fund_id stock_id number price number1 price1 3/31/2005 A 8916108 500 45 0 0 3/31/2005 A 226406106 20000 13.73 0 0 3/31/2005 A 25243Q205 9000 56.9 0 0 3/31/2005 A 292505104 15000 70.42 0 0 3/31/2005 A 633067103 4000 43.305 0 0 3/31/2005 A 767204100 1500 129.75 0 0 3/31/2005 A 874039100 700 32 0 0 3/31/2005 A 5458954 900 85 0 0 3/31/2005 A 874039100 1000 100 0 0 6/30/2005 A 25243Q205 2000 120 9000 56.9 6/30/2005 A 292505104 500 150 15000 70.42 6/30/2005 A 87403645 400 12 0 0 6/30/2005 A 645646 3000 11 0 0 6/30/2005 A 5454545 1200 13 0 0 3/31/2005 B 633067103 5000 43.305 0 0 3/31/2005 B 767204100 1500 129.75 0 0 3/31/2005 B 874039100 700 15 0 0 3/31/2005 B 77778 900 20 0 0 3/31/2005 B 121212 1000 40 0 0 3/31/2005 B 131313 2000 88 0 0 Thank you in advance for your precious help.

bera00 · ‎08-04-2017

Thank youu again

bera00 · ‎08-03-2017

have a dataset as following : For each stock id at each date, I have its relative price for which I have calculated its return. The problem is that my data give recurring prices for the stocks that became inactive after a certain date. I want to remove the recurring prices or to replace these prices by a ‘.’ Thus, I want to check for each id, the prices that are at the end ( to see if I have recurring prices and if so to remove them or to replace them with a dot) Since I have recurring prices, then their relative returns will be zeros. However I cannot delete all returns that are zeros since sometimes I have recurring prices that are not due to the fact that the stock became inactive, for example for id=2 for the period 31 may 2001-31july 2001 . I think the solution would be to start at the end of each period for each stock, by seeing if Rt=0 or if I have recurring price and go up until I have Rt that is different from zero or a different price but I don t know how to do that Your help is much appreciated because I have a huge dataset. Thank you in advance id price return date 1 17.6875 30 31-Jan-00 1 23.75 29.47244 29-Feb-00 1 16.6875 -35.2923 31-Mar-00 1 15.0625 -10.2452 30-Apr-00 1 13.875 -8.21196 31-May-00 1 12 -14.5182 30-Jun-00 1 12 0 31-Jul-00 1 11.25 -6.45385 31-Aug-00 1 11.4375 1.65293 30-Sep-00 1 11.9375 4.278728 31-Oct-00 1 10.375 -14.0286 30-Nov-00 1 12.625 19.62799 31-Dec-00 1 15.19 18.49583 31-Jan-01 1 13.6 -11.0568 28-Feb-01 1 12.76 -6.37545 31-Mar-01 1 11.23 -12.7727 30-Apr-01 1 14 22.04686 31-May-01 1 17.1 20.00211 30-Jun-01 1 16.52 -3.45067 31-Jul-01 1 17.06 3.216477 31-Aug-01 1 8.01 -75.6046 30-Sep-01 1 7.6 -5.25425 31-Oct-01 1 8.15 6.986968 30-Nov-01 1 9.01 10.03171 31-Dec-01 1 8.6 -4.65729 31-Jan-02 1 7.4 -15.0282 28-Feb-02 1 10.77 37.52845 31-Mar-02 1 12.74 16.79822 30-Apr-02 1 11.44 -10.7631 31-May-02 1 10.2 -11.4728 30-Jun-02 1 8.74 -15.4478 31-Jul-02 1 6.2 -34.3361 31-Aug-02 1 4.75 -26.6405 30-Sep-02 1 3.91 -19.4607 31-Oct-02 1 5.5 34.12107 30-Nov-02 1 5 -9.53102 31-Dec-02 1 5 0 31-Jan-03 1 5 0 28-Feb-03 1 5 0 31-Mar-03 1 5 0 30-Apr-03 1 5 0 31-May-03 1 5 0 30-Jun-03 1 5 0 31-Jul-03 1 5 0 31-Aug-03 1 5 0 30-Sep-03 1 5 0 31-Oct-03 1 5 0 30-Nov-03 1 5 0 31-Dec-03 1 5 0 31-Jan-04 1 5 0 29-Feb-04 1 5 0 31-Mar-04 1 5 0 30-Apr-04 1 5 0 31-May-04 1 5 0 30-Jun-04 1 5 0 31-Jul-04 1 5 0 31-Aug-04 1 5 0 30-Sep-04 1 5 0 31-Oct-04 1 5 0 30-Nov-04 1 5 0 31-Dec-04 2 18.015 1 31-Jan-00 2 17.77 -1.36931 29-Feb-00 2 19.55 9.546364 31-Mar-00 2 18.865 -3.56669 30-Apr-00 2 20.09 6.291383 31-May-00 2 22.47 11.19589 30-Jun-00 2 21.565 -4.11095 31-Jul-00 2 22.985 6.37702 31-Aug-00 2 20.11 -13.3625 30-Sep-00 2 20.84 3.565701 31-Oct-00 2 20.245 -2.89664 30-Nov-00 2 21.28 4.985981 31-Dec-00 2 21.34 0.281558 31-Jan-01 2 22.37 4.713753 28-Feb-01 2 23.255 3.879939 31-Mar-01 2 22 -5.54777 30-Apr-01 2 22 0 31-May-01 2 22 0 30-Jun-01 2 22 0 31-Jul-01 2 27.475 7.990623 31-Aug-01 2 30.115 9.174689 30-Sep-01 2 25.19 -17.8576 31-Oct-01 2 26.285 4.255134 30-Nov-01 2 31.39 17.74909 31-Dec-01 2 29.815 -5.14777 31-Jan-02 2 34.88 15.6902 28-Feb-02 2 37.16 6.331931 31-Mar-02 2 35.6 -4.28873 30-Apr-02 2 39.555 10.53465 31-May-02 2 44.665 12.14981 30-Jun-02 2 43.895 -1.73898 31-Jul-02 2 41.88 -4.6992 31-Aug-02 2 39.03 -7.04778 30-Sep-02 2 35.05 -10.7555 31-Oct-02 2 37.63 7.102599 30-Nov-02 2 39.06 3.729732 31-Dec-02 2 44.71 13.50983 31-Jan-03 2 56.69 23.74006 28-Feb-03 2 52.2 -8.25153 31-Mar-03 2 54.16 3.686013 30-Apr-03 2 53.12 -1.93891 31-May-03 2 54.61 2.766351 30-Jun-03 2 53.46 -2.12833 31-Jul-03 2 54.51 1.945046 31-Aug-03 2 46.34 -16.2379 30-Sep-03 2 50.47 8.537358 31-Oct-03 2 53.38 5.605704 30-Nov-03 2 51.91 -2.79247 31-Dec-03 2 50.76 -2.24028 31-Jan-04 2 44.5 -13.1619 29-Feb-04 2 44.32 -0.40531 31-Mar-04 2 41.9 -5.61502 30-Apr-04 2 45.94 9.205037 31-May-04 2 38.54 -17.564 30-Jun-04 2 38.65 0.285011 31-Jul-04 2 40.7 5.168132 31-Aug-04 2 44.175 8.193093 30-Sep-04 2 45 1.850347 31-Oct-04 2 48 6.453852 30-Nov-04 2 50 4.082199 31-Dec-04 what I would like to have : id price return date 1 17.6875 30 31-Jan-00 1 23.75 29.47244 29-Feb-00 1 16.6875 -35.2923 31-Mar-00 1 15.0625 -10.2452 30-Apr-00 1 13.875 -8.21196 31-May-00 1 12 -14.5182 30-Jun-00 1 12 0 31-Jul-00 1 11.25 -6.45385 31-Aug-00 1 11.4375 1.65293 30-Sep-00 1 11.9375 4.278728 31-Oct-00 1 10.375 -14.0286 30-Nov-00 1 12.625 19.62799 31-Dec-00 1 15.19 18.49583 31-Jan-01 1 13.6 -11.0568 28-Feb-01 1 12.76 -6.37545 31-Mar-01 1 11.23 -12.7727 30-Apr-01 1 14 22.04686 31-May-01 1 17.1 20.00211 30-Jun-01 1 16.52 -3.45067 31-Jul-01 1 17.06 3.216477 31-Aug-01 1 8.01 -75.6046 30-Sep-01 1 7.6 -5.25425 31-Oct-01 1 8.15 6.986968 30-Nov-01 1 9.01 10.03171 31-Dec-01 1 8.6 -4.65729 31-Jan-02 1 7.4 -15.0282 28-Feb-02 1 10.77 37.52845 31-Mar-02 1 12.74 16.79822 30-Apr-02 1 11.44 -10.7631 31-May-02 1 10.2 -11.4728 30-Jun-02 1 8.74 -15.4478 31-Jul-02 1 6.2 -34.3361 31-Aug-02 1 4.75 -26.6405 30-Sep-02 1 3.91 -19.4607 31-Oct-02 1 5.5 34.12107 30-Nov-02 1 5 -9.53102 31-Dec-02 1 . . 31-Jan-03 1 . . 28-Feb-03 1 . . 31-Mar-03 1 . . 30-Apr-03 1 . . 31-May-03 1 . . 30-Jun-03 1 . . 31-Jul-03 1 . . 31-Aug-03 1 . . 30-Sep-03 1 . . 31-Oct-03 1 . . 30-Nov-03 1 . . 31-Dec-03 1 . . 31-Jan-04 1 . . 29-Feb-04 1 . . 31-Mar-04 1 . . 30-Apr-04 1 . . 31-May-04 1 . . 30-Jun-04 1 . . 31-Jul-04 1 . . 31-Aug-04 1 . . 30-Sep-04 1 . . 31-Oct-04 1 . . 30-Nov-04 1 . . 31-Dec-04 2 18.015 1 31-Jan-00 2 17.77 -1.36931 29-Feb-00 2 19.55 9.546364 31-Mar-00 2 18.865 -3.56669 30-Apr-00 2 20.09 6.291383 31-May-00 2 22.47 11.19589 30-Jun-00 2 21.565 -4.11095 31-Jul-00 2 22.985 6.37702 31-Aug-00 2 20.11 -13.3625 30-Sep-00 2 20.84 3.565701 31-Oct-00 2 20.245 -2.89664 30-Nov-00 2 21.28 4.985981 31-Dec-00 2 21.34 0.281558 31-Jan-01 2 22.37 4.713753 28-Feb-01 2 23.255 3.879939 31-Mar-01 2 22 -5.54777 30-Apr-01 2 22 0 31-May-01 2 22 0 30-Jun-01 2 25.365 14.23278 31-Jul-01 2 27.475 7.990623 31-Aug-01 2 30.115 9.174689 30-Sep-01 2 25.19 -17.8576 31-Oct-01 2 26.285 4.255134 30-Nov-01 2 31.39 17.74909 31-Dec-01 2 29.815 -5.14777 31-Jan-02 2 34.88 15.6902 28-Feb-02 2 37.16 6.331931 31-Mar-02 2 35.6 -4.28873 30-Apr-02 2 39.555 10.53465 31-May-02 2 44.665 12.14981 30-Jun-02 2 43.895 -1.73898 31-Jul-02 2 41.88 -4.6992 31-Aug-02 2 39.03 -7.04778 30-Sep-02 2 35.05 -10.7555 31-Oct-02 2 37.63 7.102599 30-Nov-02 2 39.06 3.729732 31-Dec-02 2 44.71 13.50983 31-Jan-03 2 56.69 23.74006 28-Feb-03 2 52.2 -8.25153 31-Mar-03 2 54.16 3.686013 30-Apr-03 2 53.12 -1.93891 31-May-03 2 54.61 2.766351 30-Jun-03 2 53.46 -2.12833 31-Jul-03 2 54.51 1.945046 31-Aug-03 2 46.34 -16.2379 30-Sep-03 2 50.47 8.537358 31-Oct-03 2 53.38 5.605704 30-Nov-03 2 51.91 -2.79247 31-Dec-03 2 50.76 -2.24028 31-Jan-04 2 44.5 -13.1619 29-Feb-04 2 44.32 -0.40531 31-Mar-04 2 41.9 -5.61502 30-Apr-04 2 45.94 9.205037 31-May-04 2 38.54 -17.564 30-Jun-04 2 38.65 0.285011 31-Jul-04 2 40.7 5.168132 31-Aug-04 2 44.175 8.193093 30-Sep-04 2 45 1.850347 31-Oct-04 2 48 6.453852 30-Nov-04 2 50 4.082199 31-Dec-04

bera00 · ‎08-03-2017

I have a dataset as following : For each stock id at each date, I have its relative price for which I have calculated its return. The problem is that my data give recurring prices for the stocks that became inactive after a certain date. I want to remove the recurring prices or to replace these prices by a ‘.’ Thus, I want to check for each id, the prices that are at the end ( to see if I have recurring prices and if so to remove them or to replace them with a dot) Since I have recurring prices, then their relative returns will be zeros. However I cannot delete all returns that are zeros since sometimes I have recurring prices that are not due to the fact that the stock became inactive, for example for id=2 for the period 31 may 2001-31july 2001 . I think the solution would be to start at the end of each period for each stock, by seeing if Rt=0 or if I have recurring price and go up until I have Rt that is different from zero or a different price but I don t know how to do that Your help is much appreciated because I have a huge dataset. Thank you in advance id price return date 1 17.6875 30 31-Jan-00 1 23.75 29.47244 29-Feb-00 1 16.6875 -35.2923 31-Mar-00 1 15.0625 -10.2452 30-Apr-00 1 13.875 -8.21196 31-May-00 1 12 -14.5182 30-Jun-00 1 12 0 31-Jul-00 1 11.25 -6.45385 31-Aug-00 1 11.4375 1.65293 30-Sep-00 1 11.9375 4.278728 31-Oct-00 1 10.375 -14.0286 30-Nov-00 1 12.625 19.62799 31-Dec-00 1 15.19 18.49583 31-Jan-01 1 13.6 -11.0568 28-Feb-01 1 12.76 -6.37545 31-Mar-01 1 11.23 -12.7727 30-Apr-01 1 14 22.04686 31-May-01 1 17.1 20.00211 30-Jun-01 1 16.52 -3.45067 31-Jul-01 1 17.06 3.216477 31-Aug-01 1 8.01 -75.6046 30-Sep-01 1 7.6 -5.25425 31-Oct-01 1 8.15 6.986968 30-Nov-01 1 9.01 10.03171 31-Dec-01 1 8.6 -4.65729 31-Jan-02 1 7.4 -15.0282 28-Feb-02 1 10.77 37.52845 31-Mar-02 1 12.74 16.79822 30-Apr-02 1 11.44 -10.7631 31-May-02 1 10.2 -11.4728 30-Jun-02 1 8.74 -15.4478 31-Jul-02 1 6.2 -34.3361 31-Aug-02 1 4.75 -26.6405 30-Sep-02 1 3.91 -19.4607 31-Oct-02 1 5.5 34.12107 30-Nov-02 1 5 -9.53102 31-Dec-02 1 5 0 31-Jan-03 1 5 0 28-Feb-03 1 5 0 31-Mar-03 1 5 0 30-Apr-03 1 5 0 31-May-03 1 5 0 30-Jun-03 1 5 0 31-Jul-03 1 5 0 31-Aug-03 1 5 0 30-Sep-03 1 5 0 31-Oct-03 1 5 0 30-Nov-03 1 5 0 31-Dec-03 1 5 0 31-Jan-04 1 5 0 29-Feb-04 1 5 0 31-Mar-04 1 5 0 30-Apr-04 1 5 0 31-May-04 1 5 0 30-Jun-04 1 5 0 31-Jul-04 1 5 0 31-Aug-04 1 5 0 30-Sep-04 1 5 0 31-Oct-04 1 5 0 30-Nov-04 1 5 0 31-Dec-04 2 18.015 1 31-Jan-00 2 17.77 -1.36931 29-Feb-00 2 19.55 9.546364 31-Mar-00 2 18.865 -3.56669 30-Apr-00 2 20.09 6.291383 31-May-00 2 22.47 11.19589 30-Jun-00 2 21.565 -4.11095 31-Jul-00 2 22.985 6.37702 31-Aug-00 2 20.11 -13.3625 30-Sep-00 2 20.84 3.565701 31-Oct-00 2 20.245 -2.89664 30-Nov-00 2 21.28 4.985981 31-Dec-00 2 21.34 0.281558 31-Jan-01 2 22.37 4.713753 28-Feb-01 2 23.255 3.879939 31-Mar-01 2 22 -5.54777 30-Apr-01 2 22 0 31-May-01 2 22 0 30-Jun-01 2 22 0 31-Jul-01 2 27.475 7.990623 31-Aug-01 2 30.115 9.174689 30-Sep-01 2 25.19 -17.8576 31-Oct-01 2 26.285 4.255134 30-Nov-01 2 31.39 17.74909 31-Dec-01 2 29.815 -5.14777 31-Jan-02 2 34.88 15.6902 28-Feb-02 2 37.16 6.331931 31-Mar-02 2 35.6 -4.28873 30-Apr-02 2 39.555 10.53465 31-May-02 2 44.665 12.14981 30-Jun-02 2 43.895 -1.73898 31-Jul-02 2 41.88 -4.6992 31-Aug-02 2 39.03 -7.04778 30-Sep-02 2 35.05 -10.7555 31-Oct-02 2 37.63 7.102599 30-Nov-02 2 39.06 3.729732 31-Dec-02 2 44.71 13.50983 31-Jan-03 2 56.69 23.74006 28-Feb-03 2 52.2 -8.25153 31-Mar-03 2 54.16 3.686013 30-Apr-03 2 53.12 -1.93891 31-May-03 2 54.61 2.766351 30-Jun-03 2 53.46 -2.12833 31-Jul-03 2 54.51 1.945046 31-Aug-03 2 46.34 -16.2379 30-Sep-03 2 50.47 8.537358 31-Oct-03 2 53.38 5.605704 30-Nov-03 2 51.91 -2.79247 31-Dec-03 2 50.76 -2.24028 31-Jan-04 2 44.5 -13.1619 29-Feb-04 2 44.32 -0.40531 31-Mar-04 2 41.9 -5.61502 30-Apr-04 2 45.94 9.205037 31-May-04 2 38.54 -17.564 30-Jun-04 2 38.65 0.285011 31-Jul-04 2 40.7 5.168132 31-Aug-04 2 44.175 8.193093 30-Sep-04 2 45 1.850347 31-Oct-04 2 48 6.453852 30-Nov-04 2 50 4.082199 31-Dec-04 what I would like to have : id price return date 1 17.6875 30 31-Jan-00 1 23.75 29.47244 29-Feb-00 1 16.6875 -35.2923 31-Mar-00 1 15.0625 -10.2452 30-Apr-00 1 13.875 -8.21196 31-May-00 1 12 -14.5182 30-Jun-00 1 12 0 31-Jul-00 1 11.25 -6.45385 31-Aug-00 1 11.4375 1.65293 30-Sep-00 1 11.9375 4.278728 31-Oct-00 1 10.375 -14.0286 30-Nov-00 1 12.625 19.62799 31-Dec-00 1 15.19 18.49583 31-Jan-01 1 13.6 -11.0568 28-Feb-01 1 12.76 -6.37545 31-Mar-01 1 11.23 -12.7727 30-Apr-01 1 14 22.04686 31-May-01 1 17.1 20.00211 30-Jun-01 1 16.52 -3.45067 31-Jul-01 1 17.06 3.216477 31-Aug-01 1 8.01 -75.6046 30-Sep-01 1 7.6 -5.25425 31-Oct-01 1 8.15 6.986968 30-Nov-01 1 9.01 10.03171 31-Dec-01 1 8.6 -4.65729 31-Jan-02 1 7.4 -15.0282 28-Feb-02 1 10.77 37.52845 31-Mar-02 1 12.74 16.79822 30-Apr-02 1 11.44 -10.7631 31-May-02 1 10.2 -11.4728 30-Jun-02 1 8.74 -15.4478 31-Jul-02 1 6.2 -34.3361 31-Aug-02 1 4.75 -26.6405 30-Sep-02 1 3.91 -19.4607 31-Oct-02 1 5.5 34.12107 30-Nov-02 1 5 -9.53102 31-Dec-02 1 . . 31-Jan-03 1 . . 28-Feb-03 1 . . 31-Mar-03 1 . . 30-Apr-03 1 . . 31-May-03 1 . . 30-Jun-03 1 . . 31-Jul-03 1 . . 31-Aug-03 1 . . 30-Sep-03 1 . . 31-Oct-03 1 . . 30-Nov-03 1 . . 31-Dec-03 1 . . 31-Jan-04 1 . . 29-Feb-04 1 . . 31-Mar-04 1 . . 30-Apr-04 1 . . 31-May-04 1 . . 30-Jun-04 1 . . 31-Jul-04 1 . . 31-Aug-04 1 . . 30-Sep-04 1 . . 31-Oct-04 1 . . 30-Nov-04 1 . . 31-Dec-04 2 18.015 1 31-Jan-00 2 17.77 -1.36931 29-Feb-00 2 19.55 9.546364 31-Mar-00 2 18.865 -3.56669 30-Apr-00 2 20.09 6.291383 31-May-00 2 22.47 11.19589 30-Jun-00 2 21.565 -4.11095 31-Jul-00 2 22.985 6.37702 31-Aug-00 2 20.11 -13.3625 30-Sep-00 2 20.84 3.565701 31-Oct-00 2 20.245 -2.89664 30-Nov-00 2 21.28 4.985981 31-Dec-00 2 21.34 0.281558 31-Jan-01 2 22.37 4.713753 28-Feb-01 2 23.255 3.879939 31-Mar-01 2 22 -5.54777 30-Apr-01 2 22 0 31-May-01 2 22 0 30-Jun-01 2 25.365 14.23278 31-Jul-01 2 27.475 7.990623 31-Aug-01 2 30.115 9.174689 30-Sep-01 2 25.19 -17.8576 31-Oct-01 2 26.285 4.255134 30-Nov-01 2 31.39 17.74909 31-Dec-01 2 29.815 -5.14777 31-Jan-02 2 34.88 15.6902 28-Feb-02 2 37.16 6.331931 31-Mar-02 2 35.6 -4.28873 30-Apr-02 2 39.555 10.53465 31-May-02 2 44.665 12.14981 30-Jun-02 2 43.895 -1.73898 31-Jul-02 2 41.88 -4.6992 31-Aug-02 2 39.03 -7.04778 30-Sep-02 2 35.05 -10.7555 31-Oct-02 2 37.63 7.102599 30-Nov-02 2 39.06 3.729732 31-Dec-02 2 44.71 13.50983 31-Jan-03 2 56.69 23.74006 28-Feb-03 2 52.2 -8.25153 31-Mar-03 2 54.16 3.686013 30-Apr-03 2 53.12 -1.93891 31-May-03 2 54.61 2.766351 30-Jun-03 2 53.46 -2.12833 31-Jul-03 2 54.51 1.945046 31-Aug-03 2 46.34 -16.2379 30-Sep-03 2 50.47 8.537358 31-Oct-03 2 53.38 5.605704 30-Nov-03 2 51.91 -2.79247 31-Dec-03 2 50.76 -2.24028 31-Jan-04 2 44.5 -13.1619 29-Feb-04 2 44.32 -0.40531 31-Mar-04 2 41.9 -5.61502 30-Apr-04 2 45.94 9.205037 31-May-04 2 38.54 -17.564 30-Jun-04 2 38.65 0.285011 31-Jul-04 2 40.7 5.168132 31-Aug-04 2 44.175 8.193093 30-Sep-04 2 45 1.850347 31-Oct-04 2 48 6.453852 30-Nov-04 2 50 4.082199 31-Dec-04

bera00 · ‎07-28-2017

Thank you for your answer. I have doble sorted my sample and then I have used the macro posted on this link : https://sites.google.com/site/jiejaycao/home/tools This macro aims to 1-generate the average raw return matrix for double sorted group and the high-minus-low difference 2. report risk-adjusted returns: CAPM alpha, Fama-French-3-factor alpha,Carhart-4-factor alpha 3. report Newey-West adjusted t-stat in bracket for return value 4. indicate the signicance level of high-minus-low difference I will post the sas code, however I would like to ask you , I am getting a warning message: WARNING: Output 'OutputStatistics' was not created. Make sure that the output object name, label, or path is spelled correctly. Also, verify that the appropriate procedure options are used to produce the requested output object. For example, verify that the NOPRINT option is not used. WARNING: Output 'fitstatistics' was not created. Make sure that the output object name, label, or path is spelled correctly. Also, verify that the appropriate procedure options are used to produce the requested output object. For example, verify that the NOPRINT option is not used. Do you know what is the problem? I have tried ods trace on to see the output, I didn't find the fitstatistics and the outputstatistics. Do you know how can I choose the appropriate lag /********************************************************************** Purpose: 0. report formulized portfolio double sorting results 1. generate the average raw return matrix for double sorted groups and the high-minus-low difference 2. also report risk-adjusted returns: CAPM alpha, Fama-French-3-factor alpha,Carhart-4-factor alpha 3. report Newey-West adjusted t-stat in bracket for return value 4. indicate the signicance level of high-minus-low difference by '*', '**' and '***' for 10%, 5% and 1% Author : Jie Cao, jiecao@mail.utexas.edu Date : 12/01/2006 Notes : the two groups must be sorted ex-ante and each group1-group2 combination has a time-series of average raw return history Input data : the panel data, it must contains the equal or value weighted raw portfolio return (in %) for each group1-group2 each period factors : the corresponding Fama-French daily, weekly or monthly factors in time-series (http://mba.tuck.dartmouth.edu/pages/faculty/ken.french/data_library.html) byvar : usually left blank, in use if there is an extra layer like byvar-group1-group2-period for each row in the panel data rankvar1 : the group1 number rankvar2 : the group2 number timevar : the time-variable, daily, weekly, or monthly lag : the lag used for Newey-West test var : the raw return Output out1 : report double sorting results out2 : reprot both sorting and single sorting results ***********************************************************************/ %macro doublesort_adj(data=, factors=, byvar=, rankvar1=, rankvar2=, timevar=, lag=, var=, out1=, out2=); * rank1 is row and rank2 is column; proc sort data=&data; by &byvar &rankvar1 &rankvar2 &timevar;run; proc transpose data=&data out=data; by &byvar &rankvar1 &rankvar2 &timevar; var &var;run; proc sort data=data; by _name_ &byvar &rankvar1 &rankvar2 &timevar;run; *Find H-L difference for rank variable 2; proc sort data=&data out=sum; by &byvar &rankvar1 &timevar &rankvar2;run; data sum_diff; set sum(where=(&rankvar2>-1)); by &byvar &rankvar1 &timevar &rankvar2; if first.&timevar or last.&timevar; if first.&timevar then &rankvar2=1; if last.&timevar then &rankvar2=2; run; proc transpose data=sum_diff out=sum_diff2; by &byvar &rankvar1 &timevar; var &var; id &rankvar2; run; data sum_diff2; set sum_diff2; &rankvar2=99; col1 = _2 - _1; drop _2 _1; run; data sum_diff2; set data sum_diff2;run; *Find H-L difference for rank variable 1; proc sort data=sum_diff2; by _name_ &byvar &rankvar2 &timevar &rankvar1;run; data sum_diff3; set sum_diff2(where=(&rankvar1>-1)); by _name_ &byvar &rankvar2 &timevar &rankvar1; if first.&timevar or last.&timevar;if first.&timevar then &rankvar1=1; if last.&timevar then &rankvar1=2; run; proc transpose data=sum_diff3 out=sum_diff3; by _name_ &byvar &rankvar2 &timevar; var col1; id &rankvar1; run; data sum_diff3; set sum_diff3; &rankvar1=100; col1 = _2 - _1; drop _2 _1; run; data sum_diff3; set sum_diff3 sum_diff2;run; proc sort data=sum_diff3; by _name_ &byvar &rankvar1 &rankvar2 &timevar;run; **link four factors to get alpha; data ff; set &factors;run; proc sql; create table sum_diff3 as select a.*, (a.col1-b.rf*100) as exret, b.mktrf*100 as mktrf, b.smb*100 as smb,b.hml*100 as hml, b.umd*100 as umd from sum_diff3 as a left join ff as b on a.&timevar = b.date;/* the &timevar must be consistent with the time identifier (i.e. date) from FF time-series factors*/ quit; proc sort data=sum_diff3; by _name_ &byvar &rankvar1 &rankvar2 &timevar;run; data sum_diff3; set sum_diff3; if &rankvar1=100 or &rankvar2=99 then exret=col1; run; ***average return; options nonotes; proc model data=sum_diff3; by &byvar _name_ &rankvar1 &rankvar2; parms a; exogenous col1 ; instruments / intonly; col1=a; fit col1 / gmm kernel=(bart, %eval(&lag+1), 0);*lag=6; ods output parameterestimates=param0 fitstatistics=fitresult OutputStatistics=residual; quit; data param0; set param0; type='Average ret';run; **CAPM alpha; options nonotes; proc model data=sum_diff3; by &byvar _name_ &rankvar1 &rankvar2; parms a b1; instruments mktrf; exret =a+b1* mktrf; fit exret / gmm kernel=(bart, %eval(&lag+1), 0); ods output parameterestimates=param1 fitstatistics=fitresult OutputStatistics=residual; quit; data param1; set param1; type='CAPM Alpha'; if parameter='a';run; **Three Factor alpha; options nonotes; proc model data=sum_diff3; by &byvar _name_ &rankvar1 &rankvar2; parms a b1 b2 b3; instruments mktrf smb hml; exret =a+b1* mktrf+b2* smb+b3*hml; fit exret / gmm kernel=(bart, %eval(&lag+1), 0); ods output parameterestimates=param2 fitstatistics=fitresult OutputStatistics=residual; quit; data param2; set param2; type='FF3 Alpha'; if parameter='a';run; *Four factor alpha*; options nonotes; proc model data=sum_diff3; by &byvar _name_ &rankvar1 &rankvar2; parms a b1 b2 b3 b4; instruments mktrf smb hml umd; exret =a+b1* mktrf+b2* smb+b3*hml+b4*umd; fit exret / gmm kernel=(bart, %eval(&lag+1), 0); ods output parameterestimates=param3 fitstatistics=fitresult OutputStatistics=residual; quit; data param3; set param3; type='Carhart4 Alpha'; if parameter='a'; run; data param; set param0 param1 param2 param3;run; data param; set param; if probt<0.1 then p='* '; if probt<0.05 then p='** '; if probt<0.01 then p='***';tvalue2=put(tvalue,7.2); est=put(estimate, 12.2); param=est; if &rankvar1=100 or &rankvar2=99 then PARAM=compress(est||p); T=compress('('||tvalue2||')'); keep &byvar &rankvar1 &rankvar2 type _name_ param T; rename _name_=name;run; proc sort data=param; by name &byvar type &rankvar1 &rankvar2;run; proc transpose data=param out=out; by name &byvar type &rankvar1; var param T; id &rankvar2; run; data &out1; set out; if &rankvar1>-1;drop N1; if _name_='T' then &rankvar1=.; run; data &out2;set out; if _name_='T' then &rankvar1=.; rename _99=H_L N1=all;run; option notes; %mend doublesort_adj; %doublesort_adj(data=rankedfd1, factors=marketm, byvar=, rankvar1=Group_1, rankvar2=Group_2, timevar=report_date, lag=5, var=MOYENNE, out1=d1, out2=d2); .Thank youu again data have2; input report_date Group_1 Group_2 moyenne; informat report_date date9.; cards; REPORT_DATE GROUP_1 GROUP_2 MOYENNE 3/31/2005 1 1 -2.322171824 6/30/2005 1 1 0.626790319 9/30/2005 1 1 1.982357543 3/31/2006 1 1 2.198054512 6/30/2006 1 1 0.0145957 12/31/2007 1 1 -1.240469711 3/31/2008 1 1 -0.474454175 6/30/2008 1 1 -6.384736204 9/30/2008 1 1 -11.20326428 12/31/2008 1 1 4.266663084 3/31/2009 1 1 7.231308572 6/30/2009 1 1 -0.447744893 9/30/2009 1 1 5.929067393 12/31/2009 1 1 2.57751674 3/31/2010 1 1 4.036217119 6/30/2010 1 1 -1.20620396 9/30/2010 1 1 9.328738063 12/31/2010 1 1 6.353543839 3/31/2011 1 1 1.324201634 6/30/2011 1 1 -1.601201557 9/30/2011 1 1 -10.50014413 12/31/2012 1 1 2.295058131 9/30/2013 1 1 4.89632191 12/31/2013 1 1 2.32541224 3/31/2014 1 1 0.091297204 6/30/2014 1 1 2.235187498 9/30/2014 1 1 -3.484066949 12/31/2014 1 1 -1.823150093 3/31/2015 1 1 -1.527052398 6/30/2015 1 1 -2.296832908 9/30/2015 1 1 -3.226596051 12/31/2015 1 1 -2.350325922 3/31/2016 1 1 6.452720906 3/31/2005 1 2 -2.435033333 6/30/2005 1 2 1.589133564 9/30/2005 1 2 1.938033691 3/31/2006 1 2 2.606787331 6/30/2006 1 2 -1.06971269 12/31/2007 1 2 -0.846350605 3/31/2008 1 2 -0.316333072 6/30/2008 1 2 -6.576942096 9/30/2008 1 2 -13.27043089 12/31/2008 1 2 4.884534433 3/31/2009 1 2 7.43684456 6/30/2009 1 2 -0.579475586 9/30/2009 1 2 5.876049733 12/31/2009 1 2 2.463108998 3/31/2010 1 2 6.527865041 6/30/2010 1 2 -2.075713905 9/30/2010 1 2 10.38296691 12/31/2010 1 2 6.399916889 3/31/2011 1 2 0.502057898 6/30/2011 1 2 -1.243589455 9/30/2011 1 2 -10.32849698 12/31/2012 1 2 1.473758817 9/30/2013 1 2 5.054047282 12/31/2013 1 2 1.484317969 3/31/2014 1 2 0.674285761 6/30/2014 1 2 1.96611123 9/30/2014 1 2 -3.077085786 12/31/2014 1 2 -1.622661646 3/31/2015 1 2 -0.876391176 6/30/2015 1 2 -1.887314414 9/30/2015 1 2 -3.211623903 12/31/2015 1 2 -1.982155451 3/31/2016 1 2 6.444438622 3/31/2005 1 3 -3.063433079 6/30/2005 1 3 2.406043805 9/30/2005 1 3 2.587182805 3/31/2006 1 3 3.527857546 6/30/2006 1 3 -1.538172321 12/31/2007 1 3 -1.239657725 3/31/2008 1 3 -1.095768426 6/30/2008 1 3 -7.72586063 9/30/2008 1 3 -12.66765454 12/31/2008 1 3 3.346787823 3/31/2009 1 3 8.759043383 6/30/2009 1 3 -1.110819519 9/30/2009 1 3 4.954552331 12/31/2009 1 3 2.761224237 3/31/2010 1 3 5.7669492 6/30/2010 1 3 -4.294199302 9/30/2010 1 3 9.547207912 12/31/2010 1 3 6.907712084 3/31/2011 1 3 0.239119669 6/30/2011 1 3 -2.381578283 9/30/2011 1 3 -9.900471195 12/31/2012 1 3 1.982756966 9/30/2013 1 3 5.107008482 12/31/2013 1 3 2.31786484 3/31/2014 1 3 -0.608583105 6/30/2014 1 3 2.387027063 9/30/2014 1 3 -3.036925061 12/31/2014 1 3 -1.238230944 3/31/2015 1 3 -0.705223331 6/30/2015 1 3 -1.56115226 9/30/2015 1 3 -3.760019327 12/31/2015 1 3 -2.729687349 3/31/2016 1 3 7.060595051 3/31/2005 2 1 -2.140270345 6/30/2005 2 1 1.828399489 9/30/2005 2 1 3.228528125 3/31/2006 2 1 3.046076601 6/30/2006 2 1 -0.561187613 12/31/2007 2 1 -1.372377344 3/31/2008 2 1 -1.653002186 6/30/2008 2 1 -7.369311069 9/30/2008 2 1 -10.79778152 12/31/2008 2 1 4.542882575 3/31/2009 2 1 6.332025054 6/30/2009 2 1 0.665235567 9/30/2009 2 1 3.856023888 12/31/2009 2 1 2.439971204 3/31/2010 2 1 5.30331915 6/30/2010 2 1 -1.315842659 9/30/2010 2 1 9.55397894 12/31/2010 2 1 6.546704368 3/31/2011 2 1 0.09037446 6/30/2011 2 1 -1.519320206 9/30/2011 2 1 -9.284942436 12/31/2012 2 1 2.360583264 9/30/2013 2 1 5.037464272 12/31/2013 2 1 1.681270083 3/31/2014 2 1 0.272434664 6/30/2014 2 1 2.047536758 9/30/2014 2 1 -3.958858788 12/31/2014 2 1 -1.826459901 3/31/2015 2 1 -1.05916749 6/30/2015 2 1 -2.547343232 9/30/2015 2 1 -2.826212581 12/31/2015 2 1 -1.465009398 3/31/2016 2 1 6.133429858 3/31/2005 2 2 -1.916876579 6/30/2005 2 2 1.131196343 9/30/2005 2 2 2.806330757 3/31/2006 2 2 1.932782118 6/30/2006 2 2 0.122114921 12/31/2007 2 2 -1.658254159 3/31/2008 2 2 -0.904590876 6/30/2008 2 2 -8.173949476 9/30/2008 2 2 -9.721551737 12/31/2008 2 2 4.600794163 3/31/2009 2 2 6.518280792 6/30/2009 2 2 0.238502059 9/30/2009 2 2 4.450664754 12/31/2009 2 2 1.979304808 3/31/2010 2 2 5.935359522 6/30/2010 2 2 -3.022620613 9/30/2010 2 2 9.213448493 12/31/2010 2 2 6.487539172 3/31/2011 2 2 -0.12057478 6/30/2011 2 2 -1.598320202 9/30/2011 2 2 -8.293524448 12/31/2012 2 2 1.75759085 9/30/2013 2 2 5.299883246 12/31/2013 2 2 1.842179385 3/31/2014 2 2 0.215677192 6/30/2014 2 2 1.598921949 9/30/2014 2 2 -2.762990976 12/31/2014 2 2 -1.510258758 3/31/2015 2 2 -1.419294217 6/30/2015 2 2 -2.372750378 9/30/2015 2 2 -3.904015356 12/31/2015 2 2 -1.787518889 3/31/2016 2 2 6.888912545 3/31/2005 2 3 -2.669968261 6/30/2005 2 3 0.965619644 9/30/2005 2 3 1.578924128 3/31/2006 2 3 1.817990688 6/30/2006 2 3 -1.316281088 12/31/2007 2 3 -1.655901047 3/31/2008 2 3 -2.193572999 6/30/2008 2 3 -10.49299246 9/30/2008 2 3 -11.0921548 12/31/2008 2 3 4.599329464 3/31/2009 2 3 8.921736464 6/30/2009 2 3 1.091586637 9/30/2009 2 3 4.334811365 12/31/2009 2 3 2.87522004 3/31/2010 2 3 6.254339258 6/30/2010 2 3 -4.549056471 9/30/2010 2 3 10.41858545 12/31/2010 2 3 6.178918441 3/31/2011 2 3 0.383679077 6/30/2011 2 3 -1.606762104 9/30/2011 2 3 -9.302983225 12/31/2012 2 3 2.618063019 9/30/2013 2 3 5.07674057 12/31/2013 2 3 2.643362741 3/31/2014 2 3 -0.319062829 6/30/2014 2 3 2.048142593 9/30/2014 2 3 -3.489614908 12/31/2014 2 3 -1.508985386 3/31/2015 2 3 -0.62553499 6/30/2015 2 3 -1.764952512 9/30/2015 2 3 -3.916254491 12/31/2015 2 3 -2.280877653 3/31/2016 2 3 6.549847454 3/31/2005 3 1 -1.575118227 6/30/2005 3 1 1.12384807 9/30/2005 3 1 2.302000372 3/31/2006 3 1 2.263326848 6/30/2006 3 1 -0.519515099 12/31/2007 3 1 0.638903511 3/31/2008 3 1 -1.072609428 6/30/2008 3 1 -6.960439156 9/30/2008 3 1 -9.433949204 12/31/2008 3 1 2.423745032 3/31/2009 3 1 6.564420902 6/30/2009 3 1 -0.317507012 9/30/2009 3 1 5.407487336 12/31/2009 3 1 1.995858332 3/31/2010 3 1 5.610697649 6/30/2010 3 1 -1.860893117 9/30/2010 3 1 9.168087721 12/31/2010 3 1 6.711588318 3/31/2011 3 1 0.265628154 6/30/2011 3 1 -2.28810599 9/30/2011 3 1 -9.503749891 12/31/2012 3 1 1.84201768 9/30/2013 3 1 5.015118099 12/31/2013 3 1 1.763221182 3/31/2014 3 1 0.572648961 6/30/2014 3 1 2.0801556 9/30/2014 3 1 -3.639577744 12/31/2014 3 1 -1.998462654 3/31/2015 3 1 -1.68262813 6/30/2015 3 1 -2.550869277 9/30/2015 3 1 -2.876979374 12/31/2015 3 1 -2.386181485 3/31/2016 3 1 7.022733055 3/31/2005 3 2 -2.143950435 6/30/2005 3 2 0.545862866 9/30/2005 3 2 2.129712148 3/31/2006 3 2 1.764491015 6/30/2006 3 2 -0.783620219 12/31/2007 3 2 -0.97583881 3/31/2008 3 2 -0.597903926 6/30/2008 3 2 -7.678382307 9/30/2008 3 2 -11.66207884 12/31/2008 3 2 3.905737041 3/31/2009 3 2 6.525406537 6/30/2009 3 2 -1.086361905 9/30/2009 3 2 5.553301477 12/31/2009 3 2 2.949795251 3/31/2010 3 2 5.670726827 6/30/2010 3 2 -2.884013201 9/30/2010 3 2 8.764470242 12/31/2010 3 2 6.577653317 3/31/2011 3 2 0.068791001 6/30/2011 3 2 -1.521590878 9/30/2011 3 2 -9.353286348 12/31/2012 3 2 2.665415372 9/30/2013 3 2 5.28913576 12/31/2013 3 2 2.006834018 3/31/2014 3 2 -0.61642788 6/30/2014 3 2 1.769921622 9/30/2014 3 2 -2.526256388 12/31/2014 3 2 -1.523431539 3/31/2015 3 2 -0.813288728 6/30/2015 3 2 -1.941764766 9/30/2015 3 2 -3.438947813 12/31/2015 3 2 -1.956527965 3/31/2016 3 2 6.701681526 3/31/2005 3 3 -1.823792866 6/30/2005 3 3 1.726441505 9/30/2005 3 3 2.069270297 3/31/2006 3 3 3.247117139 6/30/2006 3 3 -1.423422931 12/31/2007 3 3 -1.479774712 3/31/2008 3 3 -1.756124615 6/30/2008 3 3 -8.468743543 9/30/2008 3 3 -14.43354213 12/31/2008 3 3 5.963423941 3/31/2009 3 3 7.903468017 6/30/2009 3 3 0.0470396 9/30/2009 3 3 5.671113129 12/31/2009 3 3 3.529030076 3/31/2010 3 3 6.653064067 6/30/2010 3 3 -4.448708333 9/30/2010 3 3 10.54382053 12/31/2010 3 3 6.610569877 3/31/2011 3 3 0.438423905 6/30/2011 3 3 -2.133997746 9/30/2011 3 3 -9.590102421 12/31/2012 3 3 2.536270488 9/30/2013 3 3 4.539256264 12/31/2013 3 3 2.202420887 3/31/2014 3 3 -0.668028044 6/30/2014 3 3 2.084467915 9/30/2014 3 3 -2.770458643 12/31/2014 3 3 -1.018341357 3/31/2015 3 3 -0.825179375 6/30/2015 3 3 -1.762280682 9/30/2015 3 3 -3.508094255 12/31/2015 3 3 -1.893558697 3/31/2016 3 3 6.203550471 ; run;

bera00 · ‎07-27-2017

Helloo, I have the same problem since I am using approximetaly the same macro. Can you please tell me what have you corrected with your sas code. I have added the ods trace on I have got: ods trace on; ods output moments=xmoments; proc model data=sum_diff5; by _name_ GROUP_1; parms a; exogenous col1 ; instruments / intonly; col1=a; fit col1 / gmm kernel=(bart, 4, 0); ods output parameterestimates=pa0 fitstatistics=fitresult OutputStatistics=residual; quit; ods trace off; I have got : Output Added: ------------- Name: ModSummary Label: Variable Counts Template: ets.model.ModSummary Path: Model.ModSum.ModSummary ------------- Output Added: ------------- Name: ModVars Label: Model Variables Template: ets.model.ModVars Path: Model.ModSum.ModVars ------------- Output Added: ------------- Name: Equations Label: The Equation to Estimate is Template: ets.model.Equations Path: Model.ModSum.Equations ------------- Output Added: ------------- Name: ConvergenceStatus Label: Convergence Status Template: ets.model.ConvergenceStatus Path: Model.ByGroup2.GMM.ConvergenceStatus ------------- Output Added: ------------- Name: DatasetOptions Label: Data Set Options Template: ets.model.DatasetOptions Path: Model.ByGroup2.GMM.EstSum.DatasetOptions ------------- Output Added: ------------- Name: MinSummary Label: Minimization Summary Template: ets.model.MinSummary Path: Model.ByGroup2.GMM.EstSum.MinSummary ------------- Output Added: ------------- Name: ConvCrit Label: Final Convergence Criteria Template: ets.model.ConvCrit Path: Model.ByGroup2.GMM.EstSum.ConvCrit ------------- Output Added: ------------- Name: ObsUsed Label: Observations Processed Template: ets.model.ObsUsed Path: Model.ByGroup2.GMM.EstSum.ObsUsed ------------- Output Added: ------------- Name: ResidSummary Label: Nonlinear GMM Summary of Residual Errors Template: ets.model.ResidSummary Path: Model.ByGroup2.GMM.ResidSummary ------------- Output Added: ------------- Name: ParameterEstimates Label: Nonlinear GMM Parameter Estimates Template: ets.model.ParameterEstimates Path: Model.ByGroup2.GMM.ParameterEstimates ------------- Output Added: ------------- Name: EstSummaryStats Label: Estimation Summary Statistics Template: ets.model.EstSummaryStats Path: Model.ByGroup2.GMM.EstSummaryStats ------------- Output Added: ------------- Name: GMMTestStats Label: GMM Test Statistics Template: ets.model.GMMTestStats Path: Model.ByGroup2.GMM.GMMTestStats ------------- Output Added: ------------- Name: DiagnosticsPanel Label: Panel 1 Template: ETS.Model.Graphics.DiagnosticsPanel Path: Model.ByGroup2.GMM.FitDiagnosticsPlots.DiagnosticsPanel ------------- Output Added: ------------- Name: DiagnosticsPanel Label: Panel 2 Template: ETS.Model.Graphics.DiagnosticsPanel Path: Model.ByGroup2.GMM.FitDiagnosticsPlots.DiagnosticsPanel I think the problem is that I didn t find the fitstatistics and OutputStatistics within the log . How can I know what is the equivilant of these two to get the fitresult and residual ?? Thank youuuu

bera00 · ‎07-27-2017

Hello I would like to double sort the firms based on their size (TNA) and their concentration. Therefore, first, I need to sort the firms into terciles based on their size and then sort them based on their concentration. I need then to estimate for each generated portfolio its returns and calculate the difference between tercile 3 and tercile 1 The aim is to obtain the following : concentration: size : 1-small 2 3-large 1-diversified 2- 3- concentrated tercile 3- tercile1 x x x I would like to know if my sas code is correct and to know how can I calculate tercile3-tercile1 and where to insert that in my sas code.Thank youu in advance. data have; input id $ report_date concentration alpha; informat report_date date9.; cards; 1 31-Mar-05 0.00323156 -0.00010338 31.9 2 31-Mar-05 0.0090895 0.00130514 2.9 3 31-Mar-05 0.00732307 0.0016094 7.5 4 31-Mar-05 0.143448318 -0.0036284 13.7 5 31-Mar-05 0.0128099 0.000668983 1.3 6 31-Mar-05 0.054668 -0.00081655 42.8 7 31-Mar-05 0.086314948 0.00156074 1.8 8 31-Mar-05 0.0355916 -0.0052425 7.3 9 31-Mar-05 0.00449587 0.00487914 106.4 10 31-Mar-05 0.114491666 -0.0032045 1215.2 11 31-Mar-05 0.00145931 0.00146518 38.8 12 31-Mar-05 0.0136698 -0.0027977 13.9 13 31-Mar-05 0.0240932 -0.0058679 9.5 14 31-Mar-05 -0.002676 0.00330896 1344.2 15 31-Mar-05 0.0207875 0.000187603 133.3 16 31-Mar-05 -0.01618 0.000540587 239.5 17 31-Mar-05 0.186049612 -0.0055676 4.5 18 31-Mar-05 0.11430027 -0.00054218 0.4 19 31-Mar-05 0.0420707 0.000860293 0.1 20 31-Mar-05 0.218189357 -0.0018136 0.4 21 31-Mar-05 0.00308538 0.000341367 8.2 22 31-Mar-05 0.0113599 0.00162913 2.3 23 31-Mar-05 0.0605686 -0.001316 1.1 24 31-Mar-05 0.0275298 -0.0030126 6 25 31-Mar-05 -0.00028393 0.00428045 5.7 26 31-Mar-05 0.000975115 0.00511607 2.2 27 31-Mar-05 0.0513553 -0.0017075 7.1 28 31-Mar-05 0.107913797 -0.0019555 49.4 29 31-Mar-05 0.028197 -0.0011384 42.9 30 31-Mar-05 0.021593 -0.0011053 39.8 31 31-Mar-05 0.00224724 0.00241661 5.1 32 31-Mar-05 0.0209171 -0.0024532 23.4 33 31-Mar-05 -0.0036813 0.00408326 26.7 34 31-Mar-05 -0.0052467 0.000882723 178.7 35 31-Mar-05 -0.0043958 -0.0022193 9.6 36 31-Mar-05 0.21214046 -0.0028023 80.4 37 31-Mar-05 0.00667457 0.00370299 2.9 38 31-Mar-05 0.0623278 -0.0021584 1.9 39 31-Mar-05 -0.0062717 0.000341234 1.2 40 31-Mar-05 0.0101843 -0.0034151 1.5 41 31-Mar-05 -0.022794 -0.0074877 44.3 42 31-Mar-05 0.169917216 -0.0030941 34.4 43 31-Mar-05 0.0108594 0.00377686 40 44 31-Mar-05 0.00473353 -0.00067654 78 1 30-Jun-05 0.009307 -0.00030918 1 2 30-Jun-05 0.00774149 -0.00020542 0.5 3 30-Jun-05 0.011545 -0.0020038 20 4 30-Jun-05 0.194446784 -0.002323 30 5 30-Jun-05 0.0149996 -0.0021457 30 6 30-Jun-05 0.080947592 -0.0019171 273.3 7 30-Jun-05 0.110467025 0.00173641 1816.7 8 30-Jun-05 0.116229935 -0.0028516 28.2 9 30-Jun-05 0.053037 -0.0060764 1.8 10 30-Jun-05 0.0205285 0.00401539 0.6 11 30-Jun-05 0.07412847 -0.0036452 12.9 12 30-Jun-05 0.000165941 0.00115735 9.2 13 30-Jun-05 0.0157843 -0.0045755 1 14 30-Jun-05 0.0198364 -0.0068052 2390.3 15 30-Jun-05 0.000822336 0.00199478 1366.2 16 30-Jun-05 0.0357898 -0.00060878 3729.9 17 30-Jun-05 -0.0036613 0.000226096 103.2 18 30-Jun-05 0.098482726 -0.0055159 5 19 30-Jun-05 0.0253289 0.000002754 48.6 20 30-Jun-05 0.0423226 0.00106245 7.4 21 30-Jun-05 0.168881853 -0.0012286 13.1 22 30-Jun-05 -0.009306 -0.0013763 0.6 23 30-Jun-05 0.0147948 0.000824364 52.9 24 30-Jun-05 0.107032711 -0.0013647 0.3 25 30-Jun-05 0.0269755 -0.0035086 0.3 26 30-Jun-05 0.00559733 0.00263787 0.3 27 30-Jun-05 0.00441441 0.00371973 47.1 28 30-Jun-05 -0.032813 0.000008644 0.6 29 30-Jun-05 0.09168613 -0.002004 56.1 30 30-Jun-05 0.218771292 -0.0020551 3.8 31 30-Jun-05 0.0148921 -0.0011354 1.1 32 30-Jun-05 0.0342261 0.000230794 39.1 33 30-Jun-05 0.023561 0.000594719 0.2 34 30-Jun-05 0.0212158 -0.00059915 293.6 35 30-Jun-05 0.0176603 -0.0043151 43.2 36 30-Jun-05 -0.0044589 0.00173475 23.5 37 30-Jun-05 0.000541246 0.00275968 20 38 30-Jun-05 -0.0040414 0.00162118 25 39 30-Jun-05 -0.0035244 0.000688753 23 40 30-Jun-05 0.136322452 -0.0020837 2.3 41 30-Jun-05 0.0137513 0.00307359 0.3 42 30-Jun-05 0.0579321 0.000323705 11.8 43 30-Jun-05 0.0140361 0.00403422 0.7 44 30-Jun-05 -0.013 0.000571995 2.5 45 30-Jun-05 0.0125506 -0.0053177 5 46 30-Jun-05 0.0102302 -0.0073835 0.6 47 30-Jun-05 0.186963265 -0.0023848 0.6 48 30-Jun-05 0.014541 0.00236808 152.6 49 30-Jun-05 0.00431307 -0.0017364 58.7 50 30-Jun-05 0.0498479 0.00467702 0.2 1 30-Sep-05 -0.0063786 0.000907728 0.2 2 30-Sep-05 0.0101038 0.000871673 3.2 3 30-Sep-05 0.0284981 0.00153884 0.1 4 30-Sep-05 0.121115631 -0.0015127 2.3 5 30-Sep-05 0.0306446 0.0015302 1.4 6 30-Sep-05 0.067656719 -0.00009431 0.3 7 30-Sep-05 0.108191664 -0.0018033 16.2 8 30-Sep-05 0.089732855 -0.0044431 0.1 9 30-Sep-05 0.065786426 -0.0028578 57.9 10 30-Sep-05 0.0166163 0.0128766 41.6 11 30-Sep-05 0.096876763 -0.0014895 169.1 12 30-Sep-05 0.000536793 -0.0005081 415.1 13 30-Sep-05 0.0342551 0.00110223 71.3 14 30-Sep-05 0.0504572 -0.006145 58.1 15 30-Sep-05 0.00601089 0.00436036 1.8 16 30-Sep-05 0.0417421 0.000042022 0.1 17 30-Sep-05 -0.0026028 -0.00061885 0.1 18 30-Sep-05 0.0491393 -0.0041615 30 19 30-Sep-05 -0.011321 0.00121491 1.9 20 30-Sep-05 0.0262681 0.00412299 1.9 21 30-Sep-05 0.195736815 0.00112513 2 22 30-Sep-05 -0.014973 -0.00050243 77.9 23 30-Sep-05 0.0356525 0.00176706 21.1 24 30-Sep-05 0.085042159 -0.000826 14.6 25 30-Sep-05 0.0305232 -0.0016151 3.6 26 30-Sep-05 0.00321965 0.0029778 0.1 27 30-Sep-05 0.00560532 0.00295675 32.3 28 30-Sep-05 -0.031548 0.00185938 12.6 29 30-Sep-05 0.0605934 -0.0010561 0.2 30 30-Sep-05 0.174065395 -0.0017657 6.3 31 30-Sep-05 0.012552 -0.00016551 221.1 32 30-Sep-05 0.0313465 0.00155516 39.2 33 30-Sep-05 0.015045 0.00141682 56.6 34 30-Sep-05 0.075091573 0.00254347 61.9 35 30-Sep-05 0.0438968 -0.00075478 30 36 30-Sep-05 -0.0038023 0.0039141 101.6 37 30-Sep-05 -0.007097 0.00315936 0.3 38 30-Sep-05 -0.0025448 0.00192762 0.6 ; run; proc rank data=doublesort groups=3 out=ranked; by date; var TNA; ranks GROUP_1; run; proc rank data=ranked groups=3 out=rankedfinal; by date var Concentration; ranks GROUP_2; run; proc sort data=rankedfinal; by date GROUP_2 GROUP_1 ;run; PROC UNIVARIATE DATA= rankedfinal noprint; var return; by date GROUP_2 GROUP_1 ; output out= ga3 mean=MOYENNE; run; proc sort data=ga3; by GROUP_2 GROUP_1;run; proc means data = ga3 noprint ; by GROUP_2 GROUP_1 ; var moyenne; output out = rankedfinal1 mean= ewret; run;

bera00 · ‎07-25-2017

Hello I would like to double sort the firms based on their size (TNA) and their concentration. Therefore, first, I need to sort the firms into terciles based on their size and then sort them based on their concentration. I need then to estimate for each generated portfolio its returns and calculate the difference between tercile 3 and tercile 1 The aim is to obtain the following : concentration: size : 1-small 2 3-large 1-diversified 2- 3- concentrated tercile 3- tercile1 x x x I would like to know if my sas code is correct and to know how can I calculate tercile3-tercile1 and where to insert that in my sas code.Thank youu in advance proc rank data=doublesort groups=3 out=ranked; by date; var TNA; ranks GROUP_1; run; proc rank data=ranked groups=3 out=rankedfinal; by date var Concentration; ranks GROUP_2; run; proc sort data=rankedfinal; by date GROUP_2 GROUP_1 ;run; PROC UNIVARIATE DATA= rankedfinal noprint; var return; by date GROUP_2 GROUP_1 ; output out= ga3 mean=MOYENNE; run; proc sort data=ga3; by GROUP_2 GROUP_1;run; proc means data = ga3 noprint ; by GROUP_2 GROUP_1 ; var moyenne; output out = rankedfinal1 mean= ewret; run;

bera00 · ‎05-27-2017

Hello thank you again for your answer . If I would like to set a minimum of obs then how can I do it? In addition I am getting in the log window : NOTE: Variable I is uninitialized. ERROR: Array subscript out of range at line 46 column 9. Cusip=000360206 Rt=10.433725466 Rtm=-3.146277904 Rti=-1.741508366 REPORT_DATE=04/30/2005 FIRST.Cusip=0 LAST.Cusip=0 first_date=15126 monthnum=48 m=1 I=. _ERROR_=1 _N_=68 Thank you

bera00 · ‎05-26-2017

Thank you so much for your answer. If i undestood well, this code keeps only the stocks that have previous 48 month records? However in my study I need to keep all the stocks and for those that don't have 48 then take the available records.. is that possible? I have been trying this script but it works only for one cusip not for many and it doesn t give me what I want : beta coefficients for each cusip at each date. data nstockregfinal4 / view= nstockregfinal4; do grp = 0 to nrecs-48; do j = 1 + grp to 48 + grp; set nstockregfinal3 nobs=nrecs point=j; output; end; end; stop; run; proc reg data=nstockregfinal4 outest=stats noprint; by grp; model Rt = Rtm Rti; run; quit;

bera00 · ‎05-25-2017

Hello all I am having some trouble in calculating rolling window beta by using the previous 48 month data. I have multiple stocks (20000 stock) and for each CUSIP at each date I want to obtain betai and betam using the following regression : Rt = alpha + betaiRti + betamRtm +e I have tried the following sas script, but my problem is that not all cusip have the same observations some have less then 48 month date. Any help would be much appreciated data nstockregfinal4 / view= nstockregfinal4; do grp = 0 to nrecs-48; do j = 1 + grp to 48 + grp; set nstockregfinal3 nobs=nrecs point=j; output; end; end; stop; run; proc reg data=nstockregfinal4 outest=Coeff noprint; by grp; model Rt = Rtm Rti; output out=Res p=predicted r=residus; run; quit; *however this sas code works for just one cusip code so I have tried the following code but i am not sure it s right; datanstockregfinal5 / view= nstockregfinal5; array _X1 {48} _temporary_ ; array _X2 {48} _temporary_ ; array _Y {48} _temporary_ ; set nstockregfinal3; by CUSIP; retain N 0; N = ifn(first.CUSIP,1,N+1); I=mod(N-1,48)+1; _X1{I}=Rtm; _X2{I}=Rti; _Y{I}=Rt; if N>=48 then do I= 1 to 48; Rtm=_X1{I}; Rti=_X2{I}; Rt=_Y{I}; output; end; run; proc reg data=nstockregfinal5 noprint outest=myests; by CUSIP REPORT_DATE; model Rt = Rtm Rti; run; quit;

bera00 · ‎05-24-2017

Hello all, I need your help please because I am having some troubles with the sas script to estimate rolling window betas . I want to estimate for each cusip code in each month , the betai and betam using the previous four years of monthly returns data: Rt = alpha + betai Rti + betam Rtm + e I have 20000 stocks in my dataset for a period of 2001-2016. The starting period of my study is 01/01/2005 so I would like to estimate the betas using the previous 48 months date. The problem is that not all the stocks have previous complete 48 month data returns. I have trying the following code but it doesn't work well. I would like to know your suggestions please. my dataset that looks like that: CUSIP Rt Rtm Rti Report_date and I would like to obtain REPORT_DATE CUSIP betai betam Thank you in advance data nstockregfinal5 / view= nstockregfinal5; array _X1 {48} _temporary_ ; array _X2 {48} _temporary_ ; array _Y {48} _temporary_ ; set price.nstockregfinal3; by CUSIP; retain N 0; N = ifn(first.CUSIP,1,N+1); I=mod(N-1,48)+1; _X1{I}=Rtm; _X2{I}=Rti; _Y{I}=Rt; if N>=48 then do I= 1 to 48; Rtm=_X1{I}; Rti=_X2{I}; Rt=_Y{I}; output; end; run; proc reg data= nstockregfinal5 noprint outest=myests; by CUSIP REPORT_DATE; model Rt = Rtm Rti; run; quit;

Online Status	Offline
Date Last Visited	‎03-02-2018 06:17 AM

Re: Finding a stock within fund holdings in previous quarter

Re: Finding a stock within fund holdings in previous quarter

Finding a stock within fund holdings in previous quarter

Check if a stock was in the fund portfolio at previous date

Re: remove recurring prices

remove recurring prices

Problem with recurring prices

Re: proc rank

Re: SAS Macro issue

proc rank

Re: Finding a stock within fund holdings in previous quarter

Re: Finding a stock within fund holdings in previous quarter

Re: Finding a stock within fund holdings in previous quarter

Re: proc rank

Re: how to estimate rolling window beta

Re: Finding a stock within fund holdings in previous quarter

Re: Finding a stock within fund holdings in previous quarter

Finding a stock within fund holdings in previous quarter

Check if a stock was in the fund portfolio at previous date

Re: remove recurring prices

remove recurring prices

Problem with recurring prices

Re: proc rank

Re: SAS Macro issue

proc rank

double sort proc rank

Re: how to estimate rolling window beta

Re: how to estimate rolling window beta

calculate rolling window beta

how to estimate rolling window beta