topic Re: ODS excel and proc report - Need to create a sheet even if the input dataset is empty in ODS and Base Reporting

ODS excel and proc report - Need to create a sheet even if the input dataset is empty

Vigneswar — Thu, 07 Feb 2019 10:48:15 GMT

Hi,

I have scenario where I need to export multiple datasets to a Excelsheet into different tabs.

Ex: I have three datasets A,B,C and i want to export into an excel workbook "Output" under sheets x,y,z correspondingly,

A ---> X

B ---> Y

C ---> Z

%macro Export(ouput1=,input1=,ouput2=,input2=,ouput3=,input3=);
ods excel file="/External/Output.xlsx"
options (sheet_name = "&input1.") style=statistical;
Proc report data= &output1.
style(header)=[background=LightGray];
columns col1 col2 col3;
define col1 /display ;
define col2 /display ;
define col3 /display ;
run;

ods excel options(sheet_name = "&input2");
proc report data= output2.
style(header)=[background=LightGray];
columns col1 col2 col3;
define col1 /display ;
define col2 /display ;
define col3 /display ;
run;

ods excel options(sheet_name = "&input3");
proc report data= output3.
style(header)=[background=LightGray];
columns col1 col2 col3;
define col1 /display ;
define col2 /display ;
define col3 /display ;
run;

%mend Export;

%Export(ouput1=A, input1=X, ouput2=B, input2=Y, ouput=C, input3=Z);

Out of my 3 input datasets, lets take B is an empty dataset but I wanted that corresponding sheet Y to be created as a blank one in my output excel file. Because the dataset B can be empty or have values based on that particular run on a given day.

Suggestions are highly appreciated!!

Thanks in advance,

Vigneswar

Re: ODS excel and proc report - Need to create a sheet even if the input dataset is empty

andreas_lds — Thu, 07 Feb 2019 11:13:55 GMT

Why have you written a macro? Seems to be not necessary to solve the problem. I added te option sheet_interval and i got an excel file with two sheets as expected.

ods excel file="output.xlsx" 
      options(sheet_interval="proc");

   ods excel options(sheet_name="Class");
   proc print data=sashelp.class;
   run;

   ods excel options(sheet_name="Cars");
   proc print data=sashelp.cars;
   run;

ods excel close;

Sorry, i should have read the whole post 😉

The code, of course, does not solve your problem ...

Re: ODS excel and proc report - Need to create a sheet even if the input dataset is empty

andreas_lds — Thu, 07 Feb 2019 12:20:51 GMT

Sorry, again, i have not found a way to add an empty sheet.