Skip to content

Instantly share code, notes, and snippets.

@JoostImpink JoostImpink/CCI.sas
Last active Apr 26, 2019

Embed
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 iclink.sas)
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 = wrds.wharton.upenn.edu 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 */
rsubmit;
/* upload iclink.sas (file iclink needs to be in same directory as this file) */
proc upload infile="&path.iclink.sas" outfile='~/iclink.sas'; run;
/* execute iclink.sas (creates home.iclink) */
%include '~/iclink.sas';
/* 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' ;
run;
/* 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) ;
quit;
/* 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;
quit;
/* download */
proc download data=c_ibes out=&dsout;run;
/* remote submit end */
endrsubmit;
%mend;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.