The SAS Output Delivery System and reporting techniques

ExcelXP Tagset--Header Splits and Title Spacing

Reply
Contributor
Posts: 21

ExcelXP Tagset--Header Splits and Title Spacing

1 When using the Split='/' option in Proc Print, the resulting cell in Excel has been height adjusted as if it knows there are two lines, but instead of splitting the text, it is all on one line with a non-printing character on screen. If I double click the cell, it splits the lines, but I obviously don't want to do that.

2 With multiple titles, a blank line is inserted between each title in the Header section of the spreadsheet (undesired). Then, no blank lines between the 2nd title and the headers (would prefer blank line). Tried to use the Skip_Space option, but that didn't help.

TIA for any assistance...I'm getting *so* close!
SAS Employee
Posts: 22

Re: ExcelXP Tagset--Header Splits and Title Spacing

The updated tagset should take care of both of these issues for you. You can download it from here:

http://support.sas.com/rnd/base/topics/odsmarkup/
Contributor
Posts: 21

Re: ExcelXP Tagset--Header Splits and Title Spacing

Sorry, I should have specified that I am using the latest Tagset from June 2006.
SAS Employee
Posts: 22

Re: ExcelXP Tagset--Header Splits and Title Spacing

The following sample code worked for me. The label is split appropriately. The TITLEs do not have blank lines in between and there is a blank line between TITLE2 and the beginning of the table.

ods listing close;
ods tagsets.excelxp file='test.xls'
options(embedded_titles='yes');

proc print data=sashelp.class noobs split='/';
title1 'one';
title2 'two';
label age='Age in/years';
run;

ods listing;
ods tagsets.excelxp close;

If you continue to have problems, I would suggest opening a tracking entry with Technical Support by sending your log and a sample of the output to support@sas.com.
Contributor
Posts: 21

Re: ExcelXP Tagset--Header Splits and Title Spacing

Thanks for the code; I was able to get the split labels working, but the problem with the titles is actually in the Header Section. (using the default behavior of "embedded_titles='no'" to force the title statements to appear in the Header section of Excel).

It looks like two hard returns are inserted between each title statement, and if there are more than 2 titles, the data overwrites the third title statement.

I tried to use the Print_Header command, but it didn't work for me.
SAS Employee
Posts: 95

Re: ExcelXP Tagset--Header Splits and Title Spacing

There was actually only one newline at the end of each title. But Excel was interpreting it as a double space.
replacing the newline with an causes the spacing to go away. I have an updated tagset that will be available on the web soon.

if you want to try it yourself, look for the title_data event in the tagset. Case insensitive earch for
"event title_data". This is what it looks like.
Sorry I couldn't get the indention to take.

define event title_data;

do /if $not_first;
put nl;
else;
putq " ssSmiley FrustratedtyleID=" lowcase(htmlclass);
putq ' Data="';
done;

Change the nl in the put to an " ";


define event title_data;

do /if $not_first;
put " ";
else;
putq " ssSmiley FrustratedtyleID=" lowcase(htmlclass);
putq ' Data="';
done;
SAS Employee
Posts: 174

Re: ExcelXP Tagset--Header Splits and Title Spacing

If you want to preserve indentation in SAS code, use the PRE and /PRE tags (enclosed in square brackets. Without PRE you have:

proc tabulate data=sashelp.class;
class sex age;
var height;
table sex,age*height;
run;

With PRE you have:

[pre]
proc tabulate data=sashelp.class;
class sex age;
var height;
table sex,age*height;
run;
[/pre]

-- David Kelley, SAS
Contributor
Posts: 21

Re: ExcelXP Tagset--Header Splits and Title Spacing

Thanks...I modified my template, and it worked as desired.

One last spacing issue...I have 3 Title Statements, and I would like a blank like between the Header (the 3rd Title) and the Body of the Report.

Is this something I can do via in-line formatting (perhaps a well-placed " " in my code?) or will it require a tweak to the actual template?
SAS Employee
Posts: 95

Re: ExcelXP Tagset--Header Splits and Title Spacing

Just add a blank title4.

title4 ' ';
Contributor
Posts: 21

Re: ExcelXP Tagset--Header Splits and Title Spacing

It looks like 3 is the maximum number of titles that will fit in the header, so

title4 ' ';

does not do anything. I reconfigured the titles into two lines, and then used

title3 ' ';

successfully.

Thanks!
Ask a Question
Discussion stats
  • 9 replies
  • 374 views
  • 0 likes
  • 4 in conversation