BookmarkSubscribeRSS Feed
tmm
Fluorite | Level 6 tmm
Fluorite | Level 6

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.

6 REPLIES 6
Reeza
Super User

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

Base SAS: Demo: ExcelXP Tagset and Microsoft Excel

tmm
Fluorite | Level 6 tmm
Fluorite | Level 6

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.

tmm
Fluorite | Level 6 tmm
Fluorite | Level 6


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.

Peter_C
Rhodochrosite | Level 12

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

SASKiwi
PROC Star

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.

tmm
Fluorite | Level 6 tmm
Fluorite | Level 6

ok thanks for the update


sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 780 views
  • 0 likes
  • 4 in conversation