BookmarkSubscribeRSS Feed
Prometheus
Calcite | Level 5

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 0 replies
  • 3429 views
  • 0 likes
  • 1 in conversation