The SAS Output Delivery System and reporting techniques

ODS Tagsets ExcelXP Creating Multiple Workbooks

Accepted Solution Solved
Reply
Contributor
Posts: 46
Accepted Solution

ODS Tagsets ExcelXP Creating Multiple Workbooks

Hi All,

 

I am trying to create excel with multiple sheets and also i want index as Table of contents in one first sheet.For that, i have used contents='yes' option but i am getting sheet name as Table of contents .

 

I need Title of each sheet as Table of contents in first sheet .

 

Can anyone please help if there is any option to display title in first sheet as hyperlink.

 

Regards,

Rajesh


Accepted Solutions
Solution
‎11-27-2017 10:17 AM
SAS Super FREQ
Posts: 322

Re: ODS Tagsets ExcelXP Creating Multiple Workbooks

That is an older version of the tagset, and might be the cause of the problem.  The latest version is 1.131, dated 04/23/2015.

 

This SAS Note provides information on how to update the tagset on your system:

 

Usage Note 32394: Installing and Storing Updated Tagsets for ODS MARKUP
http://support.sas.com/kb/32/394.html

 

Here is a direct link to the tagset code:

 

http://support.sas.com/rnd/base/ods/odsmarkup/excltags.tpl

 

After the update, rerun your code, making sure that the newer version of the tagset is being used.

 

Vince DelGobbo

SAS R&D

View solution in original post


All Replies
Super User
Posts: 20,731

Re: ODS Tagsets ExcelXP Creating Multiple Workbooks

Anything ODS TAGSETS related can be found here:

https://vasug.files.wordpress.com/2011/07/excelxppaperindex.pdf

 

Use the index to find the relevant paper with the example, in this case, this paper:

It's a much more complicated example here:

https://vasug.files.wordpress.com/2011/07/excelxppaperindex.pdf

Contributor
Posts: 46

Re: ODS Tagsets ExcelXP Creating Multiple Workbooks

Hi ,

Could not find the option which I N looking to customise contents worksheet.
Super User
Posts: 20,731

Re: ODS Tagsets ExcelXP Creating Multiple Workbooks

It sounds like you want custom data in the table of contents, ie not the sheet name but some sort of custom title that you've created?


Can you post some sample code that reflects your process?

Contributor
Posts: 46

Re: ODS Tagsets ExcelXP Creating Multiple Workbooks

Yes Reeza,

 

Below is my code what i tried and what i am getting in screenshot.

 

**Output Excel file**;
ods Tagsets.ExcelXP style=sasweb
file='C:\Users\desktop\Table.xml'
options(orientation='landscape' sheet_name="Table_1" embedded_titles='yes' contents='yes' index='yes');
title justify=left "TABLE 1: Details" ;


proc report data=work.Table_1 split='*' headskip spanrows spacing=0 nowd;
column srt param _freq_ ;
define srt/display order=data width=60 "order"
style(header)=[just=left] ;
define param/display order=data width=60 "DEtail"
style(header)=[just=left] ;
define _freq_/display order=data width=60 "Count"
style(header)=[just=left] ;

run;
ods Tagsets.ExcelXP close;

 

My requirement as below.

1)Remove default titles in contents worksheet and those titles are "Report" and "Detailed and/or summarized report".

2)Provide customised title1 and title2.

3)Customise the name of Table 1 to "Table 1-details".

 

Thanks in advance.

Super User
Posts: 20,731

Re: ODS Tagsets ExcelXP Creating Multiple Workbooks

Ok, so you need to create a table with those names and then build the links to the sheets. The paper I linked to had an example of how to do that. You can't use the default ToC because it doesn't do that AFAIK. It's page 6 in here:

http://support.sas.com/rnd/papers/sgf07/sgf2007-excel.pdf

Code is here:

http://support.sas.com/rnd/papers/index.html#excel2007

 

 I'll move your post to the reporting forum for the reporting gurus to comment as well. 

SAS Super FREQ
Posts: 322

Re: ODS Tagsets ExcelXP Creating Multiple Workbooks

I think @Reeza has provided the correct solution.  If you want a customized table of contents then it's best that you construct it yourself using the techniques in the referenced paper (see page 11).

 

An updated version of the referenced ExcelXP Tagset Paper Index and links to the papers is here:

 

http://www.sas.com/reg/gen/corp/867226?page=Resources

 

Vince DelGobbo

SAS R&D

 

Contributor
Posts: 46

Re: ODS Tagsets ExcelXP Creating Multiple Workbooks

Posted in reply to Vince_SAS
Hi Thanks for sharing the workaround.

I tried it and encountered another issue.

Below what i tried.

Created new dataset with custom index that i need in contents page.

