Help using Base SAS procedures

How to aggregate institutional ownership from thomson reuters?

Reply
New Contributor
Posts: 2

How to aggregate institutional ownership from thomson reuters?

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.

Ask a Question
Discussion stats
  • 0 replies
  • 845 views
  • 0 likes
  • 1 in conversation