topic Re: ods tagsets.execlxp help in SAS Procedures

ods tagsets.execlxp help

tmm — Fri, 01 Mar 2013 17:26:19 GMT

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.

Re: ods tagsets.execlxp help

Reeza — Fri, 01 Mar 2013 17:50:12 GMT

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

Base SAS: Demo: ExcelXP Tagset and Microsoft Excel

Re: ods tagsets.execlxp help

tmm — Tue, 05 Mar 2013 20:32:42 GMT

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.

Re: ods tagsets.execlxp help

tmm — Tue, 05 Mar 2013 20:50:26 GMT

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.

Re: ods tagsets.execlxp help

SASKiwi — Tue, 05 Mar 2013 21:11:06 GMT

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.

Re: ods tagsets.execlxp help

tmm — Wed, 06 Mar 2013 19:12:29 GMT

ok thanks for the update

Re: ods tagsets.execlxp help

Peter_C — Wed, 06 Mar 2013 23:44:45 GMT

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' );