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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Vince_SAS
Rhodochrosite | Level 12

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

15 REPLIES 15
Reeza
Super User

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

draroda
Fluorite | Level 6
Hi ,

Could not find the option which I N looking to customise contents worksheet.
Reeza
Super User

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?

draroda
Fluorite | Level 6

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.

Reeza
Super User

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. 

Vince_SAS
Rhodochrosite | Level 12

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

 

draroda
Fluorite | Level 6
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.
Reeza
Super User

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

Vince_SAS
Rhodochrosite | Level 12

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

draroda
Fluorite | Level 6

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.

Vince_SAS
Rhodochrosite | Level 12

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

 

draroda
Fluorite | Level 6

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

 

draroda
Fluorite | Level 6

Tagset version:

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

Vince_SAS
Rhodochrosite | Level 12

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

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
  • 15 replies
  • 2375 views
  • 8 likes
  • 3 in conversation