topic Re: Excel sheet name length limit in ODS and Base Reporting

Excel sheet name length limit

coladuck — Fri, 17 Apr 2020 17:29:56 GMT

Hi,

I have a question on sheet name been truncated in ODS EXCEL output. I saw the ODS Sheet_name has a limitation of 28 characters but Excel sheet name allow more. For example, if I use proc export, the sheet name will output fine. But because the percentage format is not kept in proc export, I had to rely on ODS EXCEL.

Is there a way to get around it, meaning keeping the full sheet name?

Here is my simple code:

ods excel file=&outputexcel; 
ods excel options(sheet_label = "XXXXXXXXX Tracking Data Summary"); run; 
proc report data=mydata;
run;
ods excel close;

and the output Excel Tab will be "XXXXXXXXX Tracking Data Summ"

Thanks in advance!!

Re: Excel sheet name length limit

Kurt_Bremser — Fri, 17 Apr 2020 19:49:49 GMT

sheet_label and sheet_name are concatenated to build the final sheet name in the workbook (if you specify sheet_interval as "bygroup", the final sheet name will be built from sheet_label and the group value).

Together they are limited by the 28 character limit. The Excel GUI allows to enter 31 characters, while the Excel file format seems to allow 255, as I have found while researching this (Maxim 6: Google Is Your Friend).

Re: Excel sheet name length limit

Tom — Fri, 17 Apr 2020 20:03:17 GMT

You might need to ask SAS technical support.

I suspect it is losing the extra three characters because of some issue with the 'sheetname$'n syntax.

https://support.sas.com/kb/16/176.html

The quotes and dollar sign add up to the missing 3 characters.

Re: Excel sheet name length limit

Cynthia_sas — Sun, 19 Apr 2020 00:17:39 GMT

Hi: You are correct. Excel allows 31 characters. ODS EXCEL allows the sheet names to be 28 characters long because part of how ODS works is that if it needs to append a number to the end of your name, it needs to reserve some extra characters for numbering as shown below:

Cynthia