Getting rid of Bygroup on tabs.

[mmaleta851]

So when I run this:

data table;
input Name:$24. Date:YYQ8. Trips Sales;
format Name $24. Date YYQ8.;
datalines;
John 2021Q1 10 100
John 2021Q3 2 200
John 2021Q4 4 400
David 2021Q1 16 300
David 2021Q2 8 600
David 2021Q4 19 100
;
run;

proc sort data=work.table out=sorted_table;
by Name;
run;

ods excel file="\Table.xlsx"
options(sheet_interval="bygroup");
ods noproctitle;
proc print data=work.sorted_table;
by Name;
run;
ods excel close;

On each excel tab there is a "ByGroup # - " in addition the group instance. How do I get rid of this? Additionally, at the top of the Excel sheets, I get a "Name=" in addition to the instance. How do I get rid of this prefix?

[Cynthia_sas]

Hi:

  You need to use one more system option and then another suboption, as shown below:

Using the NOBYLINE option is like flipping a switch so that SAS uses the syntax for by value substitution using the #byval1 and #byline syntax. With ODS EXCEL if you want the tabs to be simply labeled with the By value, then use #byval1 as the value for the sheet_name suboption. Without any usage like this, you get the entire byline on the tab.

Cynthia

[mmaleta851]

Hey Cythina,

The #byval1 worked for the sheet_name. Thank you. However I don't want to get rid of the byline completely. I just want to get rid of the prefix "Name=". Is there a way to do this?

--Michael

[andreas_lds]

Maybe there is an easier way: i use nobyline together with a title statement (same value as in sheet_name) and add the option embedded_titles='yes' in the ods options.

[Cynthia_sas]

Hi:
Look at my code example and results carefully. My tabs just say David and John, the values of the BY variable. My tabs do not say NAME=. So in the TITLE statement, you would essentially need to do the same thing. By default the BYLINE is what it is--it will ALWAYS say NAME=. One of the reasons for using NOBYLINE is to control the look/feel of the separator text you get. Using #byline, #byvar and #byval allow you to do that in the TITLE -- that's how it was designed first, the usage in Excel destinations was built upon that original capability. Make sure your TITLE statement only has #byval1 in it and use the NOBYLINE option.

Cynthia