BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
turcay
Lapis Lazuli | Level 10

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

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
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

50 REPLIES 50
Reeza
Super User

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. 

turcay
Lapis Lazuli | Level 10

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

 

 

Reeza
Super User

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

turcay
Lapis Lazuli | Level 10

Thank you @Reeza ,

 

Okay, let me try to creare my desired output.

 

Thank you,

Cynthia_sas
SAS Super FREQ

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

turcay
Lapis Lazuli | Level 10

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,

Cynthia_sas
SAS Super FREQ

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

Ksharp
Super User

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

turcay
Lapis Lazuli | Level 10

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

Reeza
Super User

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. 

turcay
Lapis Lazuli | Level 10

@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

 

Reeza
Super User

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.

turcay
Lapis Lazuli | Level 10

@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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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