BookmarkSubscribeRSS Feed
Rocktd
Calcite | Level 5

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!

14 REPLIES 14
Jay54
Meteorite | Level 14


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

Rocktd
Calcite | Level 5

Thank you! Already moved.

Reeza
Super User

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?

Cynthia_sas
SAS Super FREQ

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

Rocktd
Calcite | Level 5

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. 

ballardw
Super User

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

Rocktd
Calcite | Level 5

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;

ballardw
Super User

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;

Rocktd
Calcite | Level 5

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

error code.jpg

Ksharp
Super User

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

Rocktd
Calcite | Level 5

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

ballardw
Super User

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.

Rocktd
Calcite | Level 5

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

Reeza
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 14 replies
  • 3355 views
  • 3 likes
  • 6 in conversation