Create one XML file with multipl sheets By -ods tagsets.excelxp

[Ronein]

Hello,

This code create one XML file with multiple sheets.

May anyone explain what the following statements are doing:

1-options nobyline;

2-suppress_bylines='yes'

3-options byline;

I also want to ask which statement told SAS that the table will be located (start) in cell A1 in the XML file?

If I want that the table will start in cell A1 for example ,what shoould I change in the code?

IF I want to prevent print on screen ,what should I do?

proc sort data=sashelp.class out=class;
where age in (12 13 14);
by age;
run;
  
title;
options nobyline;
ods tagsets.excelxp file='c:\temp\showby.xml'
    style=htmlblue options(sheet_interval='bygroup' sheet_name='Age_#byval1'
                           suppress_bylines='yes');
  
proc report data=class;
by age;
column age name sex height weight;
run;
ods tagsets.excelxp close;
options byline;

[Reeza]

Q1 & 3 - RTM

BYLINE System Option

Specifies whether to print BY lines above each BY group.

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/lesysoptsref/n1hr2wb7h5g6twn1gtt5r4zjsg39.htm

Q2 - RTM

SUPPRESS_BYLINES

no

Suppresses bylines in the worksheet

https://support.sas.com/rnd/base/ods/odsmarkup/excelxp_help.html

TAGSETS does not support methods to control where the output is placed on the sheet. ODS EXCEL does have the starts_at option but that isn't present in ODS TAGSETS.EXCELXP.  You can use the SKIP_SPACE option (see link above) to fudge this a bit and control the row but not column location as far as I understand it. 

ODS TAGSETS.EXCELXP is older, and no longer being developed. ODS EXCEL is the replacement recommended. 

To prevent output to your 'screen' I assume that means your listing or HTML displayed results - you then turn off the destination/output format.

ods html close;
ods listing close;

Here's the documentation on the different destinations.

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/odsug/p1n357e2fq6kjkn1ijsu3w97lxl1.htm

---

@Ronein wrote:

Hello,

This code create one XML file with multiple sheets.

May anyone explain what the following statements are doing:

1-options nobyline;

2-suppress_bylines='yes'

3-options byline;

I also want to ask which statement told SAS that the table will be located (start) in cell A1 in the XML file?

If I want that the table will start in cell A1 for example ,what shoould I change in the code?

IF I want to prevent print on screen ,what should I do?

proc sort data=sashelp.class out=class;
where age in (12 13 14);
by age;
run;
  
title;
options nobyline;
ods tagsets.excelxp file='c:\temp\showby.xml'
    style=htmlblue options(sheet_interval='bygroup' sheet_name='Age_#byval1'
                           suppress_bylines='yes');
  
proc report data=class;
by age;
column age name sex height weight;
run;
ods tagsets.excelxp close;
options byline;

---

[jimbarbour]

---

@Ronein wrote:

May anyone explain what the following statements are doing:

1-options nobyline;

2-suppress_bylines='yes'

3-options byline;

---

The NOBYLINE and BYLINE options turn off and then back on the printing of Bylines in the Proc Report in your code.  The suppress_bylines='yes' option is an ODS option that affects how the ExcelXP tagset prints -- or in this case suppresses -- bylines.  The suppress_bylines='yes' option might be redundant if you have the system level option NOBYLINE in effect; however, it might suppress something in addtion to what NOBYLINE suppresses.  You'd have to play with that.

Jim