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!
You may be better served if you post this question on the ODS and Base Reporting community.page.
Thank you! Already moved.
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?
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
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.
It might give us some insight if you post code used to create your output. The specific procedures used might provide some ideas.
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;
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;
Thanks ballardw, I tried the code you provided and got the following error info:
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
Thansk ,Xia. Proc export works-- it will export the empty table without issue. However, the format is not nearly as good as ods tagsets.....
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.
Thanks, ballardw. I add the colon before count variable. Still only the table with content can be exported...
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.