BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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!
4 REPLIES 4
JackHamilton
Lapis Lazuli | Level 10
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
Not applicable
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
SAS Employee
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
Not applicable
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!

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 4 replies
  • 684 views
  • 0 likes
  • 3 in conversation