Getting rid of number in sheet_name #byval and Left title

[NewSASPerson]

I am trying to use #byval to split by data into multiple worksheets... it works fine except there's a '2' tagged along with each sheet name except for the first sheet. The first sheet is fine.

e.g Sheet1 = 'ALL',

Sheet2='Gucci 2'

Sheer3 = 'Jimmy Choo 2'

Sheet4 = 'Prada 2'

Sheet5 = 'Valentino 2'

I can't seem to figure out what the issue is. Please see my code below. Any insight would be helpful.!

Also, is it possible for me to align my title to the left, I tried using the tagsets options merge_titles_footnotes='no' but that doesn't seem to work?

Thanks in advance!

/*code*/

proc sort data=designer_shoes_Sales out=designersales;

  by brand;

run;

ods _all_ close;

ods tagsets.excelxp

  file= "/servloc/myfolder/designer_Sales.xls"

  options (

  autofit_height = 'yes'

  row_repeat = '5'

  Wraptext = 'no'

merge_titles_footnotes='no'

  frozen_headers = '5'

embedded_titles= 'yes'

  Orientation = 'landscape'

  sheet_interval = 'bygroup'

  suppress_bylines = 'yes'

  sheet_name='#byval(brand)');

PROC report data=designersales;

  Title1 "Sales Report";

  Title2  "January - October 2010"

  Title3 "Designer Name: #byval(brand)";

  by brand;

  define Total/ format=comma9.;

  ;

run;

ods _all_ close;

[Reeza · Accepted solution]

Change your options to remove sheet_name and add in sheet_label instead, with a blank space between the quotes.

  options (

  autofit_height = 'yes'

  row_repeat = '5'

Wraptext = 'no'

  merge_titles_footnotes='no'

  frozen_headers = '5'

embedded_titles= 'yes'

  Orientation = 'landscape'

  sheet_interval = 'bygroup'

  suppress_bylines = 'yes'

  sheet_label=' '

  );

Here's a good reference on finding the different options in tagsets:

http://www.sas.com/events/cm/867226/ExcelXPPaperIndex.pdf

[NewSASPerson]

Perfect! Thank you so much, it worked!

I figured out the Title issue. These are the changes I made.

  Title1  j=left "Sales Report"

  Title2  j=left "January - October 2010"

  Title3 j=left "Designer Name: #byval(brand)";