topic Re: Getting rid of Bygroup on tabs. in SAS Programming

Getting rid of Bygroup on tabs.

mmaleta851 — Fri, 18 Feb 2022 00:37:59 GMT

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?

Re: Getting rid of Bygroup on tabs.

Cynthia_sas — Fri, 18 Feb 2022 01:40:55 GMT

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

Re: Getting rid of Bygroup on tabs.

mmaleta851 — Fri, 18 Feb 2022 01:48:27 GMT

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

Re: Getting rid of Bygroup on tabs.

andreas_lds — Fri, 18 Feb 2022 06:29:40 GMT

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.

Re: Getting rid of Bygroup on tabs.

Cynthia_sas — Fri, 18 Feb 2022 19:47:34 GMT

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