topic Getting rid of number in sheet_name #byval and Left title in ODS and Base Reporting

Getting rid of number in sheet_name #byval and Left title

NewSASPerson — Fri, 15 Nov 2013 22:39:44 GMT

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;

Re: Getting rid of number in sheet_name #byval and Left title

Reeza — Fri, 15 Nov 2013 23:20:31 GMT

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

Re: Getting rid of number in sheet_name #byval and Left title

NewSASPerson — Sun, 17 Nov 2013 01:05:20 GMT

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)";