topic proc rank to form portfolios in Statistical Procedures

proc rank to form portfolios

odette — Tue, 25 Jul 2017 16:22:21 GMT

Hello everyone. I need your help with my sas code. At each quarter, I need to sort the funds into quintiles based on the concentration measure . Then, I need to calculate the equally weighted returns of each quintile portfolio for the following quarter and rebalance the portfolio at each quarter.

I am not sure about about the J and K and about the formation of portfolios.

I have posted some observation of my dataset.. At each quarter , I have multiple funds id for which I have their alpha and concentration measures.

Thank you in advance for your help and your comments

*** NUMBER OF PRIOR MONTHS USED TO CREATE PORTFOLIOS;

%let J=3;

*** HOLDING PERIOD IN MONTHS AFTER PORTFOLIO CREATION;
%let K=3; 

*** BEGINING SAMPLE PERIOD;
%let begyear=2005;
*** ENDING SAMPLE PERIOD;
%let endyear=2016;
*****************************************************************************;


proc sql;
    create table getr_3
    as select distinct a.id, a.report_date,alpha,concentration
    from getr_2 (keep= id report_Date) as a, getr_2 as b
    where a.id=b.id
    and  0<=intck('month', b.report_date, a.report_date)<&J
order by id, report_Date;
quit;

proc sort data=getr_3; by report_date; run;
proc rank data=getr_3 out=vol1 group=5;
by report_date;
var concentration;
ranks volr;
run;

data vol2;
set vol1;
volr=volr+1;

run;

4. Assign Ranks to the Next 1 (K) Month After Portfolio Formation
********************************************************************************;
* Portfolio return are average monthly returns rebalanced monthly;
proc sql;
create table msfx2
as select distinct a.volr, a.report_date as form_date, a.id, b.report_date, b.alpha,b.concentration
from vol2 as a, getr_2 as b
where a.id=b.id
and 0<intck('month',a.report_date,b.report_date)<=&K;
quit; 

**5. Calculate Equally-Weighted Average Monthly Returns
********************************************************************************;
proc sort data=msfx2; by report_date volr form_date ; run;
* Portfolio monthly return series;
proc means data = msfx2 noprint;
by report_date volr form_date;
var alpha;
output out = msfx3 mean=ret;
run;

* Portfolio average monthly return;
proc sort data=msfx3; by report_date volr;
where year(report_date) between &begyear and &endyear;
run;
proc means data = msfx3 noprint;
by report_date volr;
var ret;
output out = ewretdat mean= ewret;
run;

proc sort data=ewretdat; by volr ; run;
Title "Table 1: Returns of Portfolios";
Title2 "Portfolios based on &J month lagged return and held for &K months";
proc means data=ewretdat mean std t probt;
class volr;
var ewret;
run;
**6. Calculate Buy-Sell Portfolio Returns
********************************************************************************
*****;
proc sort data=ewretdat; by report_date volr; run;
proc transpose data=ewretdat out=ewretdat2
(rename = (_1=quintile1 _2=PORT2 _3=PORT3 _4=PORT4 _5=quintile5
));
by report_date;
id volr;
var ewret;
run;

data ewretdat3;
set ewretdat2;
quintile5_quintile1=quintile5-quintile1;
run;

proc means data=ewretdat3 n mean std t probt;
var quintile5 quintile1 quintile5_quintile1;
run;

Re: proc rank to form portfolios

ballardw — Tue, 25 Jul 2017 19:38:59 GMT

Many users here don't want to download Excel files because of virus potential, others have such things blocked by security software. Also if you give us Excel we have to create a SAS data set and due to the non-existent constraints on Excel data cells the result we end up with may not have variables of the same type (numeric or character) and even values.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

Re: proc rank to form portfolios

odette — Wed, 26 Jul 2017 12:18:10 GMT

Thank you for your reply .

Actually here's some observations of my dataset :

