How to - Get PROC CORR's Spearman Report Output

[turcay]

It didn't work 😞

[Cynthia_sas]

Hi:

  I get the green highlighting with this code (showing just the PROC REPORT section from the code you posted earlier here):

 
Ods Tagsets.ExcelXP file="c:\temp\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;
title 'use format on proc report for numeric variables';
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.};
define numeric1 / display Style={vjust=c fontweight=bold BackGround=CorrSecond.};
define numeric2 / display Style={vjust=c fontweight=bold BackGround=CorrSecond.};
define numeric3 / display Style={vjust=c fontweight=bold BackGround=CorrSecond.};
run;
Ods Tagsets.ExcelXP Close;

Notice where the STYLE= override for background is. See HTML and ExcelXP output below:

there are some other (minor issues) -- I think you need TAGATTR for the ExcelXP, but you already know how to do that.

cynthia

[turcay]

@Cynthia_sas,

The green colors are important for me, I think Tagattr just for decimals. In my previous code, why it cannot be working? what can be the reason? What about PValue format, it is so strange while it is working on my sample but not working on real data.

Do you have an idea about why it cannot be working in my data set?

Thank you

[Cynthia_sas]

Hi:

  This is the EXACT code I used, to get the green highlighting in the cells -- this is the same code which you posted previously-- I tried to highlight in RED the statements I added -- but it doesn't show up in the code box. Please carefully review the DEFINE statements that I added to your original code. I do NOT see anywhere in your original code with a PVALUE. format ??

cynthia

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 html;
Ods Tagsets.ExcelXP file="c:\temp\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;
title 'use format on proc report for numeric variables';
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.};
define numeric1 / display Style={vjust=c fontweight=bold BackGround=CorrSecond.};
define numeric2 / display Style={vjust=c fontweight=bold BackGround=CorrSecond.};
define numeric3 / display Style={vjust=c fontweight=bold BackGround=CorrSecond.};
run;
Ods Tagsets.ExcelXP Close;

when I make this change:

define numeric1 / display f=pvalue. Style={vjust=c fontweight=bold BackGround=CorrSecond.};
define numeric2 / display f=pvalue. Style={vjust=c fontweight=bold BackGround=CorrSecond.};
define numeric3 / display f=pvalue. Style={vjust=c fontweight=bold BackGround=CorrSecond.};

this is what I get (showing HTML and ExcelXP -- turns out that ExcelXP respects the f=pvalue. so that's good news):

cynthia

[turcay]

@Cynthia_sas,

I meant this code ->

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;

And this is the log from my real project->

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;

Can you observe any mistake in my  code?

Thank you

[Cynthia_sas]

Hi:
What I observe is not a mistake, so much as a misunderstanding of how macro works. This code:
def1= Define WOE_CMON_SCORE_PRD / Display Format=PValue. Style(Column)={BackGround=CorrSecond.} ;

comes from the %PUT.

And I see where you MAKE the %LET macro variable for def&j, but I don't see where you write out &&def&j to the code you are generating. Neither the %PUT nor the %LET is getting to the PROC REPORT code. I believe that you need something like &&def&j or %str(&&def&j) in your %DO loop.

cynthia

Notice that I don't have a %LET or a %PUT in my %DO loop. What I need written to my code is just the quoted string and I use &&student&i in the Macro program to cause ONLY the quoted string to be written to the input stack. I don't need a %PUT unless I am testing in the LOG and I don't need a %LET unless I want to "save" a value in a new macro variable.

[turcay]

@Cynthia_sas,

Exactly, you are totally right, after %Put statement I added &&def&j; then it worked. I'm sorry for tired you and @Ksharp out. 

If I have an one more question to ask you, I would like to ask that is it possible to perform the format only first rows of Numeric variables?

Current;

Desired;

Thank you

[turcay]

@Cynthia_sas and @Ksharp,

I realized something, some of the values of Numeric variables first row are coming as "<.0001" . Shouldn't them come as  "0" instead of ".<0001"

[Cynthia_sas]

Hi:
To only use traffic lighting on the FIRST row of an ORDER or GROUP variable, you have to move OUT of the DEFINE statement and put your traffic lighting in a CALL DEFINE statement.

The same applies to your question about the PVALUE format. If you only want the PVALUE to be used as the format for one row or another, you have to move the format out of the DEFINE statement and into a CALL DEFINE.

This is the type of thing that has been covered, I think in some of your previous postings. But, if you can't find them I think I have an example I can post.

cynthia

[Ksharp]

Oh. You need CALL DEFINE.


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;
title 'use format on proc report for numeric variables';
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.};
define numeric1 / display ;
define numeric2 / display ;
define numeric3 / display ;
compute numeric3;
 n+1;
 if mod(n,2)=0 then do;
  call define('numeric1','format','pvalue.');
  call define('numeric1','style','style={BackGround=CorrSecond.}');

  call define('numeric2','format','pvalue.');
  call define('numeric2','style','style={BackGround=CorrSecond.}');

  call define('numeric3','format','pvalue.');
  call define('numeric3','style','style={BackGround=CorrSecond.}');

 end;
