How to - Get PROC CORR's Spearman Report Output

[Cynthia_sas]

If you do NOT like the output from PROC CORR, then you must save the object into a SAS dataset and then print only the rows you want from the saved dataset. Either that or investigate whether PROC CORR has an option to turn off the Number of observations.

cynthia

[Cynthia_sas]

Hi: Every output object has different titles and different columns. So without data, it is hard to see what your issue is with what you're pointing to with your arrows. I see the sub-title "Number of Observations" but I'm not sure what the problem is.

cynthia

[turcay]

Hi,

So I need to use @Ksharp's method to achieve my aim as below,

Data Have;
Length Target 8 Numeric1 8 Numeric2 8 Numeric3 8 WOE1 8 WOE2 8 WOE3 8;
Infile Datalines Missover;
Input Target Numeric1 Numeric2 Numeric3 WOE1 WOE2 WOE3;
Datalines;
0 0.2 0.4 0.1 0.0 0.1 0.3
1 0.3 0.5 0.2 0.2 0.4 0.7
0 0.4 0.8 0.9 0.1 0.9 0.3
1 0.6 0.9 0.0 0.2 0.2 0.6
1 0.2 0.1 0.4 0.3 0.5 0.3
0 0.3 0.1 0.3 0.2 0.1 0.2
1 0.7 0.1 0.1 0.4 0.7 0.9
1 0.1 0.8 0.3 0.3 0.3 0.5
;
Run;

Ods Select  SpearmanCorr;
Ods Output SpearmanCorr=Spr;
Proc Corr Spearman
Data=Have;
Var
Numeric1
Numeric2
Numeric3
;
Run;


data want(drop=PNumeric1 PNumeric2 PNumeric3);
 set spr;
 output;
 Numeric1=PNumeric1;
 Numeric2=PNumeric2;
 Numeric3=PNumeric3;
 output;
run;



%let dsid=%sysfunc(open(have));
%let nobs=%sysfunc(attrn(&dsid,nlobs));
%let dsid=%sysfunc(close(&dsid));

Ods Tagsets.ExcelXP file="/folders/myfolders/Spearman.xml"
style=htmlblue  options(zoom="80" embedded_titles='yes' sheet_interval='none' sheet_name="Spearman");
ods escapechar='~';
options missing=' ';
proc report data=want nowd spanrows;
column ("Spearman Correlation Coefficients, N = &nobs
 ~n   Prob > |r| under H0: Rho=0" Variable  Numeric1 Numeric2 Numeric3);
define Variable/order ' ' style={vjust=c fontweight=bold};
run;
Ods Tagsets.ExcelXP Close;

As far as I understand there is no option for my question

Thank you

[Cynthia_sas]

Hi:

  I don't know. I guess I still don't understand what you want. When I run this code, only this:

Data Have;
Length Target 8 Numeric1 8 Numeric2 8 Numeric3 8 WOE1 8 WOE2 8 WOE3 8;
Infile Datalines Missover;
Input Target Numeric1 Numeric2 Numeric3 WOE1 WOE2 WOE3;
Datalines;
0 0.2 0.4 0.1 0.0 0.1 0.3
1 0.3 0.5 0.2 0.2 0.4 0.7
0 0.4 0.8 0.9 0.1 0.9 0.3
1 0.6 0.9 0.0 0.2 0.2 0.6
1 0.2 0.1 0.4 0.3 0.5 0.3
0 0.3 0.1 0.3 0.2 0.1 0.2
1 0.7 0.1 0.1 0.4 0.7 0.9
1 0.1 0.8 0.3 0.3 0.3 0.5
;
Run;

Ods Select  SpearmanCorr;

Proc Corr Spearman Data=Have;
Var Numeric1 Numeric2 Numeric3;
Run;

I do NOT see number of observations in the Spearman output as shown below:

So what is it about the default output from PROC CORR that is unacceptable to you? I know you want the output in Excel, but I still am not clear on what about the ExcelXP output is not to your satisfaction. This is what I get when I send the above output to Excel using TAGSETS.EXCELXP:

And except for the vertical justification of the Numeric1, Numeric2 and Numeric3 I am not sure what you are aiming for.

cynthia

[turcay]

Hello @Ksharp,

I followed your method and it seems fine. However, If I change the data set values, there are some differences in appearance of result.

For exampe, default output of Spearman, it shown as "<.0001" but in your code's output it shown "0.0000".

Proc Format;
  Value CorrSecond  
              Low-<0.7 = "CX00B050";
Run;
 
Data Have;
Length Target 8 Numeric1 8 Numeric2 8 Numeric3 8 WOE1 8 WOE2 8 WOE3 8;
Infile Datalines Missover;
Input Target Numeric1 Numeric2 Numeric3 WOE1 WOE2 WOE3;
Datalines;
0 0.01 0.01 0.01 0.0 0.1 0.3
1 0.01 0.01 0.01 0.2 0.4 0.7
0 0.01 0.01 0.01 0.1 0.9 0.3
1 0.01 0.01 0.01 0.2 0.2 0.6
1 0.01 0.01 0.01 0.3 0.5 0.3
0 0.01 0.01 0.01 0.2 0.1 0.2
1 0.01 0.01 0.01 0.4 0.7 0.9
1 0.1 0.1 0.1 0.3 0.3 0.5
;
Run;
Ods Select  SpearmanCorr;
Ods Output SpearmanCorr=Spr;
Proc Corr Spearman
Data=Have;
Var
Numeric1
Numeric2
Numeric3
;
Run;
data want(drop=PNumeric1 PNumeric2 PNumeric3);
set spr;
output;
Numeric1=PNumeric1;
Numeric2=PNumeric2;
Numeric3=PNumeric3;
output;
run;
  
%let dsid=%sysfunc(open(have));
%let nobs=%sysfunc(attrn(&dsid,nlobs));
%let dsid=%sysfunc(close(&dsid));
Ods Tagsets.ExcelXP file="/folders/myfolders/Spearman.xml"
style=htmlblue  options(zoom="80" embedded_titles='yes' sheet_interval='none' sheet_name="Spearman");
ods escapechar='~';
options missing=' ';
proc report data=want nowd spanrows;
column ("Spearman Correlation Coefficients, N = &nobs
~n   Prob > |r| under H0: Rho=0" Variable  Numeric1 Numeric2 Numeric3);
define Variable/order ' ' Style={vjust=c fontweight=bold BackGround=CorrSecond.};
run;
Ods Tagsets.ExcelXP Close;
 

Difference;

I also want to give format in my output of report. If any cell of report is less than 0.7 then it will display as green. Here is the desired image;

Can somebody help me, please?

Thank you

[Cynthia_sas]

Hi:
To change the background color of the output from PROC CORR you have 2 choices:
1) change the TABLE template used for the Spearman Correlation Coefficients (harder)
OR
2) save the output from PROC CORR to a dataset and then use either PROC PRINT, PROC REPORT or the Report Writing Interface to create your report from the dataset.

