The SAS Output Delivery System and reporting techniques

Can I put the BY-group value into the ExcelXP sheet name?

Reply
Frequent Contributor
Posts: 102

Can I put the BY-group value into the ExcelXP sheet name?

Seems like an obvious thing to want to do. Am I just missing something in the documentation?

For example:

=====
ods tagsets.excelxp file='c:\sas\programs\excelxp\ex5.xls';

ods tagsets.excelxp options(sheet_label='by');

proc sort data=sashelp.class out=classbysex;
by sex;
run;

proc report data=classbysex nofs missing;
by sex;
run;

ods tagsets.excelxp close;
=====

I want the title of the first sheet to be "Students where sex is F" and the title of the second sheet to be "Students where sex is M".

Being able to programatically set the sheet name in PROC REPORT would also be a good feature.
SAS Employee
Posts: 95

Re: Can I put the BY-group value into the ExcelXP sheet name?

Posted in reply to JackHamilton
Sure. Just tell it that is the sheet_interval you want.

ods tagsets.exelxp options(sheet_interval='bygroup')

That will give you one worksheet per bygroup. The sheet name in this case
will be something like Sex=M

If you had

by sex age

It would still have the same sheet name but there would be two tables per sheet.
Frequent Contributor
Posts: 102

Re: Can I put the BY-group value into the ExcelXP sheet name?

The sheet titles I get are

By 1 Sex=F - Print

and

By 2 Sex=M - Print

All I want is

Sex=F

or, even better

Sex F

Is there a way to do that? I was hoping ODS NOPROCTITLE would get rid of the "- Print", but it doesn't.

sheet_label=' ' will get rid of the By, but not the sheet number or the "- Print".

Specifying "ods tagsets.excelxp options(sheet_label=' ' sheet_name=' ' sheet_interval='bygroup');" produces a result that I can only call unexpected.

Something that let me get to the values of %BYVALn and %BYVARn would be good. Then maybe I could specify all the text in the sheet title, with substitutions where I want them.

