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
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
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
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?
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.
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.
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
We can't see your code so making guesses here, but did you remove the ToC options for the ODS TAGSETS.EXCELXP statement?
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
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.
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
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
Tagset version:
This is the Excel XP tagset (Compatible with SAS 9.1.3 and above, v1.94, 09/09/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
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.