The SAS Output Delivery System and reporting techniques

How to - Get PROC CORR's Spearman Report Output

Accepted Solution Solved
Reply
Super Contributor
Posts: 381
Accepted Solution

How to - Get PROC CORR's Spearman Report Output

[ Edited ]

Hello everyone,

 

I try to create report out by using Tagset.ExcelXP. I need PROC CORR’s Spearman report. The following code helps me to create Spearman report in my Tagset.ExcelXP report.However, I want to see the report as a shape of SAS report. If you glance the following image, you can observe the differences between SAS Report and Tagset.ExcelXP report. SAS report has more rows, actually, I also want to create same report(like SAS Report) in my Tagset.ExcelXP report.

 

For me, the best way out, get the SAS Report being a data set because I will also change the header of my report or header of the variables. “Ods Output SpearmanCorr=Spr;” the foregoing option provides me to create Spearman report being a dataset but not my desired shape.

 

As a summary, my purpose is that get the spearman report out in a similar way with SAS report and change the headers&variables(label of variable) of the report.

 

Can anyone help me about my foregoing situation, please?

 

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 Tagsets.ExcelXP Path="C:\Temp" file="Spearman.xml"
style=htmlblue options(zoom="80" embedded_titles='yes' sheet_interval='none' sheet_name="Spearman");
Ods Tagsets.ExcelXP Select  SpearmanCorr;
Ods Output SpearmanCorr=Spr;
Proc Corr Spearman
Data=Have;
Var
Numeric1
Numeric2
Numeric3
;
Run;
Ods Output Close;
Ods Tagsets.ExcelXP Select  SpearmanCorr;
Ods Output SpearmanCorr=Spr2;
Proc Corr Spearman
Data=Have;
Var
WOE1
WOE2
WOE3
;
Run;
Ods Output Close;
 
Ods Tagsets.ExcelXP Close;

Reports.png

 

Thank you


Accepted Solutions
Solution
‎08-03-2016 04:31 AM
Grand Advisor
Posts: 9,571

Re: How to - Get PROC CORR's Spearman Report Output

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	 



View solution in original post


All Replies
Grand Advisor
Posts: 17,320

Re: How to - Get PROC CORR's Spearman Report Output

Your second output is from a copy paste, correct? I don't think that's how it shows in SAS by default either. 

 

You can try changing the style but my guess is your likely going to have to create the table and use proc report to get exactly what you want. I usually recommend Journal or meadow as a style. 

Super Contributor
Posts: 381

Re: How to - Get PROC CORR's Spearman Report Output

Thank you for response @Reeza,

 

I hope I made myself clear.

 

Yes, my second output copy paste.

 

So first, I need to use this option -> "Ods Output SpearmanCorr=Spr;” then I will try to prepare my desired output to show my desired report?

 

Thank you

 

 

Grand Advisor
Posts: 17,320

Re: How to - Get PROC CORR's Spearman Report Output

Yes

Grand Advisor
Posts: 17,320

Re: How to - Get PROC CORR's Spearman Report Output

You could try to modify the template as well but that's usually a lot more work IMO

Super Contributor
Posts: 381

Re: How to - Get PROC CORR's Spearman Report Output

Thank you @Reeza ,

 

Okay, let me try to creare my desired output.

 

Thank you,

SAS Super FREQ
Posts: 8,717

Re: How to - Get PROC CORR's Spearman Report Output

Hi:

  I am not sure exactly, what you mean by SASReport compared to TAGSETS.EXCELXP. But when I run your code, this is exactly what I see in Enterprise Guide using SASReport as the only output type:

eg_spearman.png

 

And here is the TAGSETS.EXCELXP:

xp_spearman.png

 

Here is the HTML:

html_spearman.png

 

Here is the RTF:

rtf_spearman.png

 

Here is the PDF:

pdf_spearman.png

 

When I look at the 2 outputs side by side, next to each other in this screen shot:

compare.png

 

I do not see many differences except for the titles and procedure titles, which could have been easily fixed. I ran the EG report on a Unix system and I ran the TAGSETS.EXCELXP report on my Windows System, so there were different titles and proctitles in effect.

 

