
/*THIS PROGRAM CREATES A DATASET OF COMPUSTAT UNIVERSE FIRMS FOR THE SAMPLE PERIOD 1985-2017*/
/*THE DATASET WILL CONTAIN THE UNIQUE IDENTIFIERS TO MERGE WITH OTHER DATASETS LATER*/
rsubmit;
data a1(keep=gvkey conm tic year fybegdt fyenddt); set comp.funda; 
where consol='C' and indfmt='INDL' and datafmt='STD' and popsrc='D' and curcd = 'USD' and 1985<=fyear<=2017;
fybegdt=intnx('month',datadate,-12,'END')+1;
fyenddt=datadate;
rename fyear=year;
format fybegdt fyenddt date9.; run;
proc sort data=a1 nodupkey; by gvkey year; run;
/*OBTAIN 4-DIGIT SIC CODES & CIK*/
proc sql;
  create table a2 as select
    a.*, b.sic, b.cik, b.naics
  from a1 a left join comp.names b
  on a.gvkey=b.gvkey and
	 b.year1<=a.year<=b.year2; quit;
proc sort data=a2 nodupkey; by gvkey year; run;
/*OBTAIN PERMNO FROM CRSP-COMPUSTAT LINK FILE*/
proc sql;
  create table a3 as select
    a.*, b.lpermno as permno, b.linkdt, b.linkenddt
  from a2 a left join crsp.ccmxpf_linktable b
  on a.gvkey=b.gvkey and
	 b.linktype in ("LU" "LC" "LD" "LF" "LN" "LO" "LS" "LX") and 
	(b.LINKDT <= a.fyenddt or b.LINKDT = .B) and 
    (a.fyenddt <= b.LINKENDDT or b.LINKENDDT = .E) and
	 b.usedflag=1 and
	 b.lpermno~=.; quit;
proc sort data=a3; by gvkey year descending linkenddt;
proc sort data=a3 out=a3 (drop=linkdt linkenddt) nodupkey; by gvkey year; run;
/*OBTAIN CUSIP/NCUSIP FROM CRSP*/
proc sql;
  create table a4 as select
    a.*, b.cusip, b.ncusip
  from a3 a left join crsp.stocknames b 
    on (a.permno = b.permno) and
	   (b.NAMEDT <= a.fyenddt) and 
       (a.fyenddt <= b.NAMEENDDT); quit;
proc sort data=a4 nodupkey; by gvkey year; run;
/*OBTAIN EARNINGS ANNOUNCEMENT DATE FROM FUNDQ*/
proc sql;
  create table a5 as select
    a.*, b.rdq
  from a4 a left join comp.fundq b
  on a.gvkey=b.gvkey and
  	 a.fyenddt=b.datadate and
	 b.fqtr=4 and 
	 b.consol='C' and b.indfmt='INDL' and b.datafmt='STD' and b.popsrc='D' and b.curcdq = 'USD'; quit;
proc sort data=a5 nodupkey; by gvkey year; run;
data index; set a5; run;
proc download data=index out=index; run;
endrsubmit;
