topic Re: ODS EXCEL - sheet interval options in ODS and Base Reporting

ODS EXCEL - sheet interval options

Reeza — Tue, 23 Mar 2021 19:44:29 GMT

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

Re: ODS EXCEL - sheet interval options

PhilC — Tue, 23 Mar 2021 20:07:08 GMT

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;

Re: ODS EXCEL - sheet interval options

Reeza — Tue, 23 Mar 2021 20:08:29 GMT

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;

Re: ODS EXCEL - sheet interval options

Reeza — Tue, 23 Mar 2021 20:09:51 GMT

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.

Re: ODS EXCEL - sheet interval options

Reeza — Tue, 23 Mar 2021 20:10:09 GMT

And thanks for the solution!

Re: ODS EXCEL - sheet interval options

PhilC — Tue, 23 Mar 2021 20:13:14 GMT

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.

Re: ODS EXCEL - sheet interval options

PhilC — Tue, 23 Mar 2021 20:13:56 GMT

I need to edit myself too much.