topic Re: ODS excel and blank tabs in ODS and Base Reporting

ODS excel and blank tabs

Doug____ — Wed, 17 Nov 2021 20:45:22 GMT

When using PROC REPORT to perform a transposition, the procedure produces a blank tab in the workbook, which is not desired as all define statements are set to NOPRINT as shown below. Since I prefer to to use PROC REPORT for this and not PROC TRANSPOSE, is there a way to suppress this behavior? As it is each time a similar PROC REPORT step is encountered in the code, an additional tab (blank) is produced prior to the actual written final report.

PROC REPORT DATA = AEDECODB OUT = AEDECODP NOWD ;
COLUMN SYSTEMORGANCLASS AEDECOD EYEFL,COHORT,( PCT EVENTS) DUMMY;
DEFINE AEDECOD / ' ' GROUP NOPRINT;
DEFINE SYSTEMORGANCLASS / ' ' GROUP NOPRINT ;
DEFINE COHORT / ' ' ACROSS ORDER = FORMATTED noprint;
DEFINE EYEFL / ' ' ACROSS ORDER = FORMATTED noprint ;
DEFINE PCT / ' ' NOPRINT;
DEFINE EVENTS /' ' NOPRINT;
DEFINE DUMMY/ ' ' NOPRINT;
RUN;

Re: ODS excel and blank tabs

ballardw — Wed, 17 Nov 2021 21:22:16 GMT

Since your subject says this involves ODS Excel you should include the ODS Excel statement(s) and possibly other code that may be between the ODS statement and the Proc Report.

I would suggest if the purpose of the step is only to create a data set that you should do that outside of the Ods destination/ ods destination close; statements.

I suspect that Proc Report, as it is typically used to create tabular output when called, is reserving space in the ODS queue. Remove it from the inside the ods statements and I think it will go away.

Personally the only thing I put inside ODS destination code is stuff that actually generates all desired output. All data manipulation is done first. Just one reason is if I have to make an option change in Proc Report/Tabulate/SGPLOT etc. then I don't rerun code that might be lengthy to create the data sets used for the display just to get the new document.

Re: ODS excel and blank tabs

Cynthia_sas — Fri, 19 Nov 2021 17:53:53 GMT

Hi:
I've never seen a blank tab get generated using ODS EXCEL unless I explicitly request it using sheet_name and sheet_label sub-options, as shown below:

You did not show all your code and your ODS EXCEL options and sub-options. Also, without data, (either fake data or real data) nobody can test your code because without knowing the original structure, it's hard to understand how PROC REPORT is doing the transpose for you.

Cynthia

Re: ODS excel and blank tabs

xxformat_com — Mon, 22 Nov 2021 07:27:01 GMT

Hi @Cynthia_sas,

I guess he is looking at something like this bewlo, expecting that no output is generated if proc report has no variable to display.

I'm not aware of any option which would do that tho.


ods excel file="&xxtraining./reporting/test.xlsx";

proc report data = sashelp.class;
    column sex height, age;
    define sex    / ' ' group    ;
    define height / ' ' analysis ;
    define age    / ' ' across   ;
run;

proc report data = sashelp.class;
    column sex height, age;
    define sex    / ' ' group    noprint;
    define height / ' ' analysis noprint;
    define age    / ' ' across   noprint;
run;

ods excel close;

Re: ODS excel and blank tabs

Cynthia_sas — Mon, 22 Nov 2021 15:22:30 GMT

Hi:
But remember that the second sheet gets created when PROC REPORT -starts- -- before it knows that everything is NOPRINT (which doesn't make any sense to me why there would even be a PROC REPORT with all the columns -- ALL the columns set to NOPRINT). So the sheet name, by default is as shown below:

Since I'm not sure of the purpose of the second PROC REPORT or what the goal is, I can't comment further. When the original post came in, I interpreted the "blank tab" to be the sheet indicator tab at the bottom, not an empty sheet.Without further clarification, data and code from the original poster, we are all just guessing.

Cynthia