topic Re: ods tagsets update one sheet without writing over another in ODS and Base Reporting

ods tagsets update one sheet without writing over another

whs278 — Mon, 02 Dec 2019 21:23:04 GMT

Hi,

I wanted to add data from PROC TABULATE into one Excel sheet using ODS TAGSETS.

Then I wanted to pull that data into a table in another Excel sheet and manually adjust the table within Excel.

However, I was wondering if there was a way to update one sheet through SAS without deleting the other sheet that I created manually in Excel.

Thank you for your help. Let me know if anything is unclear, or if there is any additional information I should provide.

-Bill

Re: ods tagsets update one sheet without writing over another

SASKiwi — Mon, 02 Dec 2019 21:39:46 GMT

One of the current limitations of ODS TAGSETs or EXCEL is it can only create workbooks from scratch. It isn't possible to modify existing workbooks. ODS TAGSETs create XML-based workbooks behind the scenes. It would be problematic to interpret and modify an existing XML-based workbook.

Re: ods tagsets update one sheet without writing over another

whs278 — Mon, 02 Dec 2019 21:41:24 GMT

Is there any other method in SAS for accomplishing the same thing or something similar?

Re: ods tagsets update one sheet without writing over another

Cynthia_sas — Mon, 02 Dec 2019 21:46:05 GMT

Hi:

ODS always writes over the FILE= named file that you list in the code. You cannot "insert" a worksheet into a workbook using ODS.

You can insert a worksheet into a workbook using PROC EXPORT or the LIBNAME engine for Excel or XLSX, but you need a dataset for either of those techniques.

Hope this helps,

Cynthia

Re: ods tagsets update one sheet without writing over another

SASKiwi — Mon, 02 Dec 2019 21:52:52 GMT

Alternatives would be PROC EXPORT with the SHEET statement or the LIBNAME statement using the EXCEL or XLS or XLSX engine. These can be used with native Excel formats, not XML. Unfortunately these methods don't provide the formatting capabilities of ODS. I f formatting is important then you would need to do that manually in a sheet separate from the one SAS is populating.