ods tagsets update one sheet without writing over another

[whs278]

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 

[SASKiwi · Accepted solution]

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.

[whs278]

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

[SASKiwi]

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.

[Cynthia_sas]

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