The SAS Output Delivery System and reporting techniques

Reg: Supressing display of proc corr output in excel file

Reply
N/A
Posts: 0

Reg: Supressing display of proc corr output in excel file

My main objective of the below code is i need to print sashelp.class twice in excel one below the other.and i dont want the proc corr output to be displayed in the same excel file.
But i need proc corr to create to datasets pearson and sstat respectively.
In order to not to display proc corr in the excel file I have used ODS _ALL_ CLOSE .But when i call the PLOT macro second time its is not printing sashelp.class into the same excel file as i have closed the ods destination. So kindly help me to over come this situation.I need to open ods html above proc print and need to close it after printing. if i open it using ods html; it is printing in a default excel file rather than printing it in sam.xls file.Or kindly suggest any other solution for this


%macro plot(a,b);
proc print data=sashelp.class;
run;
ods _all_ close;
ods output PearsonCorr = Pearson;
ods output SimpleStats = SStat;
proc corr data=sashelp.class;
var &a &b;
run;
proc append base=b data=pearson;run;
%mend;

ods html file="C:\Documents and Settings\Administrator\My Documents\Downloads\sam.xls";
%macro x;
%do i=1 %to 2;
%let a=height;
%let b=age;
%plot(&a,&b);
%end;
%mend;
%x;
ods html close;
SAS Super FREQ
Posts: 8,868

Re: Reg: Supressing display of proc corr output in excel file

Posted in reply to deleted_user
Hi:
The ODS SELECT and ODS EXCLUDE statements were designed for this purpose (to allow you to select or exclude which output objects from your procedures would "go forward" to the ODS destination file.

Before you code ODS SELECT or ODS EXCLUDE, you need to know the name of the output objects created by PROC CORR. This can be accomplished by using ODS TRACE ON/ODS TRACE OFF around a sample run of PROC CORR.

But, there's even an easier way to exclude the CORR output from the HTML file. That's to just use an:
[pre]
ODS HTML EXCLUDE ALL;
ODS HTML SELECT ALL;

OR

ODS <destination> EXCLUDE ALL;
ODS <destination> SELECT ALL;
[/pre]

..."sandwich" around your PROC CORR step. As long as the LISTING destination is open, PROC CORR will still run and create the output datasets and you'll only get the 2 proc prints in the HTML file.

Remember that you are not creating a true, binay Excel file. You are using ODS HTML to create an HTML file that Excel knows how to open and render. You should experiment with these 3 ODS destinations:
ODS HTML3
ODS HTML
ODS MSOFFICE2K

They create HTML 3.2 compliant tags, HTML 4.0 compliant tags and Microsoft-specific HTML tags respectively. You may find that Excel respects the style in the HTML3 and MSOFFICE2K HTML files better than it respects the style in the ODS HTML (HTML 4) file. For example, compare the 3 outputs below to see how each output file's use of the SASWEB style is treated by Excel when the HTML file is opened.

cynthia
[pre]
%macro plot(a,b);
proc print data=sashelp.class;
run;

ods listing;
ods html exclude all;
ods html3 exclude all;
ods msoffice2k exclude all;

ods output PearsonCorr = Pearson;
ods output SimpleStats = SStat;
proc corr data=sashelp.class;
var &a &b;
run;

ods html select all;
ods html3 select all;
ods msoffice2k select all;

ods listing close;

proc append base=b data=pearson;run;
%mend;

%macro x;
%do i=1 %to 2;
%let a=height;
%let b=age;
%plot(&a,&b);
%end;
%mend;

ods html file="C:\temp\sam_ht4.xls" style=sasweb;
ods html3 file="c:\temp\sam_ht3.xls" style=sasweb;
ods msoffice2k file="c:\temp\sam_mso.xls" style=sasweb;

%x;

ods _all_ close;

[/pre]
N/A
Posts: 0

Re: Reg: Supressing display of proc corr output in excel file

Posted in reply to Cynthia_sas
Thank you very much for your quick response.

Regards
Samuel.
Ask a Question
Discussion stats
  • 2 replies
  • 334 views
  • 0 likes
  • 2 in conversation