The SAS Output Delivery System and reporting techniques

renaming excel sheets using ods markup

Reply
Contributor
Posts: 27

renaming excel sheets using ods markup

Following is the code I am using to export datasets into different excel sheets of a same workbook. I am able to get the output,but I am not able to rename the excel sheets using the sheet_name option. Can anyone let me know where i am going wrong.

Moreover if a dataset has no records I need blank sheet only with the variable names, which is not happening.



ods listing close;
ods tagsets.excelxp file='C:\test.xml'
options(sheet_interval='none' sheet_name='Missing in Dataset');

proc print data=missing_in_ds NOobs ; run;quit;
ods tagsets.excelxp options(sheet_interval='none' sheet_name='Missing in Standards');
proc print data=missing_in_stnds NOobs; run;quit;
ods tagsets.excelxp options(sheet_interval='none' sheet_name='Length_difference');
proc print data=lengths NOobs;run;quit;
ods tagsets.excelxp options(sheet_interval='none' sheet_name='Type_difference');
proc print data=type NOobs; run;quit;

ODS _all_ close;

ods listing;
SAS Super FREQ
Posts: 8,864

Re: renaming excel sheets using ods markup

Hi:
First, when I run the code below, in my SAS log, I -do- get the sheet names that you indicate. I am using the following version of the TAGSETS.EXCELXP template:

NOTE: This is the Excel XP tagset (SAS 9.1.3, v1.75, 07/26/07).



I recommend that you check your version of the ExcelXP tagset template and see whether you need to update it, following these instructions:
http://support.sas.com/kb/32/394.html (the URL to obtain updated tagset templates is listed in the note.)

When you use ODS TAGSETS.EXCELXP, you are not performing an "export" to a true, binary Excel file -- such as can be accomplished with PROC EXPORT and/or the Excel LIBNAME engine. ODS TAGSETS.EXCELXP creates a Microsoft Office 2003 Spreadsheet Markup Language XML file, which is an ASCII text file that Excel knows how to open and render as worksheets in a workbook. You could have used PROC GLM or PROC REG or PROC TABULATE to create your output, as well as PROC PRINT. For example, if you use ODS TAGSETS.EXCELXP, your report TITLE (if you have one) would go into the header area of the Excel workbook; whereas, if you used PROC EXPORT or the Excel LIBNAME engine, to export your dataset to an Excel file, your dataset would not have a TITLE statement. If you use ODS TAGSETS.EXCELXP, the style information from your ODS style template will be used to set the colors and fonts of the output when rendered in Excel. On the other hand, if you used PROC EXPORT or the Excel LIBNAME engine, you would not get any colors or font "styles" in the Excel file.

Next, if you try to use PROC PRINT on a dataset with NO observations or records, you should see this NOTE in the SAS log:
NOTE: No observations in data set WORK.EMPTY.

SAS will not automatically create a report when there are 0 observations in a dataset. You can programmatically check how many observations are in a dataset and use a SAS Macro program or some other method to create output (possibly creating one observation with all missing values to send to PROC PRINT). There have been many previous forum postings on how to test for whether a query returns results or whether a dataset has 0 observations.

If you still have problems with the sheet names after you have updated the tagset template, you might wish to work with Tech Support on this issue.

cynthia
[pre]
ods _all_ close;
**1) Use SHEET_NAME suboption;
ods tagsets.excelxp file='C:\temp\name_sheets.xml'
options(sheet_interval='none'
sheet_name='Missing in Dataset') style=sasweb;

** step boundary for proc print is just RUN do not need QUIT;
proc print data=sashelp.class(obs=2) NOobs ; run;

ods tagsets.excelxp options(sheet_interval='none' sheet_name='Missing in Standards');

proc print data=sashelp.shoes(obs=2) NOobs; run;

ods tagsets.excelxp options(sheet_interval='none' sheet_name='Length_difference');

proc print data=sashelp.prdsale(obs=2) NOobs;run;

ods tagsets.excelxp options(sheet_interval='none' sheet_name='Type_difference');

proc print data=sashelp.class(obs=3) NOobs; run;

ODS _all_ close;



**2) Show NOTE in SAS log when try to print empty dataset with 0 obs;
data empty;
set sashelp.class;
where sex = '2';
run;

ods tagsets.excelxp file='C:\temp\empty.xml'
options(sheet_interval='none'
sheet_name='Missing in Dataset') style=sasweb;

proc print data=sashelp.shoes(obs=2) NOobs; run;

ods tagsets.excelxp options(sheet_interval='none' sheet_name='Empty');

proc print data=empty NOobs ; run;

ODS _all_ close;
[/pre]
Contributor
Posts: 27

Re: renaming excel sheets using ods markup

Posted in reply to Cynthia_sas
Thanks Cynthia....It worked
Ask a Question
Discussion stats
  • 2 replies
  • 1225 views
  • 0 likes
  • 2 in conversation