The SAS Output Delivery System and reporting techniques

Getting rid of number in sheet_name #byval and Left title

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 16
Accepted Solution

Getting rid of number in sheet_name #byval and Left title

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;


Accepted Solutions
Solution
‎11-15-2013 06:20 PM
Grand Advisor
Posts: 16,297

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

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

View solution in original post


All Replies
Solution
‎11-15-2013 06:20 PM
Grand Advisor
Posts: 16,297

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

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

Occasional Contributor
Posts: 16

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

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

 

Post a Question
Discussion Stats
  • 2 replies
  • 636 views
  • 1 like
  • 2 in conversation