cynthia

[turcay]

@Cynthia_sas Thank you,

But could you give more detailed information, please? As far as I understand, the first method is difficult to do. I could not understand your second recommendation.

I tried to add following formats in the output of PROC CORR but it doesn't work. The Data Set "Want" is the output of PROC CORR but why it doesn't work,  I just don't understand. Can you lead me, please?

Proc Format;
  Value CorrSecond  
              Low-<0.7 = "CX00B050";
Run;

 Style={vjust=c fontweight=bold BackGround=CorrSecond.};

[Cynthia_sas]

Hi,
I would think that you would be using CorrSecond format on the DEFINE statement for the numeric variables (NUMERIC1, NUMERIC2, NUMERIC3). However, when I look at the code you've posted, I see this:
define Variable/order ' ' Style={vjust=c fontweight=bold BackGround=CorrSecond.};

Variable is a Character string that is used to create the first column on the report. So the value of the VARIABLE column will NEVER be a number, correct? The value of VARIABLE will always be a string used as an identifier on the left-most column of the report. When I use the style override on DEFINE statements for Numeric1, Numeric2 and Numeric3, then I do get the green background.

cynthia

[Ksharp · Accepted solution]

Oh. You need apply PVALUE. format .




Proc Format;
  Value CorrSecond  
              Low-<0.7 = "CX00B050";
Run;
 
Data Have;
Length Target 8 Numeric1 8 Numeric2 8 Numeric3 8 WOE1 8 WOE2 8 WOE3 8;
Infile Datalines Missover;
Input Target Numeric1 Numeric2 Numeric3 WOE1 WOE2 WOE3;
Datalines;
0 0.01 0.01 0.01 0.0 0.1 0.3
1 0.01 0.01 0.01 0.2 0.4 0.7
0 0.01 0.01 0.01 0.1 0.9 0.3
1 0.01 0.01 0.01 0.2 0.2 0.6
1 0.01 0.01 0.01 0.3 0.5 0.3
0 0.01 0.01 0.01 0.2 0.1 0.2
1 0.01 0.01 0.01 0.4 0.7 0.9
1 0.1 0.1 0.1 0.3 0.3 0.5
;
Run;
Ods Select  SpearmanCorr;
Ods Output SpearmanCorr=Spr;
Proc Corr Spearman
Data=Have;
Var
Numeric1
Numeric2
Numeric3
;
Run;
data want(drop=PNumeric1 PNumeric2 PNumeric3);
set spr;
output;
Numeric1=PNumeric1;
Numeric2=PNumeric2;
Numeric3=PNumeric3;
output;
run;
  
