appending outputs in excel using ODS

[Pavan_SAS]

hi,

q:how i can append my reports into an exacl sheet?

there is an output in an excel sheet.
i want to append my next output into the same sheet by using PROC REPORT and ODS.

is there any option to do this task?
By default, my new output is over writing the old one. but i want to append to the old one, with out over writing.....

Help me....

[Cynthia_sas]

Hi:
By default, ODS -recreates- your FILE= file every time you run your program. As far as I know, there is no capability to "append" files for any ODS destination except for ODS HTML, as described in this Tech Support note:
http://support.sas.com/kb/23/660.html (ODS HTML works this way because you are creating 1 HTML file and the opening and closing tags are controllable using the NOTOP and NOBOT suboptions.)

In contrast, ODS RTF and ODS PDF do not allow appending of files.
http://support.sas.com/kb/24/034.html
http://support.sas.com/kb/35/375.html

From what you say:
"there is an output in an excel sheet.
i want to append my next output into the same sheet ..."

It seems like ODS HTML (or ODS MSOFFICE2K) -might- work for you. The catch is that this technique will only work if your first/original file was also created with ODS HTML. When you use ODS HTML, you are not creating a true, binary Excel file -- you are creating an ASCII text file of HTML tags that Excel knows how to open and render. When you create your first ODS HTML file, be sure to use the NOBOT suboption. Then when you create your second ODS HTML file, be sure to use the NOTOP suboption. You should see both outputs in one HTML file -- which Excel will interpret as one sheet -- because 1 HTML page = 1 sheet in 1 workbook.

If you have an existing -binary- Excel workbook to which you want to add your report, I believe that you cannot use ODS to accomplish this task unless you first IMPORT the first workbook into SAS and then rewrite the first file's information, along with your new report -- using ODS HTML. Or, you could convert your report into a SAS dataset and then use PROC EXPORT or the Excel LIBNAME engine to write the report DATA to a new sheet in the binary Excel workbook.

The approach that you choose will be dependent on how that first sheet was created. You might wish to work with Tech Support on this question, as they can help you choose from the various options, based on information about your first sheet, which version of SAS you're using and which version of Excel/Office you're using.

cynthia