Created new worksheet with sheet_name="Index" for contents page and build
the url.for next worksheet i used sheet_name="Table_1" but another sheet
created with name as "Index 2" due to which url stands invalid.

Can you help how can i avoid auto naming convention and force sas to take
only sheet name which i provide.
Super User
Posts: 20,731

Re: ODS Tagsets ExcelXP Creating Multiple Workbooks

We can't see your code so making guesses here, but did you remove the ToC options for the ODS TAGSETS.EXCELXP statement?

SAS Super FREQ
Posts: 322

Re: ODS Tagsets ExcelXP Creating Multiple Workbooks

Here is a simple example to help get you started.

 

ods _all_ close;

data work.toc;
length toc_text $20 worksheet_name $6; 
toc_text = 'Female Student Data'; worksheet_name='Female'; output;
toc_text = 'Male Student Data';   worksheet_name='Male';   output;
run;

title;
footnote;

ods tagsets.ExcelXP file='C:\temp\temp.xml' style=sansPrinter;

ods tagsets.ExcelXP options(sheet_interval='none'
                            sheet_name='Contents');

proc report data=work.toc;
  column toc_text worksheet_name;
  define toc_text       / display 'Table of Contents';
  define worksheet_name / noprint;
  compute worksheet_name;
    call define('toc_text',
                'url',
                "#'" || strip(worksheet_name) || "'!A1");
  endcomp;
run;quit;

ods tagsets.ExcelXP options(sheet_interval='proc');

ods tagsets.ExcelXP options(sheet_name='Female');

proc print data=sashelp.class;
  where (sex eq 'F');
run; quit;

ods tagsets.ExcelXP options(sheet_name='Male');

proc print data=sashelp.class;
  where (sex eq 'M');
run; quit;

ods tagsets.ExcelXP close;

Vince DelGobbo

SAS R&D

Contributor
Posts: 46

Re: ODS Tagsets ExcelXP Creating Multiple Workbooks

Posted in reply to Vince_SAS

Hi Vince,

 

Thanks for workaround.

 

 

I did it and everything works as expected.

 

The below two issues need further help.

 

1) Title is appearing truncated 

2) Some of the values in column also appearing truncated.

 

For title, i have used title justify=left option , but that has not solved my issue.

 

For truncated appearance of value in column, i am sure that by increasing cellwidth , it can be resolved , but just for one value i do not want column to look much wider.

 

Can you please help me on same.

 

Below is code.

 

options(orientation='landscape' sheet_name="Table_1" embedded_titles='yes' embedded_footnotes='yes' embed_titles_once='yes'
absolute_column_width="39,10" autofi_height='yes');
title justify=left "TABLE 1: Demography of all people living in one country at date  DD-MMM-YY" ;
footnote link="#index!A1" "Return to Contents";

proc report data=Table_1 split='*' headskip spanrows spacing=0 nowd style(header)=[fontweight=bold ];
column srt param _freq_ ;
define srt/noprint group order=data;
define param/display order=data style(column)=[cellwidth=400 just=left] "DEMOGRAPHIC"
style(header)=[just=left] ;
define _freq_/display order=data "#"
style(header)=[just=center] style(column)=[cellwidth=100 just=center] ;

compute param;
if param in ("RACE" ,"ETHNICITY" ,"INTERVAL OF TIME BETWEEN FIRST ASSESSMENT AND SECOND ASSESSMENT"  ) then
call define (_col_,'style','style=[font_weight=bold just=left]');
endcomp;
run;

 

In above code, you can find that one of the value "INTERVAL OF TIME BETWEEN FIRST ASSESSMENT AND SECOND ASSESSMENT" is longest one in that particular column which appear truncated.

SAS Super FREQ
Posts: 322

Re: ODS Tagsets ExcelXP Creating Multiple Workbooks

What version of SAS are you using, and what version of the tagset are you using?  The tagset version is printed in the log when you open the destination.  

 

It's difficult to diagnose this type of problem without having the data.  Can you post sample data?  

 

I tried to create sample data but could not reproduce the problem.  Can you run this coded on your system to see if it reproduces the problem?

 

 

ods _all_ close;

data work.toc;
length toc_text $20 worksheet_name $7; 
toc_text = 'Female Patient Data'; worksheet_name='Table_1'; output;
toc_text = 'Male Patient Data';   worksheet_name='Table_2'; output;
run;

data work.table_1;
set sashelp.class;
length param $64;
if (age eq 14) then      param = 'RACE';
else if (age eq 15) then param = 'ETHNICITY';
else if (age eq 16) then param = 'INTERVAL OF TIME BETWEEN FIRST ASSESSMENT AND SECOND ASSESSMENT';
else param = name;
run;

