improvements to ExcelXP sheet naming conventions?

[deleted_user]

With the release of some of the new versions of the ExcelXP tagset over the past few months I have been very pleased with some of the improved built-in formatting functions, particularly the improved justification. However, I wonder if I am still missing a way of how to get the sheet-naming conventions the way I want them, or if the tagset hasn't quite yet evolved to that level of functionality.

Issue: I have a PROC REPORT with a by-group. I want each by-group table to appear on a separate sheet. Additionally, I would like each sheet name to take the value of the by-group (i.e. if my by-variable is year, the first sheet name should say 2005 and contain the 2005 table, the next sheet name should say 2006 and contain the 2006 table, etc.).

I noticed that within the March 2007 updates, there was an option added so that if sheet_label was set to " " and sheet_interval was set to "bygroup", the sheet name would take on the value of the bygroup. This is very close to what I need, since setting these options in this manner assigns the first bygroup value to the sheet name. However, sheet_interval = 'bygroup' makes all the tables appear on a single page, which is not what I want.

Am I still missing an option, or is this something that is yet to be developed?

Thanks!

[JackHamilton]

It really is too bad the OVERRIDE_SHEETNAME macro variable doesn't work the way I would expect it to - that would be the solution to many sheet naming problems in PROC REPORT.

ods tagsets.excelxp 

      file='c:\temp\sheetname.xls';



%let override_sheetname=;



proc report data=sashelp.class nowindows missing;



   columns age sex name weight height;   



   define age / order;



   compute before age; 

      title = 'Age is ' || put(age, 2.);

      call symput('override_sheetname', title);

   endcomp;

 

   break after age / page;



run;



%symdel override_sheetname / nowarn;



ods tagsets.excelxp close;

Unfortunately, the sheet names are rotated by 1 position (the title for the first sheet is shown on the second sheet, the title for the second sheet is shown on the third sheet, and so on, with the title for the last sheet appearing, surprisingly, on the first sheet).

[deleted_user]

Jack--

That's quite clever, actually. If you compute before age, everything comes out correctly other than the first and last (although the last isn't technically wrong; it's just duplicating the title of the first tab and therefore tacks a "2" onto the end).

[Eric_SAS]

Sheet_interval = bygroup causes each table to go on a single worksheet. Unless there is more than one table per by value. - ie, if you have more than one by value, the worksheet only changes when the first by value changes.

If you are using proc print, you must have bylines on. Proc print does not provide by value data to ods so the tagset has to parse the by line to get it. Without bylines proc print with a by, will all go on one page because the tagset can't tell there was a by.

There is an option on the tagset, suppress_bylines that will allow you to get rid of them in the output but still allow them for processing so the tagset knows what to do.

[deleted_user]

Apparently PROC REPORT is also affected by the bylines option. When I removed the NOBYLINE option and set suppress_bylines to yes, it worked perfectly. I never would have guessed.

This fix saved me a lot of grief...thanks so much!