options errorabend;

libname perm '../data';

data x; set perm.ea_participation;
run;

proc sort data = x out = bearish (keep=permno mostimportantdateutc) nodupkey; by permno mostimportantdateutc;
run;

/* 	Use wrds_data to merge sue to the analyst dataset - merge in mostimportantdateutc to wrds_data on rdq date 
		and keep unique obs and then merge sue to analyst dataset using permno and mostimportantdateutc		*/
data wrds_data; set perm.wrds_data (keep=gvkey permno datadate rdq sue siccd);
array vars sue ;
do over vars;
if missing(vars) then delete;
end;
sic2 = substr(left(siccd),1,2);
run;

proc sql;
	create table wrds_data_2 as select distinct
	a.*, b.mostimportantdateutc
	from wrds_data as a inner join bearish as b
	on a.permno = b.permno
	and a.rdq-15 le b.mostimportantdateutc le a.rdq+15
	order by gvkey, datadate, mostimportantdateutc;
quit;

proc sort data = wrds_data_2 nodupkey; by gvkey datadate;
run;

/*
proc contents data =x;
run;
*/

/* Separate engagement with bearish analysts based on type of analyst or firm situation */
/* -	High Pct Bearish */
/* -	Ext Negative News */
/* -	All-star Analysts */
/* -	Analysts from large brokers */

data x2; set x (keep=ticker permno mostimportantdateutc amaskcd rec5_lvl bearish in_ priority allstar_dummy numanalystbroker word_count_ans word_count_ans_scaled word_count_qa qa_min);
run;

/*
proc means data = x n mean std min p25 p50 p75 max;
var numanalystbroker  ;
where ccuser ne . and genexp ne . and numanalystbroker ne . and numfirmscov ne . and priorparticipate ne . and n_inds ne . and prioracc  ne . ;
run;
*/
**********************;
**********************;
**********************;
/* ****** Check thresholds matchup with final sample ********************** */
**********************;
**********************;
**********************;
data x3; set x2;
* 75th and 25th percentile using the final dataset;
if numanalystbroker >= 105 then h_numanalystbroker = 1;
else if numanalystbroker ne . then h_numanalystbroker = 0;
if numanalystbroker <= 20 and numanalystbroker ne . then l_numanalystbroker = 1;
else if numanalystbroker ne . then l_numanalystbroker = 0;
run;

proc sql;
	create table x4 as select distinct
	a.*, b.sue
	from x3 as a left join wrds_data_2 as b
	on a.permno = b.permno
	and a.mostimportantdateutc = b.mostimportantdateutc;
quit;

proc sort data = x4 nodupkey; by ticker mostimportantdateutc amaskcd;
run;

/* Calculate pct_bearish following the firm*/
proc sql;
    create table x4b as
    select distinct
        *, mean(bearish) as pct_bearish
    from x4
    group by ticker, mostimportantdateutc;
quit;

**********************;
**********************;
**********************;
/* ****** The partitioning values come from summary stats of final sample ********************** */
**********************;
**********************;
**********************;
data x5; set x4b;
* 75th and 25th percentile using the full sample;
if pct_bearish >= .25 then h_pct_bearish = 1;
else if pct_bearish ne . then h_pct_bearish = 0;
if pct_bearish <=.0588235 then l_pct_bearish = 1;
else if pct_bearish ne . then l_pct_bearish = 0;
* 25th and 75th percentile using the final dataset;
if sue <= -.0002818 and sue ne . then ext_neg_sue = 1;
else if sue ne . then ext_neg_sue = 0;
if sue >= .0019601 then ext_pos_sue = 1;
else if sue ne . then ext_pos_sue = 0;
comp = (h_pct_bearish = 1) or (ext_neg_sue = 1) or (allstar_dummy = 1) or (h_numanalystbroker = 1);
ncomp = 1-comp;
comp2 = (h_numanalystbroker = 1) or (h_pct_bearish = 1) or (allstar_dummy = 1);
ncomp2 = 1-comp2;
comp3 = (h_numanalystbroker = 1) or (h_pct_bearish = 1);
ncomp3 = 1-comp3;
run;

* Prepare to aggregate at the firm-quarter and separate engagement by:
		h_pct_bearish l_pct_bearish ext_neg_sue ext_pos_sue 
		allstar_dummy h_numanalystbroker l_numanalystbroker
