You should verify the output of your code using independent method.  For example, run your sequential double sort/rank on your test data using excel and then verify that you get the same results with your SAS code.  This way you will know for certain that your code is operating as expected.     ... View more

This is the same question you posted in Finding a stock within fund holdings in previous quarter, which has many more answers.  I'd suggest closing this question. ... View more

sorry i could not figure out the issue and dropped these fitstatistics=fitresult OutputStatistics=residual; from ods output parameterestimates=pa0 fitstatistics=fitresult OutputStatistics=residual; quit; it worked for me. Abdul ... View more

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

Fixed some error.   data have; call streaminit(1234); do id=1 to 4; do date='01jan2010'd to '01jan2014'd; Rt=rand('normal'); Rtm=rand('lognor'); Rti=rand('uniform'); output; end; end; format date date9.; run; proc freq data=have noprint; table id*date/out=key nocum nopercent; run; data key; set key(drop=count percent); start=intnx('month',date,-48,'s'); end=date; drop date; format start end date9.; run; %macro reg(id=,start=,end=); proc reg data=have(where=(id=&id and date between &start and &end)) outest=Coeff noprint; model Rt = Rtm Rti; run; proc append base=want data=coeff force;run; %mend; proc delete data=want;run; data _null_; set key; call execute(cats('%reg(id=',id,',start=',start,',end=',end,')')); run;     ... View more