topic Re: Is it possible to append data using ods excel in SAS Enterprise Guide

Is it possible to append data using ods excel

alepage — Wed, 16 Sep 2020 14:43:41 GMT

Hello,

I am using SAS EG 7.13 , and I need to create an Excel file with two different sheets containing title (see the provided worksheet). I the first sheet will be filled-up first then close. The data will be bring into the second sheet using another sas program ( it is why , I am looking for an append option).

Can we do this task with ods or with another one.

Re: Is it possible to append data using ods excel

ChrisHemedinger — Wed, 16 Sep 2020 14:56:31 GMT

You can add multiple sheets to an ODS EXCEL destination by using the SHEET_INTERVAL options. You can break sheets along PROC boundaries, tables, or arbitrary break points within your process by adjusting the SHEET_INTERVAL.

However, once you close the destination with ODS EXCEL CLOSE, you cannot append the sheet with ODS EXCEL again. ODS EXCEL (open) will always create/write over the XLSX file.

But PROC EXPORT can be used to add/replace sheets in an existing workbook. I have an example of this here.

Re: Is it possible to append data using ods excel

Kurt_Bremser — Wed, 16 Sep 2020 14:59:11 GMT

Or you use LIBNAME XLSX and then run a data step or PROC COPY to create the additional sheet.

Re: Is it possible to append data using ods excel

alepage — Wed, 16 Sep 2020 17:39:57 GMT

Hello,

The libname statement is a nice options but I think that I will loose my title1 and title2 into the worksheet. Am I right.

Re: Is it possible to append data using ods excel

ChrisHemedinger — Wed, 16 Sep 2020 17:44:34 GMT

Correct. PROC EXPORT and LIBNAME XLSX are for data only. ODS EXCEL allows for report elements, including titles, footnotes, graphics, and generate report formatting with colors, etc.

Re: Is it possible to append data using ods excel

brb — Wed, 18 May 2022 10:27:44 GMT

Goodness me, SAS is so limiting. I am forced to use SAS for my current project which is why I am here. Python has multiple ways of doing this easily. Anyone new to SAS, save your time and money and learn Python or R instead.