;
data x5; set x5;
b_neg_sue = bearish*ext_neg_sue;
b_h_pct_b = bearish*h_pct_bearish;
b_l_pct_b = bearish*l_pct_bearish;
b_allstar = bearish*allstar_dummy;
b_lg_broker = bearish*h_numanalystbroker;
b_sm_broker = bearish*l_numanalystbroker;
b_pos_sue = bearish*ext_pos_sue;
b_c = bearish*comp;
b_nc = bearish*ncomp;
b_c2 = bearish*comp2;
b_nc2 = bearish*ncomp2;
b_c3 = bearish*comp3;
b_nc3 = bearish*ncomp3;
b_wc_ans = bearish*word_count_ans;
b_wc_ans_sc = bearish*word_count_ans_scaled;
b_wc_ans_c2 = comp2*bearish*word_count_ans ;
b_wc_ans_sc_c2 = comp2*bearish*word_count_ans_scaled ;
b_wc_ans_nc2 = ncomp2*bearish*word_count_ans ;
b_wc_ans_sc_nc2 = ncomp2*bearish*word_count_ans_scaled ;
fav_wc_ans = (1-bearish)*word_count_ans;
fav_wc_ans_sc = (1-bearish)*word_count_ans_scaled;
rename bearish=b;
run;


/* Step 1: Define Metrics to Aggregate Dynamically */
%let metrics = b b_neg_sue b_h_pct_b b_allstar b_lg_broker 
               		   b_pos_sue b_l_pct_b b_sm_broker b_c b_nc b_c2 b_nc2 b_c3 b_nc3;

/* Macro to Generate Aggregation Code for SQL */
%macro aggregate_metrics(metrics);
    %let i = 1;
    %do %while(%scan(&metrics, &i) ne );
        %let metric = %scan(&metrics, &i);

        /* Add SQL aggregation statements */
        sum(&metric*in_) as n_&metric._in, mean(&metric) as pct_&metric.,
        max(&metric*in_) as firm_qtr_&metric._ind,
        case when sum(in_) > 0 then sum(&metric*in_) / sum(in_) else . end as &metric._in_firm_qtr,
        max(&metric*priority) as max_&metric._priority

        /* Add a comma unless it's the last iteration */
        %if %scan(&metrics, %eval(&i + 1)) ne %then %do;
            ,
        %end;

        %let i = %eval(&i + 1);
    %end;
%mend;

/* Step 2: Aggregate Data with SQL */
proc sql;
    create table y as
    select distinct
        ticker, permno, mostimportantdateutc, sue, 
        count(in_) as n_analysts, sum(in_) as n_in, 
        sum(b) as n_b, 
        %aggregate_metrics(&metrics) , 
		mean(word_count_ans) as wc_ans_mean, mean(b_wc_ans) as b_wc_ans_mean, 
		mean(b_wc_ans_sc) as b_wc_ans_sc_mean, mean(fav_wc_ans) as fav_wc_ans_mean, 
		mean(fav_wc_ans_sc) as fav_wc_ans_sc_mean, sum(word_count_ans) as wc_ans_sum, sum(b_wc_ans) as b_wc_ans_sum, 
		sum(b_wc_ans_sc) as b_wc_ans_sc_sum, sum(fav_wc_ans) as fav_wc_ans_sum, 
		sum(fav_wc_ans_sc) as fav_wc_ans_sc_sum, mean(word_count_qa) as wc_qa, mean(qa_min) as qa_min,
		sum(b_wc_ans_c2) as b_wc_ans_c2_sum, sum(b_wc_ans_sc_c2) as b_wc_ans_sc_c2_sum,
		mean(b_wc_ans_c2) as b_wc_ans_c2_mean, mean(b_wc_ans_sc_c2) as b_wc_ans_sc_c2_mean,
		sum(b_wc_ans_nc2) as b_wc_ans_nc2_sum, sum(b_wc_ans_sc_nc2) as b_wc_ans_sc_nc2_sum,
		mean(b_wc_ans_nc2) as b_wc_ans_nc2_mean, mean(b_wc_ans_sc_nc2) as b_wc_ans_sc_nc2_mean
    from x5
    group by ticker, mostimportantdateutc;
quit;

/* Step 3: Reset Variables Based on Conditions */
/* Reset Variables Dynamically Based on pct_&metric */
/* Macro to Generate Reset Logic */
%macro reset_variables(metrics);
    %let i = 1;
    %do %while(%scan(&metrics, &i) ne );
        %let metric = %scan(&metrics, &i);

        /* Generate conditional statements */
        if pct_&metric = 0 or pct_&metric = 1 then do;
            firm_qtr_&metric._ind = .;
            &metric._in_firm_qtr = .;
            max_&metric._priority = .;
        end;

        %let i = %eval(&i + 1);
    %end;
%mend;


