Proc output section into dataset - ods trace on

data tt;


do i=1 to 100;

x1=rannor(0);output;

x2=rannor(0);output;

end;

run;





ODS TRACE on / listing;



proc univariate data=tt;

var x1 x2;

run;



ODS TRACE off;





proc univariate data=tt;

var x1 x2;

ODS OUTPUT Quantiles=qntls;

run;

Outlier Treatment - 2 std

/**Outlier Treatment **/





data winsor_test;

do i = 1 to 98;

x = round(rannor(124));

y = round(rannor(24));

z = round(rannor(14));

output ;

end;

x = 989;

y = .;

z = 1234;

output;

drop i;

run;



options mlogic mprint;



%macro get_stat(datain);



proc means data = &datain. noprint ;

output out = cal_means(where=(_STAT_ in ('MEAN','STD')));

run;



proc transpose data = cal_means(drop = _type_ _freq_ _stat_) out = cal2;

run;





data _null_;

set cal2;

call symput(compress("Mean"

_n_),col1);

call symput(compress("Std"

_n_),col2);

call symput(compress("Vname"

_n_),_name_);

call symput(compress("Num"),_N_);

run;



data &datain._temp;

set &datain.;

%do i=1 %to &Num.;

if (&&Vname&i.. > &&Mean&i.. + ( 2* &&Std&i..)) OR

(&&Vname&i.. < &&Mean&i.. - ( 2* &&Std&i..)) then &&Vname&i.. = . ;

%end;

run;



proc means data = &datain._temp noprint;

output out = new_means (where=(_STAT_ in ('MEAN','STD')));

run;



proc transpose data = new_means(drop = _type_ _freq_ _stat_) out = tran_new;

run;





data _null_;

set tran_new;

call symput(compress("Mean"

_n_),col1);

call symput(compress("Std"

_n_),col2);

call symput(compress("Vname"

_n_),_name_);

call symput(compress("Num"),_N_);

run;





data &datain._final;

set &datain.;

%do i=1 %to &Num.;

New_&&Vname&i.. = &&Vname&i..;

if not missing(&&Vname&i..) then

DO;

if &&Vname&i.. > &&Mean&i.. + ( 2* &&Std&i..) then New_&&Vname&i.. = &&Mean&i.. + ( 2* &&Std&i..);

if &&Vname&i.. < &&Mean&i.. - ( 2* &&Std&i..) then New_&&Vname&i.. = &&Mean&i.. - ( 2* &&Std&i..);

END;

%end;

run;





%mend;



%get_stat(winsor_test);

retain char

data test;


input x $ page_char $ ;

datalines;

xyz page

1 xcv

2 xfv

3 ggg

pqr page

4 dfg

5 ddd

6 ggg

;run;



data test1;

set test;

if page_char="page" then

page=_N_;else page=0;

run;



data test2;

set test1;

RETAIN Page_v1 0;

Page_v1 = SUM(Page_v1, page);

run;

proc sort data=test2;by page_v1;run;



data test3(keep=New page_char x);

retain NEW;

set test2;

by page_v1;



if first.page_v1 = 1 then NEW = x;

else NEW = trim(NEW);





put _all_;

run;

Plots

options mprint symbolgen;




%macro univariate_plot(indata,invar,target,numgrps);



data srv_work.temp4plot (keep= &invar. &target.) Zeros4plot (keep= &invar. &target.) miss4plot (keep= &invar. &target.);

set srv_work.&indata.;

if &invar. = 0 then output zeros4plot;

if &invar. = . then output miss4plot;

else output srv_work.temp4plot;

run;



proc rank data=srv_work.temp4plot groups=&numgrps. out= srv_work.temp4plot;

Var &invar.;

ranks R_&numgrps.;

run;



data srv_work.fin4plot;

set srv_work.temp4plot;

run;



proc sql;

create table srv_work.t_collapsed as

select R_&numgrps.,

count(*) as N,

sum(&target.) as sum_&target.,

avg(&invar.) as M_&invar.,

avg(&target.) as &target.,

min(&invar.) as &invar.

from srv_work.fin4plot

group by r_&numgrps.;

quit;



SYMBOL1 COLOR=blue VALUE=Triangle HEIGHT=2 width=2 INTERPOL=NONE;

SYMBOL2 INTERPOL=NONE COLOR=red HEIGHT=2 VALUE=diamond WIDTH=2;



proc gplot data=srv_work.t_collapsed;

legend1 value=( "default_risk");

legend2 value=( "Number of observations");

plot &target.*&invar. / legend=legend1;

plot2 N*&invar./overlay legend=legend2;

run;

quit;



%mend;



/*Graph – Independent Variable Vs Dependent Variable */

/* Converting Total Number of observations into 40 Groups */



%univariate_plot(with_var,dlq_at_lm,default,40);

Winsorize macro

