How to aggregate institutional ownership from thomson reuters?

Prometheus — Mon, 19 Jan 2015 17:52:48 GMT

Hi,

I am struggling to figure out a very straightforward task-- calculating the % of institutional ownership for stocks at the end of each year from the Thomson s34 file. I currently run:

libname mywork 'C:\Users\Mine\SASfiles';

%macro signon;

%let wrds=wrds.wharton.upenn.edu 4016;

options comamid=TCP remote=WRDS;

signon wrds username=_prompt_;

%mend signon;

%signon;

libname comp remote ('/wrds/comp/sasdata/nam' '/wrds/comp/sasdata/nam/index') server=wrds;

libname ibes remote ('/wrds/ibes/sasdata') server=wrds;

libname tfn remote('/wrds/tfn/sasdata/s34') server=wrds;

libname rwork slibref=work server=wrds;

rsubmit;

options sasautos=('/wrds/wrdsmacros/', SASAUTOS) MAUTOSOURCE;

%let bdate='01jan2001'd; /*start calendar date of fiscal period beginning*/

%let edate='31dec2012'd; /*end calendar date of fiscal period end */

endrsubmit;

rsubmit;

proc sql;

create table thom as select mgrno, country, cusip, fdate, ticker, stkname, stkcdesc, shares/1000000 as sharesown, shrout1, shares/(shrout1*1000000) as instown from tfn.s34 where

(stkcdesc= 'COM' or stkcdesc='CMA' or stkcdesc='CMB' or stkcdesc='CMC' or stkcdesc='') and

year(fdate)>=%sysfunc(year(&bdate)) and year(fdate)<=%sysfunc(year(&edate)) and month(fdate)=12 and not missing(shrout1) and shrout2 ne 0;

quit;

endrsubmit;

rsubmit;

proc download data=thom out=mywork.thom;

endrsubmit;

proc sort data=mywork.thom out=mywork.nodupthom nodupkey; by fdate mgrno cusip; run;

proc sql;

create table mywork.totali as select cusip, fdate, sum(instown) as totalown from mywork.thom group by fdate, cusip;

quit;

However, this procedure results in a number of observations greater than 100% ! Please help.

topic How to aggregate institutional ownership from thomson reuters? in SAS Procedures

How to aggregate institutional ownership from thomson reuters?