/* Reset Variables Dynamically Based on pct_&metric */
data y2;
    set y;

    /* Include dynamically generated reset logic */
    %reset_variables(&metrics);
	if pct_b = 0 then do;
		b_wc_ans_mean = .; 
		b_wc_ans_sc_mean = .;
		b_wc_ans_sum = .;
		b_wc_ans_sc_sum = .;
	end;
	if pct_b_c2 = 0 then do;
		b_wc_ans_c2_mean = .; 
		b_wc_ans_sc_c2_mean = .;
		b_wc_ans_c2_sum = .;
		b_wc_ans_sc_c2_sum = .;

		b_wc_ans_nc2_mean = .; 
		b_wc_ans_sc_nc2_mean = .;
		b_wc_ans_nc2_sum = .;
		b_wc_ans_sc_nc2_sum = .;
	end;
    /* Flag observations with valid data */
    if firm_qtr_b_ind ne . then used = 1;
    else used = 0;
run;



/* Step 4: Automate Moving Average Transformations */
%let transforms = firm_qtr_b_ind b_in_firm_qtr max_b_priority 
					firm_qtr_b_neg_sue_ind b_neg_sue_in_firm_qtr max_b_neg_sue_priority 
					firm_qtr_b_h_pct_b_ind b_h_pct_b_in_firm_qtr max_b_h_pct_b_priority 
					firm_qtr_b_allstar_ind b_allstar_in_firm_qtr max_b_allstar_priority 
	                firm_qtr_b_lg_broker_ind b_lg_broker_in_firm_qtr max_b_lg_broker_priority 
					firm_qtr_b_pos_sue_ind b_pos_sue_in_firm_qtr max_b_pos_sue_priority 
					firm_qtr_b_l_pct_b_ind b_l_pct_b_in_firm_qtr max_b_l_pct_b_priority 
					firm_qtr_b_sm_broker_ind b_sm_broker_in_firm_qtr max_b_sm_broker_priority
					firm_qtr_b_c_ind b_c_in_firm_qtr max_b_c_priority
					firm_qtr_b_nc_ind b_nc_in_firm_qtr max_b_nc_priority
					firm_qtr_b_c2_ind b_c2_in_firm_qtr max_b_c2_priority
					firm_qtr_b_nc2_ind b_nc2_in_firm_qtr max_b_nc2_priority
					firm_qtr_b_c3_ind b_c3_in_firm_qtr max_b_c3_priority
					firm_qtr_b_nc3_ind b_nc3_in_firm_qtr max_b_nc3_priority
					;

%let mini_transforms = wc_ans_mean b_wc_ans_mean b_wc_ans_sc_mean fav_wc_ans_mean fav_wc_ans_sc_mean
					wc_ans_sum b_wc_ans_sum b_wc_ans_sc_sum fav_wc_ans_sum fav_wc_ans_sc_sum
					wc_qa qa_min 
					b_wc_ans_c2_mean b_wc_ans_sc_c2_mean b_wc_ans_c2_sum b_wc_ans_sc_c2_sum
					b_wc_ans_nc2_mean b_wc_ans_sc_nc2_mean b_wc_ans_nc2_sum b_wc_ans_sc_nc2_sum
					;

