Skip to content

Instantly share code, notes, and snippets.



Last active Aug 26, 2020
What would you like to do?
WRDS - create dataset with Compustat, CRSP and IBES identifiers
Macro that creates a dataset with Compustat, CRSP and IBES identifiers (gvkey, permno, Ibes ticker)
for fiscal years in period start-end.
macro parameters:
@dsout: name of dataset to create
@start: start year
@end: end year
@compvars: list of variables to get from compustat, default value: at sale ceq ni
@minscore: ibes iclink minimum score (0 [default] is best score, 6 worst, see
sample usage: %CCI(dsout=work.a_cci, start=2000, end=2014);
Invoke the macro from a filed that is saved in the same directory (this is needed for SAS to figure out
the relative path; change relative path if needs to be stored in another directory).
The created dataset will include the Compustat records with missing permno and missing Ibes ticker.
%macro CCI (dsout=, start=, end=, compvars=at sale ceq ni, minscore=0);
/* sign on wrds */
%let wrds = 4016;options comamid = TCP remote=WRDS;
signon username=_prompt_;
/* determine file path (for relative paths) */
%let path= %sysfunc(tranwrd(%sysget(SAS_EXECFILEPATH),%sysget(SAS_EXECFILENAME),));
/* syslput pushes macro variables to the remote connection */
%syslput dsout = &dsout;
%syslput start = &start;
%syslput end = &end;
%syslput compvars = &compvars;
%syslput minscore = &minscore;
%syslput path = &path;
/* remote submit start */
/* upload (file iclink needs to be in same directory as this file) */
proc upload infile="&" outfile='~/'; run;
/* execute (creates home.iclink) */
%include '~/';
/* Firms in Compustat in fiscal year range*/
data a_comp (keep = gvkey fyear datadate &compvars );
set comp.funda;
/* require fyear to be within start-end range */
if &start <=fyear <= &end;
/* prevent double records */
if indfmt='INDL' and datafmt='STD' and popsrc='D' and consol='C' ;
/* Match with CCM linktable to get permno */
proc sql;
create table b_permno as
select a.*, b.lpermno as permno
from a_comp a left join crsp.ccmxpf_linktable b
on a.gvkey eq b.gvkey
and b.lpermno ne .
and b.linktype in ("LC" "LN" "LU" "LX" "LD" "LS")
and b.linkprim IN ("C", "P")
and ((a.datadate >= b.LINKDT) or b.LINKDT eq .B) and
((a.datadate <= b.LINKENDDT) or b.LINKENDDT eq .E) ;
/* Match with home.iclink to get Ibes ticker */
proc sql;
create table c_ibes as
select a.*, b.ticker as ibesticker
from b_permno a left join home.iclink b
on a.permno = b.permno and b.score <= &minscore;
/* download */
proc download data=c_ibes out=&dsout;run;
/* remote submit end */
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment