topic Re: Hiding by variable from proc report output in ODS and Base Reporting

Hiding by variable from proc report output

js5 — Thu, 15 Aug 2019 11:22:36 GMT

Dear community,

I have created a fake by variable in order to be able to name sheets using #byval1 in ods excel output while using multiple proc report calls.

ods excel file="foo.xlsx" options(sheet_name="#byval1");

proc report data=foo;
by dsname;
quit;

proc report data=bar;
by dsname;
quit;

ods excel close;

While it worked in terms of giving the sheets human-readable names, I now have dsname=foo present as subtitle in the sheets. As each dataset reported on has just one value of the dsname variable, this carries no information and I would like to remove it. Is there a parameter to proc report which could accomplish the job?

Alternatively, is there a way to dynamically name the sheets of ods excel other than using #byval? Thank you!

Re: Hiding by variable from proc report output

jimhorne — Thu, 15 Aug 2019 11:29:16 GMT

Have you tried OPTIONS NOBYLINE?

Re: Hiding by variable from proc report output

js5 — Thu, 15 Aug 2019 11:38:28 GMT

That is exactly what I needed, thanks!
Is there a less crude way of naming the sheets dynamically when using multiple proc report calls? Adding a fake variable containing the dataset name and using by with single value seems a bit crude.

Re: Hiding by variable from proc report output

ballardw — Fri, 16 Aug 2019 15:22:44 GMT

@js5 wrote:
That is exactly what I needed, thanks!
Is there a less crude way of naming the sheets dynamically when using multiple proc report calls? Adding a fake variable containing the dataset name and using by with single value seems a bit crude.

It depends on what you mean by "dynamically". And to some extent the exact nature of your proc report calls.

If your Proc Report code is not being generated dynamically then it may just be easier to add an

ods excel options(sheet_name="dataset");

before each proc report changing the text of "dataset" as desired. I typically do this because SAS dataset names aren't necessarily "pretty". So I could use sheet_name="Child Age" instead of sheet_name="childage".

If you have some macro that generates the proc report code from a parameter list that includes a dataset name then you could dynamically add that ODS Excel option before the proc report code as part of the macro such as:

%macro genericreport(libname=work,dsname=_last_);
ods excel options(sheet_name="&dsname.");
proc report data=&libname..&dsname.;
run;
%mend;


ods excel file="foo.xlsx" ;

%genericreport(dsname=foo);
%genericreport(dsname=bar);

ods excel close;

If you have any more options to the Proc Report code though it would likely be easier just to add the separate ODS EXcel options as needed since you are already typing out the proc report. The extremely lazy could create very short macro to do just that line with the desired text.

Re: Hiding by variable from proc report output

js5 — Wed, 28 Aug 2019 11:17:42 GMT

Thanks, this did the trick! I did not know that you can call ods excel multiple times in addition to ods excel file... and ods excel close.