%let dsid=%sysfunc(open(have));
%let nobs=%sysfunc(attrn(&dsid,nlobs));
%let dsid=%sysfunc(close(&dsid));
Ods Tagsets.ExcelXP file="/folders/myfolders/Spearman.xml"
style=htmlblue  options(zoom="80" embedded_titles='yes' sheet_interval='none' sheet_name="Spearman");
ods escapechar='~';
options missing=' ';
proc report data=want nowd spanrows;
column ("Spearman Correlation Coefficients, N = &nobs
~n   Prob > |r| under H0: Rho=0" Variable  Numeric1 Numeric2 Numeric3);
define Variable/order ' ' Style={vjust=c just=c fontweight=bold BackGround=CorrSecond.};
define Numeric1 /format=pvalue.;
define Numeric2 /format=pvalue.;
define Numeric3 /format=pvalue.;
run;
Ods Tagsets.ExcelXP Close;
 





OUTPUT:

Spearman 相关系数, N = 8
Prob > |r| under H0: Rho=0
 	Numeric1	Numeric2	Numeric3
Numeric1	
1.00000
 
1.00000
<.0001
1.00000
<.0001
Numeric2	
1.00000
<.0001
1.00000
 
1.00000
<.0001
Numeric3	
1.00000
<.0001
1.00000
<.0001
1.00000
 
Spearman Correlation Coefficients, N = 8
Prob > |r| under H0: Rho=0
 	Numeric1	Numeric2	Numeric3
Numeric1	1.0000	1.0000	1.0000
 	<.0001	<.0001
Numeric2	1.0000	1.0000	1.0000
<.0001	 	<.0001
Numeric3	1.0000	1.0000	1.0000
<.0001	<.0001	 

[turcay]

@Cynthia_sas and @Ksharp,

Sorry Cynthia, you are right. Variable is string variable, I need to give the format Numeric1, Numeric2 and Numeric3 variables which have numeric type. However, according to your and Xia's suggestions, I tried to do following code but it doesn't give my desired output.Nothing changes of display of the output. Am I missing something?

Can you help me, please?

Proc Format;
  Value CorrSecond  
              Low-<0.7 = "CX00B050";
Run;
 
%Macro MakeDefine;
%If &Count. GT 0 %Then %Do;
  %Do j = 1 %to &Count.;
    %Let def&j = Define %Scan(&ModelVar.,&j) / Display Format=PValue. Style(Column)={BackGround=CorrSecond.} %Str(;);
    %Put def&j= &&def&j;
%End;
%End;
%Else %Do;
  %Put ERROR: No value for ModelVar specified: &ModelVar;
%End;
%Mend  MakeDefine;
 
%Macro;
 
Ods Escapechar='~';
Options Missing=' ';
Proc Report Data=SprRaw2_&Suffix. Nowd SpanRows Style(Column)=[BorderColor=Black BorderWidth=1px]
Style(Header)=Header [BorderColor=Black BorderWidth=1px];
Column ("Spearman Correlation Coefficients, N = &Nobs
~n  Prob > |r| under H0: Rho=0" Variable &ModelVar_Raw.);
Define Variable /Order ' ' Style(Column)=Header {VJust=C /*BackGround=CorrSecond.*/ FontWeight=Bold Color=#112277};
%MakeDefine;
Run;
 
%Mend;

[Ksharp]

If that would not work, the one way I can think is transform it into STRING .






Proc Format;
  Value CorrSecond  
              Low-<0.7 = "CX00B050";
Run;
 
Data Have;
Length Target 8 Numeric1 8 Numeric2 8 Numeric3 8 WOE1 8 WOE2 8 WOE3 8;
Infile Datalines Missover;
Input Target Numeric1 Numeric2 Numeric3 WOE1 WOE2 WOE3;
Datalines;
0 0.01 0.01 0.01 0.0 0.1 0.3
1 0.01 0.01 0.01 0.2 0.4 0.7
0 0.01 0.01 0.01 0.1 0.9 0.3
1 0.01 0.01 0.01 0.2 0.2 0.6
1 0.01 0.01 0.01 0.3 0.5 0.3
0 0.01 0.01 0.01 0.2 0.1 0.2
1 0.01 0.01 0.01 0.4 0.7 0.9
1 0.1 0.1 0.1 0.3 0.3 0.5
;
Run;
Ods Select  SpearmanCorr;
Ods Output SpearmanCorr=Spr;
Proc Corr Spearman
Data=Have;
Var
Numeric1
Numeric2
Numeric3
;
Run;
data want(drop=Numeric1-Numeric3 PNumeric1 PNumeric2 PNumeric3);
set spr;
length CNumeric1-CNumeric3 $ 10;
CNumeric1='09'x||vvalue(Numeric1);
CNumeric2='09'x||vvalue(Numeric2);
CNumeric3='09'x||vvalue(Numeric3);
output;
CNumeric1=vvalue(PNumeric1);
CNumeric2=vvalue(PNumeric2);
CNumeric3=vvalue(PNumeric3);
output;
run;
  
%let dsid=%sysfunc(open(have));
%let nobs=%sysfunc(attrn(&dsid,nlobs));
%let dsid=%sysfunc(close(&dsid));
Ods Tagsets.ExcelXP file="/folders/myfolders/Spearman.xml"
style=htmlblue  options(zoom="80" embedded_titles='yes' sheet_interval='none' sheet_name="Spearman");
ods escapechar='~';
options missing=' ';
proc report data=want nowd spanrows;
column ("Spearman Correlation Coefficients, N = &nobs
~n   Prob > |r| under H0: Rho=0" Variable  CNumeric:);
define Variable/order ' ' Style={vjust=c just=c fontweight=bold BackGround=CorrSecond.};
run;
Ods Tagsets.ExcelXP Close;
 

[turcay]

@Ksharp,

Actually, your previous code also seems correct. It works on my sample.however, it doesn't work in my real data.

Here is the log of my code. am I doing something wrong? Do you have an idea?

MPRINT(REPORTING_CORRELATION):   Title ;
MPRINT(REPORTING_CORRELATION):   Ods Escapechar='~';
MPRINT(REPORTING_CORRELATION):   Options Missing=' ';
MPRINT(REPORTING_CORRELATION):   Proc Report Data=Spr2_MDL Nowd SpanRows Style(Column)=[BorderColor=Black BorderWidth=1px] 
Style(Header)=Header [BorderColor=Black BorderWidth=1px];
MPRINT(REPORTING_CORRELATION):   Column ("Spearman Correlation Coefficients, N = 2813~n  Prob > |r| under H0: Rho=0" Variable 
WOE_CMON_SCORE_PRD WOE_MEM_BANK_COUNT WOE_MEM_TOT_CASH_UTI WOE_MEM_TOT_NONCASH_UTI WOE_MEM_YKB_CASH_UTI WOE_MONTHS_CIF_OPEN 
WOE_NEGINFO_CT_2YR WOE_NET_SHARHOLDR_EQUITY WOE_ROA);
MPRINT(REPORTING_CORRELATION):   Define Variable /Order ' ' Style(Column)=Header{VJust=C FontWeight=Bold Color=#112277} ;
def1= Define WOE_CMON_SCORE_PRD / Display Format=PValue. Style(Column)={BackGround=CorrSecond.} ;
def2= Define WOE_MEM_BANK_COUNT / Display Format=PValue. Style(Column)={BackGround=CorrSecond.} ;
def3= Define WOE_MEM_TOT_CASH_UTI / Display Format=PValue. Style(Column)={BackGround=CorrSecond.} ;
def4= Define WOE_MEM_TOT_NONCASH_UTI / Display Format=PValue. Style(Column)={BackGround=CorrSecond.} ;
def5= Define WOE_MEM_YKB_CASH_UTI / Display Format=PValue. Style(Column)={BackGround=CorrSecond.} ;
def6= Define WOE_MONTHS_CIF_OPEN / Display Format=PValue. Style(Column)={BackGround=CorrSecond.} ;
def7= Define WOE_NEGINFO_CT_2YR / Display Format=PValue. Style(Column)={BackGround=CorrSecond.} ;
def8= Define WOE_NET_SHARHOLDR_EQUITY / Display Format=PValue. Style(Column)={BackGround=CorrSecond.} ;
def9= Define WOE_ROA / Display Format=PValue. Style(Column)={BackGround=CorrSecond.} ;
MPRINT(REPORTING_CORRELATION):  ;
MPRINT(REPORTING_CORRELATION):   Run;

Thank you

[Ksharp]

I didn't see anything wrong in your code . What wrong output you got ?

[turcay]

Like as below,

I use 9.3 with other environment, maybe this can be the reason?

Thank you

[Ksharp]

Can you try to make a format on your own , not use PVALUE. ?


proc format;
value fmt
 low-0.001='<0.001'
.......