Can you explain exactly what it is about the "SASReport" output versus the ExcelXP output that you want to change????

 

cynthia

Super Contributor
Posts: 381

Re: How to - Get PROC CORR's Spearman Report Output

[ Edited ]

Hello @Cynthia_sas,

 

The differences is the row numbers. I added an image which shows the different row numbers between Tagset.ExcelXP and SAS Report.

 

On the other hand, I want to change the headers, I mean this one -> "Spearman Correlation Coefficients, N = 8" and this one "Prob > |r| under H0: Rho=0".

 

I hope I could make myself clear.

 

DifferencesIsRowNumber.png

 

Thank you,

SAS Super FREQ
Posts: 8,717

Re: How to - Get PROC CORR's Spearman Report Output

[ Edited ]

Hi:
You cannot compare the row numbers in output that you have copied and pasted with the row numbers that you get in ExcelXP. If you notice in the ExcelXP output, for example, you can find that row 1, is wrapping, so it looks like it is taking up 2 rows, but it is really row 1, wrapped.

The SASReport output also shows interior table lines that do not appear in the TAGSETS.EXCELXP output.

If you want to change the header your options are to collect the output object information and use PROC PRINT or PROC REPORT or to alter the TABLE template for PROC CORR.

Are you saying that you want the TAGSETS.EXCELXP output to match "exactly" what you get in Enterprise Guide -- when it is in Enterprise Guide? You cannot expect SAS to create output that looks as if it has been copied and pasted. Also I don't understand how want to change the headers -- the text on the headers looks the same -- only the line breaks and spacing look different.

cynthia

Grand Advisor
Posts: 9,571

Re: How to - Get PROC CORR's Spearman Report Output

You can get it by hand.

 

 

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;

x.png

Super Contributor
Posts: 381

Re: How to - Get PROC CORR's Spearman Report Output

Thank you very much to all of you,

 

I just want to one additional questions. 

 

Ods Tagsets.ExcelXP Select  SpearmanCorr;

 This option sometimes brings the reports with N values but sometimes it doesn't bring. What is the reason for that? And is it possible to prevent to bring N values?

 

Thank you

Grand Advisor
Posts: 17,320

Re: How to - Get PROC CORR's Spearman Report Output

It depends on if you have it in the correct place. Your code should look like the following:

 

ods select spearmancorr;
proc corr data=...


run;

You don't need the tagsets portion in there, though it's good. I'm surprised you'd get other output, if you are, you need to post some data than can replicate this issue. 

Super Contributor
Posts: 381

Re: How to - Get PROC CORR's Spearman Report Output

@Reeza@Ksharp,  @Cynthia_sas ,

 

Hello again,

 

About my "Number of Obsvervation" question. Actually, I didn't gain a clear understanding. Even though, I wrote the "Ods Tagsets.ExcelXP Select SpearmanCorr" statement for both different large datasets, one of them brings number of observations the other one doesn't bring number of observation.

 

I added an image. Is it possible for you to explain the reason or is it possible to prevent to print "number of obsverations" for both data sets?

 

Your answers will be appreciated.

 

Thank you

Que.png

 

Grand Advisor
Posts: 17,320

Re: How to - Get PROC CORR's Spearman Report Output

Your screen shot isn't clear enough to see what you're referring to.

 

And you didn't include your code so hard to say there either.

Super Contributor
Posts: 381

Re: How to - Get PROC CORR's Spearman Report Output

@Ksharp @Cynthia_sas @Reeza,

 

To make clear, at the following link, you can examine the first example, it prints the following image. As you can see, there are Number of Observation rows for every variable, I just don't want to bring this values.

 

I only want to bring firts two rows for each variables.

 

Is it possible, is there an option for this? 

 

http://support.sas.com/documentation/cdl/en/procstat/63963/HTML/default/viewer.htm#procstat_corr_sec...

 

Corr.png

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 50 replies
  • 728 views
  • 5 likes
  • 4 in conversation