ods tagsets.ExcelXP to export one table

[Ronein]

Hello

I am using ods tagsets.ExcelXP   to export one table to one XLS file.

I see that after I run it there is print  on my screen in "Results-SAS Report" window.

How can I prevent the print?

The reason is that if the table is very big then it might cause problem to print on screen such a big table.

I only want to export it without print it.

I know that proc export just export it without print it but I also want to know how to do it via ods tagsets.ExcelXP.

%macro ExportOneTbl(ds,path,FileName);
ods listing close;
ods tagsets.ExcelXP  
file= "&FileName"  
path= &path;
proc print data=&ds  noobs; 
run; ods tagsets.ExcelXP close; 
ods listing;
%mend ExportOneTbl;


%ExportOneTbl(ds=sashelp.cars,                     
              path="/My path/",
              FileName=cars1.xls);

/*Ask:How to prevent print on screen*/
/*Ask:How to control sheet name?*/
/*Ask:Here we export to xls file. Can we also export to csv/xlsx/xml files*/
/*Is it better to use proc export or  ods tagsets.ExcelXP ? */
/*Can we control formats  with ods tagsets.ExcelXP ?*/

[andreas_lds · Accepted solution]

Which client is used? Enterprise Guide? SAS Studio? In Enterprise Guide, you can deactivate the default output of programs via their properties. Or try

ods _all_ close;

or

ods <destination_name> exclude  all;

before you proc print. And afterwards

ods <destination_name> exclude none;

The destination name depends on the client you are using. You can query sashelp.vdest to get the names of the open destinations:

proc print data=sashelp.vdest;
run;

You could also create a dynamic exclude by querying the view and creating exclude statements.

[SASKiwi]

Use ODS _ALL_ CLOSE instead of ODS LISTING CLOSE: https://documentation.sas.com/?docsetId=odsug&docsetTarget=n1sqlptpa8rdtsn1g45nrh7yoff6.htm&docsetVe...

[Ronein]

Great .

ods _all_ close worked well.

There is still window called "Results-SAS report" but the print includes no data.

%macro ExportOneTbl(ds,path,FileName);
ods _all_ close;/*To prevent print on screen*/
ods listing close;
ods tagsets.ExcelXP  
file= "&FileName"  
path= &path;
proc print data=&ds  noobs; 
run; 
ods tagsets.ExcelXP close; 
ods listing;
%mend ExportOneTbl;
%ExportOneTbl(ds=sashelp.cars,
              path="/my path/",
              FileName=cars1.xls);