Help with a model

[PGStats]

SPREFIX option in PROC STDIZE is new to SAS version 9.3. You can get compatible results using earlier versions with:

proc stdize data=fraction out=stdFraction(rename=fraction=stdFraction);

by qtr;

var fraction;

run;

PG

[Costasg]

Thanks for that PG.

I would like to ask you something

When we used this  intnx("QTR",date,0) as qtr format=yyqd7. at the first query, did it mix up the dates, cause I am trying to merge the results with the initial database and I can't. The dates have different numerical values. Could this be caused by that?

Thank you for your time.

[PGStats]

For a given date, expression intnx("QTR",date,0) returns the date of the first day of the quarter to which date belongs. Thus all dates from the same quarter get the same value for qtr. Variable qtr is required as a grouping factor when standardizing the fractions within each quarter.

[Costasg]

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?