title justify=left 'TABLE 1: Demography of all people living in one country at date  DD-MMM-YY';
footnote link='#index!A1' 'Return to Contents';

ods tagsets.ExcelXP file='C:\temp\temp.xml' style=sansPrinter;

ods tagsets.ExcelXP options(sheet_interval='none'
                            sheet_name='Index');

proc report data=work.toc;
  column toc_text worksheet_name;
  define toc_text       / display 'Contents';
  define worksheet_name / noprint;
  compute worksheet_name;
    call define('toc_text',
                'url',
                "#'" || strip(worksheet_name) || "'!A1");
  endcomp;
run;quit;

ods tagsets.ExcelXP options(sheet_interval='proc');

ods tagsets.ExcelXP options(sheet_name='Table_1'
                            orientation='landscape' 
                            embedded_titles='yes' 
                            embedded_footnotes='yes' 
                            embed_titles_once='yes'
                            absolute_column_width='39,10' 
                            autofit_height='yes');

proc report data=work.table_1 split='*' headskip spanrows spacing=0 nowd 
  style(header)=[fontweight=bold];
  where (sex eq 'F');
  column weight param age;

  define weight / noprint group order=data;

  define param  / display order=data 
                  style(column)=[cellwidth=400 just=left] 'DEMOGRAPHIC'
                  style(header)=[just=left] ;

  define age    / display order=data "#"
                  style(header)=[just=center] 
                  style(column)=[cellwidth=100 just=center];
  compute param;
    if param in ('RACE', 'ETHNICITY' ,
                 'INTERVAL OF TIME BETWEEN FIRST ASSESSMENT AND SECOND ASSESSMENT') 
    then call define (_col_, 'style', 'style=[font_weight=bold just=left]');
  endcomp;
run; quit;

ods tagsets.ExcelXP options(sheet_name='Table_2');

proc report data=work.table_1 split='*' headskip spanrows spacing=0 nowd 
  style(header)=[fontweight=bold];
  where (sex eq 'M');
  column weight param age;

  define weight / noprint group order=data;

  define param  / display order=data 
                  style(column)=[cellwidth=400 just=left] 'DEMOGRAPHIC'
                  style(header)=[just=left] ;

  define age    / display order=data "#"
                  style(header)=[just=center] 
                  style(column)=[cellwidth=100 just=center];
  compute param;
    if param in ('RACE', 'ETHNICITY' ,
                 'INTERVAL OF TIME BETWEEN FIRST ASSESSMENT AND SECOND ASSESSMENT') 
    then call define (_col_, 'style', 'style=[font_weight=bold just=left]');
  endcomp;
run; quit;

ods tagsets.ExcelXP close;

 

Vince DelGobbo

SAS R&D

 

Contributor
Posts: 46

Re: ODS Tagsets ExcelXP Creating Multiple Workbooks

Posted in reply to Vince_SAS

Hi,

 

Thanks for workaround , but strange that it is not working on actual data and work on data that you created.

 

Here is some data where i face issue and on title issue, it remains as it is.

 

Race

  Asian

  American

  White

 

INTERVAL OF TIME BETWEEN FIRST ASSESSMENT  AND SECOND(Years)

 

 <2

 2-5

 >5

 

I tried to set absolute column width as 42 but still no luck.

 

One more issue i have seen that although  i have inserted spaces for values Indian,american and white, the same are not apperaing in excel as in sas dataset.Is it excel feature to remove blank spaces or as i can produce rtf report with that alignment ?

 

Thanks in advance.

 

Regards,

Rajesh

 

Contributor
Posts: 46

Re: ODS Tagsets ExcelXP Creating Multiple Workbooks

Posted in reply to Vince_SAS

Tagset version:

This is the Excel XP tagset (Compatible with SAS 9.1.3 and above, v1.94, 09/09/12 ).

Solution
‎11-27-2017 10:17 AM
SAS Super FREQ
Posts: 322

Re: ODS Tagsets ExcelXP Creating Multiple Workbooks

That is an older version of the tagset, and might be the cause of the problem.  The latest version is 1.131, dated 04/23/2015.

 

This SAS Note provides information on how to update the tagset on your system:

 

Usage Note 32394: Installing and Storing Updated Tagsets for ODS MARKUP
http://support.sas.com/kb/32/394.html

 

Here is a direct link to the tagset code:

 

http://support.sas.com/rnd/base/ods/odsmarkup/excltags.tpl

 

After the update, rerun your code, making sure that the newer version of the tagset is being used.

 

Vince DelGobbo

SAS R&D

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 15 replies
  • 757 views
  • 8 likes
  • 3 in conversation