The SAS Output Delivery System and reporting techniques

Suppressing the var= prefix when using ExcelXP and bygroup processing

Accepted Solution Solved
Reply
Regular Contributor
Posts: 150
Accepted Solution

Suppressing the var= prefix when using ExcelXP and bygroup processing


Hi,

I'm getting great results with the sheet-interval='bygroup' in generating separate tabs in Excel; the only hitch I'm struggling with is it includes the <variable name>= in front of each value.  I'd like it just to use the value itself as the tab name, as I have a table of contents with links to the different tab values.

Is that possible?

SAS vsn 9.2, Excel XP tagset v1.94, 09/09/12.

Thanks much!

--Ben


Accepted Solutions
Solution
‎09-16-2013 06:23 PM
Grand Advisor
Posts: 16,334

Re: Suppressing the var= prefix when using ExcelXP and bygroup processing

Try Sheet_Label="    " option added in to your tagset options.

See page 14 here: http://support.sas.com/resources/papers/proceedings11/170-2011.pdf

View solution in original post


All Replies
Solution
‎09-16-2013 06:23 PM
Grand Advisor
Posts: 16,334

Re: Suppressing the var= prefix when using ExcelXP and bygroup processing

Try Sheet_Label="    " option added in to your tagset options.

See page 14 here: http://support.sas.com/resources/papers/proceedings11/170-2011.pdf

SAS Super FREQ
Posts: 8,644

Re: Suppressing the var= prefix when using ExcelXP and bygroup processing

Hi:

  There are suboptions to do what you want. Use doc='Help' in your suboption list to investigate the suppress_bylines suboption.

cynthia
 
ods _all_ close;
ods tagsets.excelxp style=sasweb
    file='c:\temp\by_options.xml'
    options(suppress_bylines='yes' sheet_name='#byval1' doc='Help');

  

...your procedure with BY statement...

...like BY AGE; ...

   

ods _all_ close;

Regular Contributor
Posts: 150

Re: Suppressing the var= prefix when using ExcelXP and bygroup processing

Hi Reeza, Cynthia,

These two parameter combinations worked great.  So I had to break them. Smiley Happy  I uncommented a preceeding proc report step that has a hard-coded sheet name called 'Contents' where it generates a table of contents.  This resulted in the sucessive sheet names being called Contents2, Contents3, etc., ignoring the sheet_label and suppress_bylines values.  ??

I suppose I could generate the table of contents as the last sheet, then manually move it to the beginning of the list.  Would like to avoid that if I could, though.

Thanks!

--Ben

Grand Advisor
Posts: 16,334

Re: Suppressing the var= prefix when using ExcelXP and bygroup processing

Have you tried resetting the options after the proc report? I think with some tinkering you should be able to get it.

There's also an option for a TOC from tagests, though its probably not as nice as the one you're making from proc report Smiley Happy

ods tagsets.excelxp options (Sheet_name="Contents");

****proc report code here****;

ods tagsets.excelxp options (Sheet_interval="proper value" Sheet_label=" "  sheet_name=); *you'll need to set these appropriately;

****other code here****;

Regular Contributor
Posts: 150

Re: Suppressing the var= prefix when using ExcelXP and bygroup processing

Hi Reeza,

Didn't know there was TOC tagset functionality.  Inherited this code, so it may not have existed when it was written.  Will take a look at it.  As it is, the suggestions you and Cynthia made eliminated the need for macro looping, saving a ton of time and several hundred pages of log files.

Much appreciated!

--Ben

Grand Advisor
Posts: 16,334

Re: Suppressing the var= prefix when using ExcelXP and bygroup processing

Base SAS: Quick Reference for TAGSETS.EXCELXP Tagset

See the Contents option.

The link is also a good reference in the future, besides the doc=help as Cynthia suggested.

SAS Super FREQ
Posts: 8,644

Re: Suppressing the var= prefix when using ExcelXP and bygroup processing

Hi:

  At this point, you've received several code examples and suggestions and shared none of your actual code. To correctly debug your issue and figure out a workaround, I would recommend that you open a track with Tech Support for specific help with your EXACT code. I suspect that you are not correctly switching suboptions between steps. I cannot replicate what you describe using SAS 9.3, Excel 2013, which is why I recommend working with Tech Support. Otherwise, it is all guesswork about how your code "broke" the sheet_names. See the attached screenshot...I was able to get the first sheet named "Contents" and the other sheets named with their BY group values. It would not be appropriate to put the byline-related suboptions in the invocation statement that precedes your PROC REPORT step for the contents.

cynthia

 
ods _all_ close;
ods tagsets.excelxp style=sasweb
    file='c:\temp\by_options2.xml'
    options(sheet_name='Contents');


** first proc report step with contents info;

ods tagsets.excelxp  options(suppress_bylines='yes' sheet_name='#byval1');
** second proc with BY statement;

 

ods _all_ close;

Attachment
Regular Contributor
Posts: 150

Re: Suppressing the var= prefix when using ExcelXP and bygroup processing

Hi Cynthia,

Sorry, left work several hours ago and don't have the code with me now.  I'll post it tomorrow morning when I get in.  Didn't mean to offend anyone.

--Ben

Regular Contributor
Posts: 150

Re: Suppressing the var= prefix when using ExcelXP and bygroup processing

Hi Cynthia,

Apparently the security restrictions on the system I work on prevent me from pasting into a web browser.  I'll retype the code I'm using, omitting the details within the proc reports...

ods tagsets.ExcelXP close;

ods tagsets.ExcelXP

  options(embedded_titles='yes'  embedded_footnotes='no' width_fudge='0.75' center_horizontal='yes' autofit_height='yes' skip_space='0' row_repeat='2' row_heights='47, 15, 15, 15, 15, 15, 15' pagebreaks='no' scale='85')

  file='test.xml' style=journal;

* Table of Contents with Hyperlinks;

ods tagsets.ExcelXP

   options(sheet_interval='none' sheet_name="Contents" default_column_width="9, 40, 9");

proc report data=user.nbr_vendors nowindows;

  column vendor_id organization_name vendor_type;

  <series of define statements>;

  compute vendor_id;

      vend_ref="#'"||trim(left(vendor_id))||"'!A1";

      call define(_col_,'URL',vend_ref);

   endcomp;

run;

ods escapechar='^';

ods noproctitle noptitle;

options byline;

ods tagsets.ExcelXP

   options(sheet_name=" " sheet_label=" " suppress_bylines="yes" sheet_interval="bygroup" default_column_width="25,9, 6, 6, 6, 6, 6, 6");

title '#byval1 -- #byval2';

proc report data=detail nowindows;

  by vendor_id organization name;

   column question response pct1 pct2 pct3 pct4 us_pct1 us_pct2;

   <series of define statements>;

   run;

   quit;

ods tagsets.ExcelXP close;

Is there anything after the first proc report I can add to reset the sheet name without closing the xml file creation?

Thanks!

--Ben

Post a Question
Discussion Stats
  • 9 replies
  • 646 views
  • 6 likes
  • 3 in conversation