BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
arjunsingh3993
Calcite | Level 5

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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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;

Tom_0-1677130241604.png

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.

Tom_1-1677130467562.png

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

View solution in original post

4 REPLIES 4
ChrisNZ
Tourmaline | Level 20

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;

ChrisNZ_0-1677118166238.png

Please provide more details.

 

Note that you'll get warnings such as

ChrisNZ_1-1677118226796.png

if you use double quotes.

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

 

arjunsingh3993
Calcite | Level 5

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.

Tom
Super User Tom
Super User

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;

Tom_0-1677130241604.png

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.

Tom_1-1677130467562.png

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

ChrisNZ
Tourmaline | Level 20

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).

ChrisNZ_0-1677204677954.png

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

 

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 1404 views
  • 1 like
  • 3 in conversation