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.
This might help, see Data Driven Worksheets and Manual Multiple worksheets here:
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.
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.
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' );
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.
ok thanks for the update
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.