topic ODS EXCEL : Add two blank sheets in SAS Programming

ODS EXCEL : Add two blank sheets

elsfy — Fri, 10 Jun 2022 14:48:13 GMT

Hi,

Do you know if it's possible to add two blank sheets before exporting SAS outputs ?

I found that it is possible to add one before and after a sheet in which there is a SAS table, or one between two different sheets containing SAS outputs. I want to know if it is possible to add two blank sheets consecutively.

I did this


%macro reporting(...) ; 
proc report data = ...  ; 
run ; 
%mend ;
 ods excel file = "" 
options (sheet_interval="none" blank_sheet="INFORMATION") ; 
 ods excel options(sheet_name="A" blank_sheet="INFORMATION_A") ; 
%reporting(...) ; 
...

Yet it only outputs two sheets : "INFORMATION_A" and "A" while i want 3 sheets in this order : INFORMATION, INFORMATION_A, A

Thanks

Re: ODS EXCEL : Add two blank sheets

JOL — Fri, 10 Jun 2022 18:10:14 GMT

It appears only one blank sheet can be added before your data sheet, but here's a work around you can try, add an extra ods options statement.

ods excel file="c:\temp\file.xlsx"
options (sheet_name="A" blank_sheet="Information_A");

proc print data = sashelp.class;
run;

ods excel options (blank_sheet="Information");
ods excel close;

Re: ODS EXCEL : Add two blank sheets

Ksharp — Sat, 11 Jun 2022 06:32:33 GMT

Try this one :

ods excel file="c:\temp\file.xlsx" options (sheet_name="A" );

proc print data = sashelp.class;
run;

ods excel options (sheet_name="BlankSheet1");
proc sql;
select name label='#' from sashelp.class(obs=0);
quit;

ods excel options (sheet_name="BlankSheet2");
proc sql;
select name label='#' from sashelp.class(obs=0);
quit;
ods excel close;