ODS EXCEL - sheet interval options

[Reeza]

Not sure if I'm missing something obvious here or if there's a bug in ODS EXCEL but this seems to generate an Excel file with two sheets for Asia and then one sheet for each other region.

*sorts your data to use BY logic;
proc sort data=sashelp.cars out=cars;
by origin;
run;



%macro create_report(origin = );
 
 
ods excel options(sheet_interval="NOW" sheet_name = "&origin.");
 
*displays data in Excel sheet;
proc print data=cars;
where origin = "&origin.";
run;

ods excel options(sheet_interval="NONE" sheet_name = "&origin.");

proc sgplot data=cars;
where origin = "&origin.";
scatter x=mpg_city y=mpg_highway / group = type;
run;

%mend;



*removes proc title and by line which are usually printed by default;
ods noptitle;
options nobyline;
options mprint;

*sets file options - notice use of #BYVAL1 in Sheet Name to control sheet names;
ods excel file='/home/fkhurshed/ODS_Example2.xlsx' style=meadow;
 

*create list of origins to run this for;
proc sql;
create table report_list_origins as
select distinct origin
from cars;
quit;

*call macro for each origin;
data _null_;
set report_list_origins;
	str = catt('%create_report(origin=', origin, ');');
	call execute(str);
run;


*closes file;
ods excel close;

Using SAS Academics On Demand, SAS 9.4M6

[PhilC]

I dont think its a bug.  ODS EXCEL is just weird.

*sorts your data to use BY logic;
proc sort data=sashelp.cars out=cars;
by origin;
run;

%macro create_report(origin = );

ods excel options( sheet_name = "&origin.");
 
*displays data in Excel sheet;
proc print data=cars;
where origin = "&origin.";
run;

proc sgplot data=cars;
where origin = "&origin.";
scatter x=mpg_city y=mpg_highway / group = type;
run;

%mend;



*removes proc title and by line which are usually printed by default;
ods noptitle;
options nobyline;
options mprint;

*sets file options - notice use of #BYVAL1 in Sheet Name to control sheet names;
ods excel file='ODS_Example2.xlsx' style=meadow options(sheet_interval="NONE");
 

*create list of origins to run this for;
proc sql;
create table report_list_origins as
select distinct origin
from cars;
quit;

*call macro for each origin;
data _null_;
set report_list_origins end=eof;
  length str $300;
	str = catt('%create_report(origin=', origin, ');');
  if eof=0
    then str=catt(str,'ods excel options(sheet_interval="NOW");');
  call execute(str);

run;


*closes file;
ods excel close;

[Reeza]

It seems like a bug to me, but I did find a workaround.
It almost seems like the options statement is being executed AFTER the procedure since the graph has a NONE right before it.

[Reeza]

And thanks for the solution!

[Reeza]

This seems to work, I moved the SHEET_INTERVAL=NOW to the end of the first set of code so it's just before the new set starts. I would have expected the code posted to work though. It almost seems like it's evaluating the ODS options statement AFTER the PROC for some reason, even though there's another options statement. 

*sorts your data to use BY logic;
proc sort data=sashelp.cars out=cars;
by origin;
run;



%macro create_report(origin = );
 
 
ods excel options( sheet_name = "&origin.");
 
*displays data in Excel sheet;
proc print data=cars;
where origin = "&origin.";
run;

ods excel options(sheet_interval="NONE" sheet_name = "&origin.");

proc sgplot data=cars;
where origin = "&origin.";
scatter x=mpg_city y=mpg_highway / group = type;
run;

ods excel options(sheet_interval="NOW" sheet_name = "&origin.");

%mend;



*removes proc title and by line which are usually printed by default;
ods noptitle;
options nobyline;
options mprint;

*sets file options - notice use of #BYVAL1 in Sheet Name to control sheet names;
ods excel file='/home/fkhurshed/ODS_Example2.xlsx' style=meadow options(Sheet_interval = "NONE");
 

*create list of origins to run this for;
proc sql;
create table report_list_origins as
select distinct origin
from cars;
quit;

*call macro for each origin;
data _null_;
set report_list_origins;
	str = catt('%create_report(origin=', origin, ');');
	call execute(str);
run;


*closes file;
ods excel close;

[PhilC]

IMO, The first sheet_interval "NOW" had to be resolved.  I believe one handles this problem like one programs the placing of commas in a string in between items of a list.

[PhilC]

I need to edit myself too much.