data have;
input id $ report_date concentration alpha;
informat report_date date9.;
cards;
1 31-Mar-05 0.00323156 -0.00010338
2 31-Mar-05 0.0090895 0.00130514
3 31-Mar-05 0.00732307 0.0016094
4 31-Mar-05 0.143448318 -0.0036284
5 31-Mar-05 0.0128099 0.000668983
6 31-Mar-05 0.054668 -0.00081655
7 31-Mar-05 0.086314948 0.00156074
8 31-Mar-05 0.0355916 -0.0052425
9 31-Mar-05 0.00449587 0.00487914
10 31-Mar-05 0.114491666 -0.0032045
11 31-Mar-05 0.00145931 0.00146518
12 31-Mar-05 0.0136698 -0.0027977
13 31-Mar-05 0.0240932 -0.0058679
14 31-Mar-05 -0.002676 0.00330896
15 31-Mar-05 0.0207875 0.000187603
16 31-Mar-05 -0.01618 0.000540587
17 31-Mar-05 0.186049612 -0.0055676
18 31-Mar-05 0.11430027 -0.00054218
19 31-Mar-05 0.0420707 0.000860293
20 31-Mar-05 0.218189357 -0.0018136
21 31-Mar-05 0.00308538 0.000341367
22 31-Mar-05 0.0113599 0.00162913
23 31-Mar-05 0.0605686 -0.001316
24 31-Mar-05 0.0275298 -0.0030126
25 31-Mar-05 -0.00028393 0.00428045
26 31-Mar-05 0.000975115 0.00511607
27 31-Mar-05 0.0513553 -0.0017075
28 31-Mar-05 0.107913797 -0.0019555
29 31-Mar-05 0.028197 -0.0011384
30 31-Mar-05 0.021593 -0.0011053
31 31-Mar-05 0.00224724 0.00241661
32 31-Mar-05 0.0209171 -0.0024532
33 31-Mar-05 -0.0036813 0.00408326
34 31-Mar-05 -0.0052467 0.000882723
35 31-Mar-05 -0.0043958 -0.0022193
36 31-Mar-05 0.21214046 -0.0028023
37 31-Mar-05 0.00667457 0.00370299
38 31-Mar-05 0.0623278 -0.0021584
39 31-Mar-05 -0.0062717 0.000341234
40 31-Mar-05 0.0101843 -0.0034151
41 31-Mar-05 -0.022794 -0.0074877
42 31-Mar-05 0.169917216 -0.0030941
43 31-Mar-05 0.0108594 0.00377686
44 31-Mar-05 0.00473353 -0.00067654
1 30-Jun-05 0.009307 -0.00030918
2 30-Jun-05 0.00774149 -0.00020542
3 30-Jun-05 0.011545 -0.0020038
4 30-Jun-05 0.194446784 -0.002323
5 30-Jun-05 0.0149996 -0.0021457
6 30-Jun-05 0.080947592 -0.0019171
7 30-Jun-05 0.110467025 0.00173641
8 30-Jun-05 0.116229935 -0.0028516
9 30-Jun-05 0.053037 -0.0060764
10 30-Jun-05 0.0205285 0.00401539
11 30-Jun-05 0.07412847 -0.0036452
12 30-Jun-05 0.000165941 0.00115735
13 30-Jun-05 0.0157843 -0.0045755
14 30-Jun-05 0.0198364 -0.0068052
15 30-Jun-05 0.000822336 0.00199478
16 30-Jun-05 0.0357898 -0.00060878
17 30-Jun-05 -0.0036613 0.000226096
18 30-Jun-05 0.098482726 -0.0055159
19 30-Jun-05 0.0253289 0.000002754
20 30-Jun-05 0.0423226 0.00106245
21 30-Jun-05 0.168881853 -0.0012286
22 30-Jun-05 -0.009306 -0.0013763
23 30-Jun-05 0.0147948 0.000824364
24 30-Jun-05 0.107032711 -0.0013647
25 30-Jun-05 0.0269755 -0.0035086
26 30-Jun-05 0.00559733 0.00263787
27 30-Jun-05 0.00441441 0.00371973
28 30-Jun-05 -0.032813 0.000008644
29 30-Jun-05 0.09168613 -0.002004
30 30-Jun-05 0.218771292 -0.0020551
31 30-Jun-05 0.0148921 -0.0011354
32 30-Jun-05 0.0342261 0.000230794
33 30-Jun-05 0.023561 0.000594719
34 30-Jun-05 0.0212158 -0.00059915
35 30-Jun-05 0.0176603 -0.0043151
36 30-Jun-05 -0.0044589 0.00173475
37 30-Jun-05 0.000541246 0.00275968
38 30-Jun-05 -0.0040414 0.00162118
39 30-Jun-05 -0.0035244 0.000688753
40 30-Jun-05 0.136322452 -0.0020837
41 30-Jun-05 0.0137513 0.00307359
42 30-Jun-05 0.0579321 0.000323705
43 30-Jun-05 0.0140361 0.00403422
44 30-Jun-05 -0.013 0.000571995
45 30-Jun-05 0.0125506 -0.0053177
46 30-Jun-05 0.0102302 -0.0073835
47 30-Jun-05 0.186963265 -0.0023848
48 30-Jun-05 0.014541 0.00236808
49 30-Jun-05 0.00431307 -0.0017364
50 30-Jun-05 0.0498479 0.00467702
1 30-Sep-05 -0.0063786 0.000907728
2 30-Sep-05 0.0101038 0.000871673
3 30-Sep-05 0.0284981 0.00153884
4 30-Sep-05 0.121115631 -0.0015127
5 30-Sep-05 0.0306446 0.0015302
6 30-Sep-05 0.067656719 -0.00009431
7 30-Sep-05 0.108191664 -0.0018033
8 30-Sep-05 0.089732855 -0.0044431
9 30-Sep-05 0.065786426 -0.0028578
10 30-Sep-05 0.0166163 0.0128766
11 30-Sep-05 0.096876763 -0.0014895
12 30-Sep-05 0.000536793 -0.0005081
13 30-Sep-05 0.0342551 0.00110223
14 30-Sep-05 0.0504572 -0.006145
15 30-Sep-05 0.00601089 0.00436036
16 30-Sep-05 0.0417421 0.000042022
17 30-Sep-05 -0.0026028 -0.00061885
18 30-Sep-05 0.0491393 -0.0041615
19 30-Sep-05 -0.011321 0.00121491
20 30-Sep-05 0.0262681 0.00412299
21 30-Sep-05 0.195736815 0.00112513
22 30-Sep-05 -0.014973 -0.00050243
23 30-Sep-05 0.0356525 0.00176706
24 30-Sep-05 0.085042159 -0.000826
25 30-Sep-05 0.0305232 -0.0016151
26 30-Sep-05 0.00321965 0.0029778
27 30-Sep-05 0.00560532 0.00295675
28 30-Sep-05 -0.031548 0.00185938
29 30-Sep-05 0.0605934 -0.0010561
30 30-Sep-05 0.174065395 -0.0017657
31 30-Sep-05 0.012552 -0.00016551
32 30-Sep-05 0.0313465 0.00155516
33 30-Sep-05 0.015045 0.00141682
34 30-Sep-05 0.075091573 0.00254347
35 30-Sep-05 0.0438968 -0.00075478
36 30-Sep-05 -0.0038023 0.0039141
37 30-Sep-05 -0.007097 0.00315936
38 30-Sep-05 -0.0025448 0.00192762
39 30-Sep-05 -0.0066275 0.00092711
40 30-Sep-05 0.13281018 -0.0062985
41 30-Sep-05 0.173510881 -0.0072015
42 30-Sep-05 0.00675264 0.00554874
43 30-Sep-05 0.0294203 -0.0026573
44 30-Sep-05 0.0101847 0.00369176
45 30-Sep-05 -0.014731 0.00119482
46 30-Sep-05 0.063999589 -0.0046412
47 30-Sep-05 0.0483339 -0.0070585
48 30-Sep-05 0.252721641 -0.0021684
49 30-Sep-05 0.0100197 0.00268955
50 30-Sep-05 0.00391579 0.00139966
51 30-Sep-05 0.0380953 0.000094518
1 31-Dec-05 0.0131905 -0.00029097
2 31-Dec-05 0.0144021 0.000538923
3 31-Dec-05 0.0276551 0.00115489
4 31-Dec-05 0.0132871 -0.00094229
5 31-Dec-05 0.0124777 0.00386587
6 31-Dec-05 0.00469583 -0.00051309
7 31-Dec-05 0.00615574 0.000695245
8 31-Dec-05 0.0086013 -0.0022949
9 31-Dec-05 0.0245215 -0.0021632
10 31-Dec-05 0.0157078 -0.00065568
11 31-Dec-05 0.00368506 0.0096235
12 31-Dec-05 0.0135509 -0.0014549
13 31-Dec-05 0.00123486 0.00413094
14 31-Dec-05 0.0307219 0.00179464
15 31-Dec-05 0.0177341 -0.0051517
16 31-Dec-05 0.0119216 0.00474353
17 31-Dec-05 0.0123691 0.00145782
18 31-Dec-05 0.00476836 0.00414512
19 31-Dec-05 0.00727119 -0.0033807
20 31-Dec-05 0.00205216 0.0033979
21 31-Dec-05 0.00151617 0.003921
22 31-Dec-05 0.0150062 0.00132232
23 31-Dec-05 0.00245666 -0.000025677
24 31-Dec-05 0.0229298 0.00283142
25 31-Dec-05 0.00700216 -0.00078955
26 31-Dec-05 0.0143618 -0.0017033
27 31-Dec-05 0.0056572 0.00275935
28 31-Dec-05 0.0022097 0.00164912
29 31-Dec-05 0.00257962 0.00111764
30 31-Dec-05 0.0159053 0.000637337
31 31-Dec-05 0.0151542 -0.0009783
32 31-Dec-05 0.0099024 0.00231647
33 31-Dec-05 0.026267 0.00494402
34 31-Dec-05 0.0245294 0.00164092
35 31-Dec-05 0.000010427 0.00503388
36 31-Dec-05 0.0476632 0.000713119
37 31-Dec-05 0.00127324 0.00312471
38 31-Dec-05 0.001854 0.00447179
39 31-Dec-05 0.00219443 0.00193825
40 31-Dec-05 0.00251093 0.00168201
41 31-Dec-05 0.078075201 -0.0031058
42 31-Dec-05 0.0151925 0.0080064
43 31-Dec-05 0.0168015 -0.0061668
44 31-Dec-05 0.00678574 0.00206148
45 31-Dec-05 0.0214962 0.0030498
46 31-Dec-05 0.0401143 -0.0026568
47 31-Dec-05 0.0236629 -0.009472
48 31-Dec-05 0.0190439 -0.0012702
49 31-Dec-05 0.0024767 0.00397258
50 31-Dec-05 0.0224459 0.00283253
51 31-Dec-05 0.00264168 -0.00020995

;
run;