BookmarkSubscribeRSS Feed
William
Calcite | Level 5
I am pretty new to ExcelXP, using ExcelXP to create a Excel file. Somehow, when the titles are very long, only partial be displayed.

Below is a example
ods listing close;
ods tagsets.ExcelXP file="test.xls";
ods tagsets.ExcelXP options(sheet_name="test" EMBEDDED_TITLES='yes');
title1 "title1 title1 title1 title1 title1 title1 title1 title1 title1 title1";
title2 "title2 title2 title2 title2 title2 title2 title2";
proc print data=sashelp.class noobs;
var age;
var name;
run;
ods tagsets.ExcelXP close;
ods listing;

After open the file in Excel, the title rows look below
title1 title1 title1
title2 title2 title2
(first 2 cells be merged, and wrapped)

My expected result would be like
title1 title1 title1
title1 title1 title1
title1 title1 title1
title1
title2 title2 title2
title2 title2 title2
title2
(besides merging and wrapping, I hope the heights should be big enough to display whole titles)

Thanks in advance for any help or suggestion.
William
3 REPLIES 3
Cynthia_sas
SAS Super FREQ
Hi:
It's been my experience that wrapped text for titles when using TAGSETS.EXCELXP does -not- happen automatically. When you use the EMBEDDED_TITLES suboption, I believe the behavior is to merge the cells across the top of the table and put the embedded title text into that merged cell. With only 2 variables in your table, there just wasn't enough real estate for the title text to show. If you had more variables, or explicitly made the columns wider, then you'd see the whole text. And, of course, if you make the table wide enough so you can see the whole title string, then there's no need to wrap text, because it all fits.

Consider the difference in output from the programs below, with step 1, where the output has only 2 variables and absolute_column_width is explicitly set; and step 2, where the output has 10 variables and no column width set. As you can see, the more variables you have, the wider the table and the more the title statement is revealed.

There are some row height adjustment options in the ExcelXP tagset, which you can reveal by using the doc='Help' sub-option. However, I'm not sure of the interaction between column width and row height sub-options or how you'd make them work together -and- if you did get them to work together, whether you'd get wrapping the way you want.

You might wish to open a track with Tech Support to explore the best way to accomplish what you are trying to do.

cynthia

[pre]
ods listing close;
** step1;
ods tagsets.ExcelXP file="c:\temp\test1.xml"
options(doc='Help' sheet_name="test" EMBEDDED_TITLES='yes'
absolute_column_width='35') style=sasweb;
title1 "title1 word1 word2 word3 word4 word5 word6 word7 word8 word9";
title2 "title2 word1 word2 word3 word4 word5 word6 word7";
proc print data=sashelp.class noobs;
var age name;
run;
ods tagsets.excelxp close;

** step 2;
ods tagsets.ExcelXP file="c:\temp\test2.xml"
options(doc='Help' sheet_name="test" EMBEDDED_TITLES='yes')
style=sasweb;

title1 "title1 word1 word2 word3 word4 word5 word6 word7 word8 word9";
title2 "title2 word1 word2 word3 word4 word5 word6 word7";
proc print data=sashelp.class noobs;
var age name height weight sex age name height weight sex;
run;

ods tagsets.ExcelXP close;
ods listing;
[/pre]
William
Calcite | Level 5
thanks Cynthia.

Actually I tried a couple options, like Row_Heights and Autofit_height, not successful.

1. Autofit_height:yes
never works

As mentioned in the help doc
Autofit_height: Default Value 'no'
Values: yes, no
If yes no row heights will be specified. This allows the auto fit height
of Excel to do it's job, sometimes not so well.


2. Row_Heights
it works. BUT for all the titles (here title1 and title2) share one setting, so in my case, I need different settings for title1 and title2.

Anyway, I will open a track tomorrow
William
EllieBelle
Calcite | Level 5

Hi William,

I am having the same problem that you encountered in 2009.  Were you able to resolve it?

Thanks!

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

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
  • 3 replies
  • 2136 views
  • 0 likes
  • 3 in conversation