BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
BenConner
Pyrite | Level 9


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

1 ACCEPTED SOLUTION

Accepted Solutions
9 REPLIES 9
Reeza
Super User

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

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

Cynthia_sas
SAS Super FREQ

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;

BenConner
Pyrite | Level 9

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

Reeza
Super User

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

BenConner
Pyrite | Level 9

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

Reeza
Super User

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.

Cynthia_sas
SAS Super FREQ

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;


xp_sheet_names.png
BenConner
Pyrite | Level 9

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

BenConner
Pyrite | Level 9

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 1475 views
  • 6 likes
  • 3 in conversation