Many thanks for your help so far PGStats! As told you before, I need to calculate this formula: where K is the number of securities σ(RawΔκ,t) is the cross-sectional standard deviation of the raw fraction of investors buying the security k in quarter t σ(RawΔκ,t-1) is the cross-sectional standard deviation of the raw fraction of investors buying the security k in quarter t-1 RawΔκ,t bar is the cross-sectional average raw fraction of institutions buying in quarter t RawΔκ,t-1 bar is the cross-sectional average raw fraction of institutions buying in quarter t-1 Dn,k,t is a dummy variable taking the value of one when investor n is a buyer of security k in quarter t and zero when fund n is a seller of security k in quarter t (in the example database a buyer is indicated as + and a seller as -) N k,t is the number of investors actively trading security k in quarter t Here is what I have so far. For the first term: proc freq data= tmp1.example; tables date / out=K noprint; run; proc sql; create table K1 as select date, count, intnx("QTR",date,0) as qtr format=yyqd7.,count-1 as K_1 from K ; quit; proc means data=fraction mean std nway noprint; var fraction; class qtr; output out=want (drop=_:) mean= std= /autoname; run; data K_1; set want; set K1 (keep=K_1); frmean_1= lag(fraction_mean); frstddev_1=lag(fraction_stddev); run; data fcoefficient; set K_1; coefficient= 1/(k_1*fraction_stddev*frstddev_1); run; For the second term: data a1;set tmp1.example (keep= date mgrno ticker sign); rename date=qtr; if sign='+' then D=1; if sign='-' then D=0; if sign='0' then D=.; run; proc sql; create table N as select ticker, intnx("QTR",date,0) as qtr format=YYQD6., sum(sign="+")+(sum(sign="-")) as N fromtmp1.example group by calculated qtr, ticker order by calculated qtr, ticker; quit; proc sql; create table a2 as select A.ticker, A.qtr, A.N, B.Fraction_mean, B.frmean_1 from N as A inner join A as B on A.qtr=B.qtr order by qtr, ticker; quit; proc sql; create table a3 as select put(A.qtr, yyq6.) as QTR, A.mgrno, A.ticker, A.D, B.N, B.fraction_mean, B.frmean_1 from a1 as A left join a2 as B on put(A.qtr, yyq6.)=put(B.qtr, yyq6.) and A.ticker=B.ticker order by qtr, ticker; quit; I had to convert "qtr" to character variables in order to be able to merge the last two databases as they had different numeric values after using intnx before. However, now I am stuck to go further. First of all I need Dn,k,t-1 and Nk,t-1 which I find quite difficult especially and then calculate the expression in the brackets for each stock. I attach a file how I used to calculate the term in excel. This is for one stock, so I had to do it for each stock separately. Could anyone please help?
... View more
