
/*THIS PROGRAM CLEANS UP THE RAVENPACK DATA*********************************************************/
/*NEWS CLASSIFICATION: USE VARIABLE GROUP (25 CATEGORIES) FOLLOWING BILLINGS ET AL. (2021 RAST) TABLE 7 PANEL A*/
data rpsample; set raven.ravenpack (keep=RP_ENTITY_ID RPNA_DATE_UTC ENTITY_NAME GROUP COUNTRY_CODE RELEVANCE ESS NIP); 
where relevance=100;
label 	ESS='ESS: EVENT SENTIMENT SCORE'
		NIP='NIP: NEWS IMPACT PROJECTIONS';
format RPNA_DATE_UTC date9.; run;
/*STEP 1: CONVERT THE DATES INTO SAS FORMAT*/
data a1 (drop=range_start range_end); set raven.rp_mapping; where entity_type='COMP' and data_type='CUSIP';
startdate=input(range_start,yymmdd10.);
enddate=range_end;
format startdate enddate date9.; run;
/*STEP 2: MERGE WITH MAPPING FILE: START: 3,156,832 OBS*/
data a2; set rpsample; 
/*CREATE SERIAL NUMBER TO KEEP TRACK*/
sn=_n_; run;
proc sql;
  create table a3 as select
	a.*, b.data_value as cusip/*, b.startdate, b.enddate*/
	from a2 a left join a1 b
	on a.rp_entity_id=b.rp_entity_id and
	   b.startdate<=a.rpna_date_utc and
	  (a.rpna_date_utc <= b.enddate or b.enddate = .); quit;
data a3; set a3; if cusip='' then delete; run;
/*MERGE WITH INDEX FILE*/
proc sql;
  create table a4 as select
    a.*, b.gvkey, b.year
  from a3 a left join index b
  on substr(a.cusip,1,8)=b.ncusip and
  	 b.fybegdt<=a.rpna_date_utc<=fyenddt; quit;
proc sort data=a4 nodupkey; by sn cusip; run;
data a4; set a4; if gvkey='' then delete; run;
proc sort data=a4 nodupkey; by sn; run;
data rpsample; set a4; run;

PROC EXPORT DATA= WORK.rpsample 
            OUTFILE= "C:\JAR Files\rpsample.dta" 
            DBMS=STATA REPLACE; run;
RUN;
