The SAS Output Delivery System and reporting techniques

How to use ods to export a table when there is no content

Reply
Occasional Contributor
Posts: 10

How to use ods to export a table when there is no content

I was using ODS tagsets.excelxp to export 4 different tables to 4 corresponding tabs in the same excel file. It works great when all 4 tables have content. However, if one table does not have any content, then the tab won't show up in the output excel file at all. How can I make this tab show up even there is no content? There must be some option for this situation...

Any input will be appreciated, thanks!

SAS Super FREQ
Posts: 1,141

Re: How to use ods to export a table when there is no content


You may be better served if you post this question on the ODS and Base Reporting community.page.

Occasional Contributor
Posts: 10

Re: How to use ods to export a table when there is no content

Thank you! Already moved.

Super User
Posts: 19,156

Re: How to use ods to export a table when there is no content

Sadly, I don't think there's just an option or switch to deal with this.

My usual workaround is a macro that conditionally executes.  What do you want to be printed/exported if there is no data?

SAS Super FREQ
Posts: 8,820

Re: How to use ods to export a table when there is no content

I agree with Reeza. There is no "automatic" option to deal with this situation. It is up to the person who writes the code to control what is written out to the Excel sheet. There have been a lot of previous forum postings on the type of macro she describes, so it should not be too hard to find.

cynthia

Occasional Contributor
Posts: 10

Re: How to use ods to export a table when there is no content

If there is no content in the table, then I would like to print something like: "no subject were selected.", and still keep that tab in the same excel. The main purpose is to not let this empty tab disappear. 

Super User
Posts: 11,134

Re: How to use ods to export a table when there is no content

It might give us some insight if you post code used to create your output. The specific procedures used might provide some ideas.

Occasional Contributor
Posts: 10

Re: How to use ods to export a table when there is no content

Absolutely, here is the code I used:

ods listing close;

ods tagsets.excelxp file="     "

  style=analysis options(sheet_name="Inappropriate"); title; footnote;

proc sql;

select * from outliers (drop= tran_num);

quit;

ods tagsets.excelxp options(sheet_name="Multiple"); title; footnote;

Proc sql;

select * from multi_count ;

quit;

ods tagsets.excelxp options(sheet_name="Multiple Exceptions"); title; footnote;

proc sql;

select * from exceptions (drop= tran_num);

quit;

ods tagsets.excelxp options(sheet_name="All"); title; footnote;

proc sql;

select * from transfused (drop= tran_num);

quit;

ods tagsets.excelxp close;

ods listing;

Super User
Posts: 11,134

Re: How to use ods to export a table when there is no content

Something like this MAY work.

%macro MyOutput;

ods listing close;

ods tagsets.excelxp file="     "

  style=analysis options(sheet_name="Inappropriate"); title; footnote;

Proc sql noprint;

     select count(*) into outliercount

     from outliers;

quit;

%if &outliercount > 0 %then %do;

proc sql;

select * from outliers (drop= tran_num);

quit;

%end;

%else %do;

ODS text = "No outliers";

%end;

ods tagsets.excelxp options(sheet_name="Multiple"); title; footnote;

Proc sql noprint;

     select count(*) into multicount

     from multi_count;

quit;

%if &multicount>0 %then %do;

Proc sql;

select * from multi_count ;

quit;

%end;

%else %do;

Ods Text = "No multiple records found";

%end;

ods tagsets.excelxp options(sheet_name="Multiple Exceptions"); title; footnote;

proc sql noprint;

     select count(*) into trancount

     from exceptions;

quit;

%if &trancount > 0 %then %do;

proc sql;

select * from exceptions (drop= tran_num);

quit;

%end;

%else %do;

ODS text = "No Exceptions found";

%end;

ods tagsets.excelxp options(sheet_name="All"); title; footnote;

proc sql noprint;

     select count(*) into transfusecount

     from transfused;

quit;

%if &transfusecount > 0 %then %do;

proc sql;

select * from transfused (drop= tran_num);

quit;

%end;

%else %do;

ods text = "No transfused records found.";

%end;

ods tagsets.excelxp close;

ods listing;

%mend;

%MyOutput;

Occasional Contributor
Posts: 10

Re: How to use ods to export a table when there is no content

Thanks ballardw, I tried the code you provided and got the following error info:

error code.jpg

Super User
Posts: 9,867

Re: How to use ods to export a table when there is no content

if you like proc export :

proc export data=sashelp.class(where=(sex='F')) outfile='c:\temp\want.xls' dbms=excel replace;

sheet='Female';run;

proc export data=sashelp.class(where=(sex='FFFF')) outfile='c:\temp\want.xls' dbms=excel replace;

sheet='FFFF';run;

Xia Keshan

Occasional Contributor
Posts: 10

Re: How to use ods to export a table when there is no content

Thansk ,Xia. Proc export works-- it will export the empty table without issue. However, the format is not nearly as good as ods tagsets.....

Super User
Posts: 11,134

Re: How to use ods to export a table when there is no content

It looks like the editor stripped, or I had a brain malf, out the : in the into clauses. There does need to be one for each such as INTO : Outliercount.

Occasional Contributor
Posts: 10

Re: How to use ods to export a table when there is no content

Thanks, ballardw. I add the colon before count variable. Still only the table with content can be exported...

Super User
Posts: 19,156

Re: How to use ods to export a table when there is no content

This works, but isn't pretty. Basically you have to create an empty data set and if the data set you'd like to print is empty you print the empty data set instead.

Wrapping the whole thing in a macro makes it work conditionally.

If this was a one-off I'd do it this way. If this was for production or you're going to look at this again, I'd recommend a different solution.

data class2;

    set sashelp.class;

    if _n_>=1 then delete;

run;

data empty;

    value="This dataset is empty";

    label value="Unfortunately";

run;

%macro export();

ods listing close;

ods tagsets.excelxp file="C:\_localdata\test.xml"

  style=analysis options(sheet_name="Inappropriate"); title; footnote;

proc sql;

select * from  class;

quit;

%if &sqlobs=0 %then %do;

proc print data=empty noobs label;

run;

%end;

ods tagsets.excelxp options(sheet_name="Multiple"); title; footnote;

Proc sql;

select * from class ;

quit;

%if &sqlobs=0 %then %do;

proc print data=empty noobs label;

run;

%end;

ods tagsets.excelxp options(sheet_name="Multiple Exceptions"); title; footnote;

proc sql;

select * from class ;

quit;

%if &sqlobs=0 %then %do;

proc print data=empty noobs label;

run;

%end;

ods tagsets.excelxp options(sheet_name="All"); title; footnote;

proc sql;

select * from class2 ;

quit;

%if &sqlobs=0 %then %do;

proc print data=empty noobs label;

run;

%end;

ods tagsets.excelxp close;

ods listing;

%mend export;

%export;

Ask a Question
Discussion stats
  • 14 replies
  • 1340 views
  • 3 likes
  • 6 in conversation