Hi everyone, I have created macro to test for multicollinearity but I have a couple problems that I need help from the following code. %macro vif_automated(target,num);
%let VIF_limit=2;
%let VIF_val=100;
/*Variable list*/
proc sql noprint;
select Variable
into :varlist
separated by " "
from PD.cluster_&segment._select
where variable not in ("&target.");
quit;
/*Create a blank table for removed variable*/
proc reg data=PD.MEF_ALL_&segment.;
model &target.=&varlist. /vif;
ods output parameterestimates=vif;
quit;
data removed_variable_list;
set vif;
if _n_=0 then output removed_variable_list;
run;
/*Loop*/
%do %while (%sysevalf(&vif_val.>&VIF_limit.));
proc reg data=PD.MEF_ALL_&segment.;
model &target.=&varlist. /vif;
ods output parameterestimates=vif;
quit;
dm 'odsresults; clear';
dm 'log; clear';
proc sort data=vif;
by descending VarianceInflation;
quit;
data vif_top vif_others;
set vif;
if _n_=1 then output vif_top;
if _n_>1 then output vif_others;
run;
data vif_top;
set vif_top;
call symput(compress("vif_val"),compress(VarianceInflation));
run;
data removed_variable_list;
set vif_top removed_variable_list;
run;
proc sql;
select distinct variable
into: varlist
separated by " "
from vif_others
where variable^="Intercept";
quit;
%end;
data final_variable_vif_&num.;
set vif;
run;
%mend;
%vif_automated(ConLoanc_M12_lg2,1);
%vif_automated(X_CVM_M12_lg4,2);
%vif_automated(C_CVM_M6_lg1,3);
%vif_automated(Auto_sale_comm_C,4);
%vif_automated(C_CVM_M12_lg3,5);
%vif_automated(C_CVM_M6_lg4,6);
%vif_automated(C_CVM_M6_lg3,7);
%vif_automated(Farm_C_lg1,8);
%vif_automated(X_CVM_M6_lg2,9);
%vif_automated(UR_M6,10);
%vif_automated(X_CVM_LN_lg4,11);
%vif_automated(X_CVM_M3_lg4,12);
%vif_automated(Auto_sale_Pass_C,13);
%vif_automated(CCI_C_lg2,14);
%vif_automated(UR_LN,15);
%vif_automated(UR_M3,16);
%vif_automated(C_CVM_lg3,17);
%vif_automated(Farm_C,18); I try to avoid to type Y variable name as a target by write code like above. Are there any faster way to set one of the variables in PD.cluster_&segment._select file as Y and the rest of them are X. Another thing is that when I manually set Y variable, I have to put the number 1,2,3,... to keep result. Are there any way to create the final results file as one time after all combination. Thank you so much
... View more