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
Try Sheet_Label=" " option added in to your tagset options.
See page 14 here: http://support.sas.com/resources/papers/proceedings11/170-2011.pdf
Try Sheet_Label=" " option added in to your tagset options.
See page 14 here: http://support.sas.com/resources/papers/proceedings11/170-2011.pdf
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;
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
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****;
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
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.
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;
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
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.