topic Changing sheet counter in excel output (from ods tagsets.excelxp) in SAS Procedures

Changing sheet counter in excel output (from ods tagsets.excelxp)

Daniela_P — Thu, 11 Jun 2015 13:29:25 GMT

Dear experts,

I'd like to change the default number of the sheets when I use tagsets.excelxp with a proc tabulate and different table instructions inside.

I'd like to avoid the alternative way to create many proc tabulate as many tables I have to do...

Is there any option/code to do it?

Thanks in advance.

Daniela

Re: Changing sheet counter in excel output (from ods tagsets.excelxp)

RW9 — Thu, 11 Jun 2015 14:15:27 GMT

Sorry you will need to be clearer in your description, I am not understanding what you mean by "default number" of sheets? Specify what you are doing with code and required output.

Re: Changing sheet counter in excel output (from ods tagsets.excelxp)

Daniela_P — Thu, 18 Jun 2015 13:05:27 GMT

The problem is when I write a lot of table inside the same proc tabulate (within an ods tagsets.excelxp output):

ods tagsets.excelxp path="C:\Tables" file="table1.xml";

ods tagsets.excelxp options (sheet_interval="table");

ods tagsets.excelxp options (sheet_name="table 1");

proc tabulate data=sashelp.class;

CLASS age sex height weight;

TABLE age,sex*(n=''*F=9.)/rts=40;

TABLE height,sex*(n=''*F=9.)/rts=40;

TABLE weight,sex*(n=''*F=9.)/rts=40;

run;

ods tagsets.excelxp options (sheet_name="table 2");

proc tabulate data=sashelp.class;

where age=15;

CLASS age sex height weight;

TABLE age,sex*(n=''*F=9.)/rts=40;

TABLE height,sex*(n=''*F=9.)/rts=40;

TABLE weight,sex*(n=''*F=9.)/rts=40;

run;

ods _all_ close;

By default sas names the sheets of the first proc: table 1 , table 1 2, table 1 3 and the sheets of the second proc: table 2, table 2 2, table 2 3.

I'd like, instead: table 1.1 , table 1.2, table 1.3 and table 2.1 , table 2.2, table 2.3.

So it should begin from number 1 in the first sheet (instead of writing nothing) and it should put "." instead of the space between the name and the counter.

Thanks again,

Daniela

Re: Changing sheet counter in excel output (from ods tagsets.excelxp)

Cynthia_sas — Thu, 18 Jun 2015 14:28:14 GMT

Hi:

I am not sure you can get ODS to use a '.' instead of a space, but you can use the sheet_name and sheet_label suboptions to have some control over the table. Switching your code to SAS Macro might be one approach. I am not sure whether you can change the tagset template for ExcelXP to alter the way it makes sheet names or not. That would be a question for Tech Support.

cynthia