03-01-2013 12:26 PM
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.
03-05-2013 03:32 PM
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.
03-05-2013 03:50 PM
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.
03-05-2013 04:11 PM
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.