Display an empty table with ODS Tagsets.ExcelXP

Question

I have this code to write a report in an Excel file, using ODS Tagsets.ExcelXP. When the dataset "revenus" is not empty, the report is displayed in the Excel sheet. But, when the dataset "revenus" is empty, the report is not created.

How we can modify this code to display a report even if the dataset is empty? Can we have the report header and an empty row under to it?

Thank you.

ods tagsets.ExcelXP path="&pathEx." file="file_name.xls" style=seaside
options(autofit_height="yes"
        pagebreaks="yes"
        orientation="portrait"
        papersize="letter"
        sheet_interval="none"
        sheet_name="Infos"
        WIDTH_POINTS = "12" WIDTH_FUDGE = ".0625"       absolute_column_width="120,80,200,200"
        );

ods escapechar="^";

proc report data=&lib..revenus nowindows missing spanrows  style=seaside
    style(report)=[frame=box rules=all bordercolor=black borderwidth=0.5
                    foreground=black Font_face='Times New Roman' font_size=10pt
                    background=none ]
    style(header)=[background=#e0e0e0 foreground=blue just=center]
    style(column)=[Font_face='Times New Roman' font_size=10pt
                    just=left]
;

    define an_imposi / display 'Année';
    define rev_tot / display 'Revenu total';

    title j=center height=12pt 'Revenus';

run;

The macro appendix has a solution for this here: documentation.sas.com/… — Reeza
Thank you! I had a problem with how to write this "if" condition in a macro and "Proc Report". Now it is ok. — D. O.

D. O. D. O. · Accepted Answer · 2018-01-30T21:47:52

The code is modified with the help of Reeza comment :

ods tagsets.ExcelXP path="&pathEx." file="file_name.xls" style=seaside
options(autofit_height="yes"
        pagebreaks="yes"
        orientation="portrait"
        papersize="letter"
        sheet_interval="none"
        sheet_name="Infos"
        WIDTH_POINTS = "12" WIDTH_FUDGE = ".0625"       absolute_column_width="120,80,200,200"
        );

ods escapechar="^";

%macro imm;
%if &nobs ne 0 %then %do;
proc report data=&lib..revenus nowindows missing spanrows  style=seaside
    style(report)=[frame=box rules=all bordercolor=black borderwidth=0.5
                    foreground=black Font_face='Times New Roman' font_size=10pt
                    background=none ]
    style(header)=[background=#e0e0e0 foreground=blue just=center]
    style(column)=[Font_face='Times New Roman' font_size=10pt
                    just=left]
;

    define an_imposi / display 'Année';
    define rev_tot / display 'Revenu total';

    title j=center height=12pt 'Revenus';

run;

%end;
%else %do;
    data _null_;
     title;
     file print;
     put _page_;
     put "No data in the table!!";
    run;
   %end;

%mend imm;
%imm;

The macro variable "nobs" contains the number of observations in the dataset "revenus".

Display an empty table with ODS Tagsets.ExcelXP

1 Answers