/* Macro to Apply Moving Average Transformations */
%macro apply_transforms(transforms);
    %let i = 1;
    %do %while(%scan(&transforms, &i) ne );
        %let var = %scan(&transforms, &i);

        /* Determine new variable name based on prefix and remove specific suffixes */
        %if %substr(&var, 1, 9) = firm_qtr_ %then %do;
            %let base_name = %substr(&var, 10);
            %let new_var = b_engage_%sysfunc(prxchange(s/_ind$//, -1, &base_name));
        %end;
        %else %if %substr(&var, 1, 2) = b_ %then %do;
            %let base_name = %substr(&var, 3);
            %let new_var = b_engage_2_%sysfunc(prxchange(s/_in_firm_qtr$//, -1, &base_name));
        %end;
        %else %if %substr(&var, 1, 6) = max_b_ %then %do;
            %let base_name = %substr(&var, 7);
            %let new_var = b_priority_%sysfunc(prxchange(s/_priority$//, -1, &base_name));
        %end;

        /* Apply transformation and rename */
        convert &var = &new_var / transformout=(movave 12 TRIMLEFT 2);

        %let i = %eval(&i + 1);
    %end;
%mend;

%macro apply_mini_transforms(mini_transforms);
    %let i = 1;
    %do %while(%scan(&mini_transforms, &i) ne );
        %let var = %scan(&mini_transforms, &i);

        /* Apply transformation and rename */
        convert &var = &var._12q / transformout=(movave 12 TRIMLEFT 2);

        %let i = %eval(&i + 1);
    %end;
%mend;


proc sort data = y2 nodupkey; by permno mostimportantdateutc;
run;

/* Use PROC EXPAND for Moving Averages */
proc printto log="../logs/proc_expand_junk.log" ; run;
proc expand data=y2 out=y3 method=none;
    by permno;
    %apply_transforms(&transforms);
	%apply_mini_transforms(&mini_transforms);
run;
proc printto; run;

/* Step 5: Perform PCA and Correlation Analysis */
%let groups = b_engage_b b_engage_2_in_firm_qtr b_priority_priority | /* Group 1 - Main*/ 
              b_engage_b_c b_engage_2_c b_priority_c | /* Group 2 Neg SUE, High %Bearish, Allstar, and Large Broker*/
              b_engage_b_nc b_engage_2_nc b_priority_nc | /* Group 3 Not part of the list above*/
              b_engage_b_c2 b_engage_2_c2 b_priority_c2 | /* Group 4 High %Bearish, Allstar, and Large Broker*/
              b_engage_b_nc2 b_engage_2_nc2 b_priority_nc2 | /* Group 5 Not part of the list above*/
			  b_engage_b_c3 b_engage_2_c3 b_priority_c3 | /* Group 6 High %Bearish and Large Broker*/
              b_engage_b_nc3 b_engage_2_nc3 b_priority_nc3 | /* Group 7 Not part of the list above*/
			  b_engage_b b_engage_2_in_firm_qtr b_priority_priority b_wc_ans_sc_mean_12q | /* Group 8 - Main with mean word count*/
			  b_engage_b_c2 b_engage_2_c2 b_priority_c2 b_wc_ans_sc_c2_mean_12q | /* Group 9 w/ WC Neg SUE, High %Bearish, Allstar, and Large Broker*/
              b_engage_b_nc2 b_engage_2_nc2 b_priority_nc2 b_wc_ans_sc_nc2_mean_12q  /* Group 10 w/ WC not in list above*/
			  ;

/* Macro to Perform PCA and Correlation for Each Group */
%macro analyze_and_merge(groups, varout, dataset, mainfile);
    %let i = 1;
    %do %while(%scan(&groups, &i, |) ne );
        %let group = %scan(&groups, &i, |);
        
        /* Generate a suffix based on the group */
        %let suffix = group&i;

        /* Perform PCA for the current group */
        PROC PRINCOMP DATA=&dataset OUT=prin_out&i OUTSTAT=stats&i;
        VAR &group;
        RUN;

        /* Extract prin_sir and add suffix */
        DATA &varout.&i;
            SET prin_out&i(KEEP=prin1 permno mostimportantdateutc);
            RENAME prin1 = &varout._&suffix;
        RUN;

        /* Merge prin_sir back into the main file */
        PROC SORT DATA=&varout.&i; BY permno mostimportantdateutc; RUN;
        PROC SORT DATA=&mainfile; BY permno mostimportantdateutc; RUN;

        DATA &mainfile;
            MERGE &mainfile &varout.&i;
            BY permno mostimportantdateutc;
        RUN;

        %let i = %eval(&i + 1);
    %end;
%mend;

%analyze_and_merge(groups=&groups, varout=prin_sir, dataset=y3, mainfile=y3);

* PCA for individual earnings call;
%let ind_groups = firm_qtr_b_ind b_in_firm_qtr max_b_priority |
					firm_qtr_b_c_ind b_c_in_firm_qtr max_b_c_priority |
					firm_qtr_b_nc_ind b_nc_in_firm_qtr max_b_nc_priority |
					firm_qtr_b_c2_ind b_c2_in_firm_qtr max_b_c2_priority |
					firm_qtr_b_nc2_ind b_nc2_in_firm_qtr max_b_nc2_priority |
					firm_qtr_b_c3_ind b_c3_in_firm_qtr max_b_c3_priority |
					firm_qtr_b_nc3_ind b_nc3_in_firm_qtr max_b_nc3_priority |
					firm_qtr_b_ind b_in_firm_qtr max_b_priority b_wc_ans_sc_mean |
					firm_qtr_b_c_ind b_c2_in_firm_qtr max_b_c2_priority b_wc_ans_sc_c2_mean |
					firm_qtr_b_nc2_ind b_nc2_in_firm_qtr max_b_nc2_priority b_wc_ans_sc_nc2_mean ;

%analyze_and_merge(groups=&ind_groups, varout=prin, dataset=y3, mainfile=y3);

%let outfile = comp_engage;
data perm.&outfile; set y3;
run;

proc export data = y3
	outfile = "../data/&outfile..dta"
	dbms = dta
	replace;
run;