/*****************************************


* Source From - http://www.wrds.us/index.php/repository/view/1

******Trim or winsorize macro

*byvar = none for no byvar;

*type = delete/winsor (delete will trim, winsor will winsorize;

*dsetin = dataset to winsorize/trim;

*dsetout = dataset to output with winsorized/trimmed values;

*byvar = subsetting variables to winsorize/trim on;

****************************************/



data test;

do i=1 to 100;

x=round(abs(rannor(0)));

y=round(ranuni(0));output;

end;

x=900;y=1;output;

/*x=800;y=0;output;*/

drop i;

run;



%winsor(dsetin=Test,dsetout=TestOut,byvar=none,vars=x,type=winsor,pctl=1 99);



options mlogic mprint;

%macro winsor(dsetin=, dsetout=, byvar=none, vars=, type=winsor, pctl=);



%if &dsetout = %then %let dsetout = &dsetin;



%let varL=;

%let varH=;

%let xn=1;



%do %until ( %scan(&vars,&xn)= );

%let token = %scan(&vars,&xn);

%let varL = &varL &token.L;

%let varH = &varH &token.H;

%let xn=%EVAL(&xn + 1);

%end;



%let xn=%eval(&xn-1);



/* Assignin input data set to Xtemp */



data xtemp; set &dsetin; run;



/* if no byvar variable then it will assign to 1 */

%if &byvar = none %then %do;

data xtemp;

set xtemp;

xbyvar = 1;

run;

%let byvar = xbyvar;

%end;



proc sort data = xtemp;

by &byvar;

run;



proc univariate data = xtemp noprint;

by &byvar;

var &vars;

output out = xtemp_pctl PCTLPTS = &pctl PCTLPRE = &vars PCTLNAME = L H;

run;



data &dsetout;

merge xtemp xtemp_pctl;

by &byvar;

array trimvars{&xn} &vars;

array trimvarl{&xn} &varL;

array trimvarh{&xn} &varH;



do xi = 1 to dim(trimvars);



%if &type = winsor %then %do;

if not missing(trimvars{xi}) then do;

if (trimvars{xi} < trimvarl{xi}) then trimvars{xi} = trimvarl{xi};

if (trimvars{xi} > trimvarh{xi}) then trimvars{xi} = trimvarh{xi};

end;

%end;



%else %do;

if not missing(trimvars{xi}) then do;

if (trimvars{xi} < trimvarl{xi}) then delete;

if (trimvars{xi} > trimvarh{xi}) then delete;

end;

%end;



end;

drop &varL &varH xbyvar xi;

run;



%mend winsor;

Information Value

/*Information Value*/


%Macro IV_Numeric(Indep_Var);



proc rank data = test out = Ranks_Var ties = mean groups = 24;

var &Indep_Var.;

ranks X_rank_&Indep_Var.;

run;



%IV_Char(Ranks_Var,X_rank_&Indep_Var.);



%Mend IV_Numeric;



%Macro IV_Char(Data_Set,Cat_Var);



proc freq data =&Data_Set.(where=(y=1)) noprint;

table &Cat_Var.*y/out=freq_out_y1 (rename=(percent=percent1 count=count1)) ;

run;



proc freq data =&Data_Set.(where=(y=0)) noprint;

table &Cat_Var.*y/out=freq_out_y0 (rename=(percent=percent0 count=count0));

run;



data Freq_Out(drop=y &Cat_Var.);

merge freq_out_y0 freq_out_y1;

by &Cat_Var.;

woe=log(percent0/percent1);

iv=(percent0-percent1)*woe;

run;



proc means data= Freq_Out SUM; run;



data Freq_Out;

length Variable_Name $32.;

set Freq_Out;

Variable_Name="&Cat_Var.";

run;



proc append base = Final data = Freq_Out force;run;



%Mend IV_Char;



%Macro All_Independent_Vars();



/* Numeric Variables */



proc sql noprint;

select count(*) into :nobs from data_chars (where=( name <> "y" and type=1));

quit;run;



data _null_;

set data_chars (where=( name <> "y" and type=1));



call symput(compress("Var"

_n_),name);

run;



%do i=1 %to &nobs.;

%IV_Numeric(&&Var&i..);

%end;



/* Char Variables */



proc sql noprint;

select count(*) into :nobs from data_chars (where=( name <> "y" and type=2));

quit;run;



data _null_;

set data_chars (where=( name <> "y" and type=2));

call symput(compress("Var"

_n_),name);

run;



%do i=1 %to &nobs.;

%IV_Char(test,&&Var&i..);

%end;



proc means data=final SUM;

class Variable_Name;

var iv;output out=final_iv(drop=_type_) sum= ;

run;



%Mend All_Independent_Vars;



proc contents data=test out=data_chars(keep=name type) noprint;

run;



%All_Independent_Vars;

Different types of correlation

http://www.fgse.nova.edu/edl/secure/stats/lesson6.htm


Variable X     Variable Y          Type of Correlation

Interval Interval Pearson product moment (r)

Ordinal Ordinal Spearman rank coefficient (rho or p)

Nominal Ordinal Rank biserial coefficient

Nominal Interval Point biserial

Nominal Nominal Phi coefficient