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
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
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 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.};
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
@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;
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;
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
Like as below,
I use 9.3 with other environment, maybe this can be the reason?
Thank you
Can you try to make a format on your own , not use PVALUE. ? proc format; value fmt low-0.001='<0.001' .......
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.