And something that would let me set it programatically with a CALL DEFINE in PROC REPORT would be even better (maybe not better for people who don't use PROC REPORT, but that would just be another reason for them to learn how to use it).
SAS Employee
Posts: 95

Re: Can I put the BY-group value into the ExcelXP sheet name?

Posted in reply to JackHamilton
You need an up to date tagset. That will give you what you want.

I know you know the url...

http://support.sas.com/rnd/base/topics/odsmarkup/

If you still are not happy you can create your own way of auto naming
by over riding the worksheet_label event. That event's only purpose
is to create the sheet name.

define tagset tagsets.excel;
parent = tagsets.excelxp;

define event worksheet_label;
.....
end;
end;

But the current tagset and probably any tagset created in the last few months will name the worksheet Sex=F and Sex=M.

But it only does that if the sheet_interval is bygroup. Maybe that naming
scheme is wrong. I'm not sure. I just tried to guess at what made sense.
SAS Employee
Posts: 95

Re: Can I put the BY-group value into the ExcelXP sheet name?

I should also mention that over riding that worksheet_label event would also allow you to change the way the sheet_label option works. Let me know if you have any idea's about how to make it better.
Frequent Contributor
Posts: 102

Re: Can I put the BY-group value into the ExcelXP sheet name?

I was having a problem (whose cause I still haven't figured out) getting the recompiled template to go into a place where SAS could find it, so I thought I was using the current version but wasn't. Still haven't gotten it to work right, but that's a separate problem.

If I run this code:

=====
ods tagsets.excelxp file='c:\sas\programs\excelxp\ex5.xls' options(sheet_interval='bygroup');

proc sort data=sashelp.class out=classbysex;
by sex;
run;

proc report data=classbysex nofs missing;
by sex;
run;

ods tagsets.excelxp close;
=====

I get this log:

=====
17187 ods tagsets.excelxp file='c:\sas\programs\excelxp\ex5.xls' options(sheet_interval='bygroup');
NOTE: Writing TAGSETS.EXCELXP Body file: c:\sas\programs\excelxp\ex5.xls
NOTE: This is the Excel XP tagset (SAS 9.1.3, v1.34, 04/07/06). Add options(doc='help') to the ods statement for more information.
17188
17189 proc sort data=sashelp.class out=classbysex;
17190 by sex;
17191 run;

NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: SAS sort was used.
NOTE: The data set WORK.CLASSBYSEX has 19 observations and 5 variables.
NOTE: PROCEDURE SORT used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds


17192
17193 proc report data=classbysex nofs missing;
17194 by sex;
17195 run;

NOTE: There were 19 observations read from the data set WORK.CLASSBYSEX.
NOTE: PROCEDURE REPORT used (Total process time):
real time 0.04 seconds
cpu time 0.04 seconds


17196
17197 ods tagsets.excelxp close;
=====

I get a workbook with two sheets, the first of which is named "Sex=M', the second of which is named "By 2 - Report".
SAS Employee
Posts: 95

Re: Can I put the BY-group value into the ExcelXP sheet name?

Posted in reply to JackHamilton
Here's what the doc says.

Sheet_Label: Default Value 'None'
Values: Any String";
This option is used in combination with the various worksheet naming.
heuristics which are based on the sheet interval.
This string will be used as the first part of the name instead of the
predefined string it would normally use.

These are the defaults:

'Proc ' total_Proc_count - label
'Page ' total_page_count - label
'By ' numberOfWorksheets byGroupLabel - label
'Table ' numberOfWorksheets - label

If sheet interval is bygroup, the worksheet name will be Just the
byvar = byval

If I understand you correctly what you would really like is this

label byval

Correct? I can get a tagset to you today if you like.
SAS Employee
Posts: 95

Re: Can I put the BY-group value into the ExcelXP sheet name?

Posted in reply to JackHamilton
I don't see a way to do an attachment. I can send you the tagset if you email me.

Here's the piece of code I changed in the worksheet_label event.
I think it will give you just what you want.

else /if cmp($sheet_interval, 'bygroup');
do /if ^$byval_name;
do /if $sheet_label;
set $worksheetName $label ' ' $byGroupLabel;
else;
set $worksheetName 'By ' ;
set $worksheetName $worksheetName $numberOfWorksheets ' ' $byGroupLabel;
done;
else;
do /if $sheet_label;
set $sheet_label $last_byval;
else;
set $worksheetName $worksheetName $byval_name '=' $last_byval;
done;
done;
Frequent Contributor
Posts: 102

Re: Can I put the BY-group value into the ExcelXP sheet name?

What I really want is a generalized way to specify my own worksheet name, not a dozen different tagsets with slight variations in the way names are constructed.

One way to do it is to add the following code near the bottom of the worksheet_label event, just above "unset $byGroupLabel":

=====
mvar override_sheetname;
putlog "Override_sheetname=" override_sheetname / if override_sheetname;
set $worksheetName override_sheetname / if override_sheetname;
=====

Then I can code

=====
proc report data=classbysex nofs missing;
column sex n name age height weight;
define sex / order noprint;
define n / noprint;
break after sex / page;
compute after sex;
call symput('override_sheetname', compbl('Sex is ' || sex || ' with ' || n || ' obs') );
endcomp;
run;

=====

and I will get exactly the titles I want.

I don't like using a macro variable, though, because if I forget to unset it bad things will happen the next time I use proc report without that call symput. Is there a way to make sure that the macro variable is unset at the end of the proc?

I thought of using a tagattr instead of a macro variable, but I was afraid that the parsing would be too difficult, and the code to create it would be complicated also.

Any possibility of putting something like that into the next release of the tagset?
Frequent Contributor
Posts: 102

Re: Can I put the BY-group value into the ExcelXP sheet name?

Posted in reply to JackHamilton
After further consideration, I prefer this code:

=====
do / if override_sheetname;
set $worksheetName override_sheetname;
putlog "Macro variable OVERRIDE_SHEETNAME used to create worksheet " $numberOfWorksheets " name '" override_sheetname "'.";
done;
=====

That makes it clear where the worksheet name came from.

In my example, I should have use the PUT function to format n, eliminating conversion messages.
SAS Employee
Posts: 95

Re: Can I put the BY-group value into the ExcelXP sheet name?

Posted in reply to JackHamilton
Currently a macro variable is going to give you the best results. It's the only thing dynamic enough to change at any point in time.

I guess we could do a tagattr value on the table style. That might be
better. The style over-ride could change for each by value. I'm not
sure you can do that though. Can we do a compute before a table or after a by?
Frequent Contributor
Posts: 102

Re: Can I put the BY-group value into the ExcelXP sheet name?

If there's a way to do a COMPUTE AFTER byvar, I don't know what it is.

You could specify the same variable as BOTH a BY variable and an ORDER variable, which would achieve a similar effect.

=====
proc report data=classbysex nofs missing;
by sex;
column sex n name age height weight;
define sex / order noprint;
define n / noprint;
break after sex / page;
compute before sex;
call symput('override_sheetname', compbl('Sex is ' || sex || ' with ' || put(n, comma6.0) || ' obs') );
endcomp;
run;
=====

Unfortunately, using a BY breaks my macro variable solution - I get "Sex is M with 10 obs" on both sheets. Maybe I just need to move the location where the name is set.

BY also breaks the regular naming - with a BY added to the PROC REPORT, but no OVERRIDE_SHEETNAME, the sheet names are "Sex=M" and "By 2 - Report".

How do you keep all of this tagset in your head at the same time? Aren't you afraid it will explode?
Frequent Contributor
Posts: 102

Re: Can I put the BY-group value into the ExcelXP sheet name?

Posted in reply to JackHamilton
By the way, I did a presentation on simple uses of the ExcelXP tagset, concentrating on MVS, at the Kaiser SAS Users Group meeting today. People really liked it; it will be a real timesaver.

I want to clean up the presentation a bit, then I'll put it on my web page.

I hate PowerPoint.
SAS Employee
Posts: 95

Re: Can I put the BY-group value into the ExcelXP sheet name?

Posted in reply to JackHamilton
I try to forget some of it between working on it. It is a monster of a tagset.
SAS Super FREQ
Posts: 8,868

Re: Can I put the BY-group value into the ExcelXP sheet name?

Here's what I'm wondering about -- related, but going further with the discussion. In the SAS title statement, you can use #byvar and #byval, etc with by group processing. ODS PROCLABEL does not currently support #byvar and #byval, but -if- it did, then you could kill 2 birds etc, etc, by allowing something like this:
[pre]
ods html ....;
ods tagsets.excelxp ...;
options nobyline;
ODS PROCLABEL "#byvar1 = #byval1";
proc whatever data=wombat;
by koala eucalyptus;
run;
ods _all_ close;
[/pre]
if ods html was using contents=, then every by group could use ODS PROCLABEL automatically without doing macro things to get the TOC label the way you wanted.

if excelxp was making multiple worksheets, then ODS PROCLABEL could indicate the string to be picked up by the tagset.

I know that Eric has done something similar in a tagset revision to answer a different question (for me, offline), but this possibility ODS PROCLABEL ...or a new ODS PROCBYLABEL might simplify things in making a one place to specify what you want using the #byval and #byvar that already exists and that folks know how to use.

Of course, changing a tagset is my favorite morning activity -- but it's not a solution for everybody -- and implementing the #byvar etc functionality via ODS PROCBYLABEL or ODS PROCLABEL -- would make it easier for folks new to SAS and ODS to do something slick with a much lower learning curve. If #byvar and #byval are put -someplace- so the title statement can get to them, why can't they be pumped into someplace in memory that persists beyond the life of the title statement???

just curious,
cynthia
Ask a Question
Discussion stats
  • 17 replies
  • 855 views
  • 0 likes
  • 4 in conversation