topic Re: ODS Excel Sheet Name length limit reduced when using & in sheet name in SAS Programming

ODS Excel Sheet Name length limit reduced when using & in sheet name

arjunsingh3993 — Wed, 22 Feb 2023 10:56:55 GMT

While using ODS Excel, sheet name does have limit of 28 characters but if & is present in sheetname this limit is reduced. Could anyone please explain the reason behind this.

Thank you so much in advance!!.

For Example - Sheetname = "Acct_Num&ABCD_BCD&ABC" is getting trimmed to Acct_Num&ABCD_BCD&A.

But Sheetname = "Acct_Num&ABCD_BCDandABC" is not getting trimmed at all.

Re: ODS Excel Sheet Name length limit reduced when using & in sheet name

ChrisNZ — Thu, 23 Feb 2023 02:12:42 GMT

What you describe is not happening to me.

proc export data=sashelp.class outfile="&wdir\t3.xlsx"; sheet="Acct_Num&ABCD_BCD&ABC"; run;
proc export data=sashelp.class outfile="&wdir\t4.xlsx"; sheet="Acct_Num&ABCD_BCDandABC"; run;

Please provide more details.

Note that you'll get warnings such as

if you use double quotes.

To avoid these warnings, use single quotes when you don't want to resolve macro variables.

Re: ODS Excel Sheet Name length limit reduced when using & in sheet name

arjunsingh3993 — Thu, 23 Feb 2023 05:11:50 GMT

Thanks for replying ChrisNZ,

Proc export does work but I want to use the functionality of ODS Excel (as I want to format output with title etc.) like in below code.

ods excel file="path/Report.xlsx";

ods excel options( sheet_name = 'Acct_Num&ABCD_BCD&ABC');

title1 "Report Monday";

proc report data=sashelp.cars;

column _all_;

run;

title;

ods excel close;

In above code sheet name is getting trimmed to 'Acct_Num&ABCD_BCD&AB'.(20 Characters)

If I use 'Acct_NumandABCD_BCDandABC' (25 Characters) is not getting trimmed.

Need to know the reason behind this, Or any possible solution. Thanks.

Re: ODS Excel Sheet Name length limit reduced when using & in sheet name

Tom — Thu, 23 Feb 2023 19:36:37 GMT

With my version of SAS (9.4M5) and Excel () the limit seems to actually be 28 bytes.

But it does seem to shorten by 4 bytes for every &.

So for every & you add to the name you need to chop four off the length of the sheet name.

ods excel file="%sysfunc(pathname(work))test.xlsx";
ods excel options (sheet_name="a234567890b2345678901234567890") ;
proc print data=sashelp.class;
run;
ods excel options (sheet_name="a&34567890b2345678901234567890") ;
proc print data=sashelp.class;
run;
ods excel options (sheet_name="a&3&567890b2345678901234567890") ;
proc print data=sashelp.class;
run;
ods excel options (sheet_name="a&3&5&7890b2345678901234567890") ;
proc print data=sashelp.class;
run;
ods excel close;

It is probably actually writing the HTML tag & instead of just & so it needs 5 bytes instead of 1.

I am not sure if this is a feature of EXCEL or just how ODS EXCEL is generating the sheet name when it writes the file. I suspect the latter since Excel will let me rename to sheet to something that is 31 bytes long.

The other 3 bytes are probably eaten by quotes and possible $ that sometimes appears in sheet references.

Re: ODS Excel Sheet Name length limit reduced when using & in sheet name

ChrisNZ — Fri, 24 Feb 2023 02:12:37 GMT

I'd ask tech support about this.

They'll probably say using & instead of & is a feature, but it looks like a defect to me as it restricts name lengths.

Tab name length doesn't seem to be byte-restricted either as these tab names are valid (using an English version of Windows).

So there really is no excuse for the restriction SAS imposes. Ask them to register it as a defect,