Help using Base SAS procedures

ods tagsets.execlxp help

Reply
Regular Contributor
Regular Contributor
Posts: 238

ods tagsets.execlxp help

i have an ods and i want the ability to write to several sheets. Sure I can do multiple proc sqls or data steps to get a single table for each thing I want to export, which is:

1st sheet: overall counts by tax id summary

2nd sheet: metal counts by tax id

3rd sheet: drug counts by tax id

4th sheet: no implant count by tax id

Adding the counts on sheets 2-4 would equal what is on the 1st sheet

Right now I have all this data that writes to one table and then I do an ods to export to excel naming columns what I want, etc, but it will write to 1 workbook, the first sheet only. Then I would have to move things around. I don't want to do that. I am just wondering in the ods if I can do 1 taget and name my main workbook and then thru the 1 ods tagset depending on the data, tell it to output to whatever I name a sheet. If I cannot and have to do separate ods statements for each, then it might be best just to create mulitple proc sql or data steps to get the 4 tables in a final export version and export using a sheet name to the overall workbook.

Super User
Posts: 17,829

Re: ods tagsets.execlxp help

This might help, see Data Driven Worksheets and Manual Multiple worksheets here:

Base SAS: Demo: ExcelXP Tagset and Microsoft Excel

Regular Contributor
Regular Contributor
Posts: 238

Re: ods tagsets.execlxp help

I read thru the proc export and you can put in options(sheet="Name of Sheet'); but when I do this it is still not working. The pages you gave me don't really have an example of how it should looke when using the prior tag of:

ods tagsets.excelxp and then the sheet name thrown in. Of course proper format and following this is my proc report statement nowd split='/' data=pci.tablename;

then the columns etc etc. when i run this is just overlooks the sheet=name.

Regular Contributor
Regular Contributor
Posts: 238

Re: ods tagsets.execlxp help


Ok. just an update. I got the sheet_name in options to work but I cannot get it to append to the current workbook. Like further down with other code and tables I want to use the same workbook name defined and just select a new sheet name. I did a options(doc='help') to look for options but see none.

Valued Guide
Posts: 2,175

Re: ods tagsets.execlxp help

don't close your  ODS destination

until you do the next PROC writes to the workbook

just use

ods tagsets.excelxp options( sheet_name='next name' );

Super User
Posts: 3,106

Re: ods tagsets.execlxp help

You can't use the ExcelXP tagset to append to an existing workbook. This is because it creates a workbook as an XML file and so needs to be built from scratch each time.

The usual way to deal with this is to keep a complete copy of the workbook data in SAS and write out every sheet, every time.

There are other techniques for populating existing workbooks, including PROC EXPORT with the SHEET statement, and LIBNAME EXCEL, but then you lose the additional formating functionality provided by ExcelXP.

Regular Contributor
Regular Contributor
Posts: 238

Re: ods tagsets.execlxp help

ok thanks for the update


Ask a Question
Discussion stats
  • 6 replies
  • 262 views
  • 0 likes
  • 4 in conversation