endcomp;
run;
Ods Tagsets.ExcelXP Close;

[turcay]

@Ksharp,

Thank you very much for your help. Let's change my format with following format.

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

Then my desired output like this 

In @Cynthia_sas's sample there is a separatrix for "Sex" variable like "F" and "M" but in my data set both two rows have same values like "Numeric1" and "Numeric1" and etc.

You did some additional calculation here but I do not exactly understand. Can you help to reach my desired output?

 n+1;
 if mod(n,2)=0 then do;

 Thank you

[Ksharp]

OK. That means . Only EVEN number of obs are applied with format . Leave ODD obs be what they are .

[Ksharp]

n+1;
 if mod(n,2)=1 then do;

[turcay]

Hello @Ksharp and @Cynthia_sas,

I'm so close to complete my task, however, because of my code's structure the "N+1" statement probably multiplexing.For this reason the image of my result comes little bit strange.

%Macro MakeDefine;
%If &Count. GT 0 %Then %Do;
  %Do j = 1 %to &Count.;
    %Let def&j = Define %Scan(&ModelVar.,&j) / Display /*Format=PValue. Style={BackGround=CorrSec.}*/ %Str(;);
    %Put def&j= &&def&j;
     &&def&j;
%End;
%End;
%Else %Do;
  %Put ERROR: No value for ModelVar specified: &ModelVar;
%End;
%Mend  MakeDefine;
 
Ods Escapechar='~';
Options Missing=' ';
Proc Report Data=Spr2_&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.);
Define Variable /Order ' ' Style(Column)=Header{VJust=C FontWeight=Bold Color=#112277} ;
%MakeDefine;
%If &Count. GT 0 %Then %Do;
  %Do i = 1 %to &Count.;
   %Let Var&i = %Scan(&ModelVar.,&i,%str( ));
   %Put Var&i= &&Var&i;
Compute &&Var&i;
N+1;
If Mod(N,2)=1 Then Do;
  Call Define("&&Var&i","Format","PValue.");
  Call Define("&&Var&i","Style","Style={BackGround=CorrSec.}");
End;
EndComp;
%End;
%End;
 
Run;

The result of image as below;

As you can realise column's formats are different.

Thank you

[Ksharp]

If you have many compute block, you can use _col_  keyword.
I also notice you include N in many compute block, that would have some collision.
You only need one compute block to do these (As I showed in my code with the last variable).

Actually , you can make a dummy(last) variable , and put all the call define in it, just as I did .


column ..........  dummy ; <---- the last variable
.........
define dummy/ computed NOPRINT ;

compute dummy;
 /*Put all call define in it ,and don't make so many compute block*/

N+1;
If Mod(N,2)=1 Then Do;

%If &Count. GT 0 %Then %Do;
  %Do i = 1 %to &Count.;
   %Let Var&i = %Scan(&ModelVar.,&i,%str( ));

  Call Define("&&Var&i","Format","PValue.");
  Call Define("&&Var&i","Style","Style={BackGround=CorrSec.}");

%End;
End;


endcomp;