topic Re: Suppressing the var= prefix when using ExcelXP and bygroup processing in ODS and Base Reporting

Suppressing the var= prefix when using ExcelXP and bygroup processing

BenConner — Mon, 16 Sep 2013 22:13:19 GMT

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

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

Reeza — Mon, 16 Sep 2013 22:23:35 GMT

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

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

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

Cynthia_sas — Mon, 16 Sep 2013 22:36:30 GMT

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;

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

BenConner — Mon, 16 Sep 2013 23:00:18 GMT

Hi Reeza, Cynthia,

These two parameter combinations worked great. So I had to break them. 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

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

Reeza — Mon, 16 Sep 2013 23:19:28 GMT

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

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****;

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

Cynthia_sas — Tue, 17 Sep 2013 01:29:59 GMT

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;

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

BenConner — Tue, 17 Sep 2013 02:24:36 GMT

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

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

BenConner — Tue, 17 Sep 2013 15:11:44 GMT

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

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

BenConner — Tue, 17 Sep 2013 18:07:09 GMT

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

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

Reeza — Tue, 17 Sep 2013 18:14:36 GMT

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.