BookmarkSubscribeRSS Feed
JackHamilton
Lapis Lazuli | Level 10
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.
18 REPLIES 18
Eric_SAS
SAS Employee
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.
JackHamilton
Lapis Lazuli | Level 10
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).
Eric_SAS
SAS Employee
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.
Eric_SAS
SAS Employee
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.
JackHamilton
Lapis Lazuli | Level 10
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".
avellani
Fluorite | Level 6
Specifying "ods tagsets.excelxp options(sheet_label=' ' sheet_name=' ' sheet_interval='bygroup');" produces a result that I can only call unexpected.
hahaha
Eric_SAS
SAS Employee
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.
Eric_SAS
SAS Employee
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;
JackHamilton
Lapis Lazuli | Level 10
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?
JackHamilton
Lapis Lazuli | Level 10
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.
Eric_SAS
SAS Employee
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?
JackHamilton
Lapis Lazuli | Level 10
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?
JackHamilton
Lapis Lazuli | Level 10
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.
Eric_SAS
SAS Employee
I try to forget some of it between working on it. It is a monster of a tagset.

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
  • 18 replies
  • 2527 views
  • 1 like
  • 5 in conversation