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.