topic Re: ods tagsets.ExcelXP to export one table in SAS Programming

ods tagsets.ExcelXP to export one table

Ronein — Wed, 27 Nov 2019 06:19:46 GMT

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 ?*/

Re: ods tagsets.ExcelXP to export one table

andreas_lds — Wed, 27 Nov 2019 06:41:14 GMT

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;

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.

Re: ods tagsets.ExcelXP to export one table

SASKiwi — Wed, 27 Nov 2019 06:42:57 GMT

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

Re: ods tagsets.ExcelXP to export one table

Ronein — Wed, 27 Nov 2019 06:45:01 GMT

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);