formatting excelxp output

[ding_dong]

Hi

Unfortunately, I'm having major problems trying to format output produced using the excelxp tagset. I'm unable to get the titles formatted as per the instructions provided using the in-line statements. Any suggestions would be much appreciated. I've provided the code below.

I'm using the latest version of the tagset and the version of SAS I'm running is 9.1.3.


options nobyline nodate nonumber;

libname rptdata 'e:\documents\data';

ods escapechar='^';

ods noresults;

ods listing close;

proc template;
define style lst;
parent = styles.printer;
style data / background=white
foreground = black
font_face = "arial"
font_size = 12pt
just=center;
style systemtitle / background = red
foreground = white
font_face = "arial"
font_size = 24pt;
style header / font_face = "arial"
font_size = 14pt
foreground = black
background = lightgrey
just=center
vjust=middle;
style Table / frame = box
rules = all
cellpadding = 4pt
cellspacing = 0.25pt
borderwidth = 0.75pt;
end;
run;


/* create table containing distinct combinations of channels and product */
proc SQL;
create table shtlinks as
select distinct
catx(' - ',region,product) as grp
from rptdata.shoes
order by calculated grp;
quit;

proc sort data=rptdata.shoes;
by region product;
run;

ods tagsets.excelxp file='C:\Users\Vojo\Desktop\report2.xls'
style=lst
options(embedded_titles='yes'
Suppress_Bylines='yes'
sheet_label=''
sheet_name='contents' );

proc report data=shtlinks split='#' nowd;
title1 'Shoe Report';
column grp;
define grp / 'Click on the link'
display
style(column)=[cellwidth=1450 foreground=blue];
compute grp;
/* create hyperlinks to the individual sheets in the final excel file */
/* the sheets range from 'Tab' (first sheet) to 'Tab #' (where # is equal to the */
/* total number of sheets */
n+1;
if n=1 then urlstring='#Tab!A1 ';
else urlstring="#'Tab "||strip(put(n,2.))||"'!A1";
call define(_col_, 'URL', urlstring);
endcomp;
run;

ods tagsets.excelxp options(sheet_name='Tab');

proc report data=rptdata.shoes nowd;
title1 '#byval(region) - #byval(product)';
title2 '^S={background=white}';
title3 link="#contents!A1" "^S={foreground=blue background=white font_size=12pt}Return to Contents Page";
column Subsidiary
stores
sales
inventory
returns;
by region product;
define subsidiary / 'Subsidiary' group;
define stores / 'Stores' group;
define sales / 'Sales' analysis sum;
define inventory / 'Inventory' analysis sum;
define returns / 'Returns' analysis sum;
run;
ods tagsets.excelxp close;

title1; title2; title3;

[Cynthia_sas]

Hi:
I believe you can't change the style of the title using ODS ESCAPECHAR...partly because of how Excel sets the style for hyperlinks. However, when I use TITLE statement H= and COLOR= options, my LINKS in the titles do have the right color and font size that I want. By default, my title is Arial, so I did not need to use the FONT= attribute in the title statement. And then, to see the links, I needed to use the EMBEDDED_TITLES suboption for the ExcelXP tagset template.

cynthia

[pre]
title;
proc format ;
value $reglnk 'Asia'= '#Asia!A1'
'Canada' = '#Canada!A1';
run;

ods tagsets.excelxp file='c:\temp\try_hyper_title.xml' style=sasweb
options(sheet_name='Main' embedded_titles='yes');

proc report data=sashelp.shoes nowd ;
title link='#Asia!A1' h=12pt color=purple 'Go to Asia Sheet';
column region product sales;
where region in ('Asia', 'Canada');
define region / group
style(column)={url=$reglnk.};
define product / group;
define sales / sum;
rbreak after / summarize;
run;

ods tagsets.excelxp options(sheet_name='Asia') ;
proc report data=sashelp.shoes nowd ;
title link='#Canada!A1' h=12pt color=purple 'Go To Canada Sheet';
column region product sales;
where region = 'Asia';
define region / display;
define product / display;
define sales / sum;
rbreak after / summarize;
run;

ods tagsets.excelxp options(sheet_name='Canada');

proc report data=sashelp.shoes nowd ;
title link='#Main!A1' h=12pt color=cyan 'Back to Main Sheet';
column region product sales;
where region = 'Canada';
define region / display;
define product / display;
define sales / sum;
rbreak after / summarize;
run;

ods tagsets.excelxp close;
[/pre]

[ding_dong]

Hi Cynthia

Thanks for the speedy response. Is there a way of changing the background colour of titles 2 and 3? I need them to be white. However, SAS sets them to red as per my template. Essentially, I need to temporarily overide the template. Also, the links that I create with call define are not being formatted as per the style statement. I need them blue and underlined and ideally with no gridlines. Any suggestions?

Regards

[ding_dong]

Hi Cynthia

I've given this some more thought and have used the below code to generate a link instead of a title that uses link=. The problem I have with the below is that excel does not seem to respect the second line statement. Any suggestions?

compute before _page_;
line '=HYPERLINK("[C:\Users\Vojo\Desktop\report2.xls]contents!A1","CLICK HERE")';
line '';
endcomp;

[Cynthia_sas]

Hi:
In SAS 9.2, there are some possibilities that may help you. The textdecoration style attribute allows you to specify underline or overline attributes for text and there are also various bordertopstyle, borderbottomstyle, etc attributes that you can set. In addition, you will be able to set one style for a TITLE1 statement, for example, versus a different style element for TITLE2 -- which would be the ideal solution/approach for what you want to do.

If you look at the template and how border changes were implemented for ExcelXP in this paper
http://analytics.ncsu.edu/sesug/2008/CS-050.pdf (starting on page 14, including a discussion of the new STYLE/MERGE attribute for CALL DEFINE)
...you'll have a preview of what will be available in SAS 9.2.

For SAS 9.1.3, I'm stumped. You may have to give up the red background and do something else...like make the background white for all the titles and just use bolding and colors to highlight the titles on the different sheets.

Or, you might check with Tech Support to see whether they have any ideas. Until 9.2, however, I think that all you can do is change the foreground color of the system titles using the color= option.

cynthia

[ding_dong]

Hi Cynthia

Is there a reason why the excelxp tagset will not action a line statement in proc report if it is empty? For example

compute before _page_;
line 'xyz';
line '';
end;

I need to place a blank line after some text.

Regards

[Cynthia_sas]

Hi:
I believe that if you really examine what is being written, you will find that the space from the second LINE statement is being appended to the string from the first LINE statement

I assume that what you want is a line feed? Or a hard return?? Or a second line? I'm not sure how you're thinking of what you want.

Excel only allows you to specify a "return" in a cell by typing Alt+Enter. Apparently, the way you specify the equivalent of Alt+Enter in a Spreadsheet Markup Language XML file is
[pre]


[/pre]

You might refer to this previous forum posting for a bit more information:
http://support.sas.com/forums/thread.jspa?messageID=11607ⵗ

(in that example, the person who posted had embedded the special entity sequence in his data.) I have never tried it in a LINE statement. You -may- need to also turn protectspecialchars attribute to off, to prevent the ampersand from being protected and turned into &amp instead of &

cynthia

[ding_dong]

Hi Cynthia

I am truly baffled. Is there a problem with the tagset? or is excel not honouring SAS' instructions? If I use 2 line statements in the PDF destination, I am able to insert a blank line after the first line statement.

ods pdf file='C:\report2.pdf';
proc report data=sashelp.prdsale split='#' nowd;
compute before _page_;
line 'xyz';
line '';
endcomp;
column product;
define product / group;
run;
ods pdf close;

In contrast, using the same code with excelxp (below) results in the second line statement being ignored. I have also used the below code to output to RTF and listing and the second line statement is being respected.

ods tagsets.excelxp file='C:\Users\Vojo\Desktop\report2.xls';
proc report data=sashelp.prdsale split='#' nowd;
compute before _page_;
line 'xyz';
line '';
endcomp;
column product;
define product / group;
run;
ods tagsets.excelxp close;

[Cynthia_sas]

Hi:
Ah, this is your "it depends on the destination" moment.

There is nothing wrong with the tagset. There is nothing wrong with SAS. The 2 LINE statements in many destinations will be rendered as 2 separate lines with a "hard return" or line feed between them. ExcelXP in 9.1.3 just doesn't render them as 2 separate lines. The good news is that, in 9.2, when I use 2 LINE statements and ExcelXP, I do get 2 separate lines in my worksheet. However, in 9.1.3, if you had 2 line statements like this:
[pre]
line "one";
line "two";
[/pre]

You would see 2 lines in HTML, RTF and PDF and 1 line in 9.1.3 ExcelXP output:
[pre]
onetwo
[/pre]

Your second LINE statement is being "respected" in the other destinations because different destinations treat that second line statement differently. ExcelXP output is XML-based output -- Spreadsheet Markup Language. You can't really compare PDF rendering -- a proprietary binary format or RTF rendering -- an ACSII markup format invented by Microsoft with the XML markup tags that are written by ExcelXP.

That is the "it depends" part -- fundamentally, RTF and PDF are rendering the 2 LINE statements differently in 9.1.3. What gets sent to those destinations is different than the XML that is sent to Excel -- because the destinations are fundamentally different.

As I said, in SAS 9.2, your syntax produces 2 "lines" in Excel2007 by creating 2 separate <ROW> tags.

[pre]
<Row ss:StyleID="_body"><Cell ss:StyleID="notecontent__c"><Data ss:Type="String">one</Data></Cell></Row>
<Row ss:StyleID="_body"><Cell ss:StyleID="notecontent__c"><Data ss:Type="String">two</Data></Cell></Row>
[/pre]

However, in SAS 9.1.3, the XML that's produced only has 1 (one) <ROW> tag. I suspect this difference happens because something in the events being sent to the tagset template by PROC REPORT must have changed between 9.1.3 and 9.2. The events in 9.1.3 generate ONE <ROW> tag and the events in PROC REPORT 9.2 generate TWO <ROW> tags.

As I indicated in my previous post, one possible workaround is for you to put the equivalent of Alt+Enter (&#10;) into your text strings. The trick, then, will be to make sure that Excel expands the row height appropriately when the file is being rendered.

cynthia

[ding_dong]

Hi Cynthia

Thanks for getting back to me so soon. I've tried placing in the below strong with no success. I suspect I am placing in the wrong place. Any suggestions?

compute before _page_;
line '=HYPERLINK("#contents!A1","Click here to return to contents page ")';
line '';
endcomp;

[Cynthia_sas]

Hi:
&#10;

the semicolon is part of the entity --
ampersand-sharp sign-10-semi-colon

When I do this in PROC REPORT:
line 'one&#10;';
line 'two';

if my row height is high enough, I get the equivalent of an Alt+Enter in the cell.

I have never tried this in the context of an =HYPERLINK cell instruction. I mostly stick with LINK= and URL= from the ODS side of things and let ODS build the hyperlink that's appropriate for the destination. I don't know whether this will work or not.

You might want to check with Tech Support.

cynthia

[ding_dong]

Hi Cynthia

I tried using the url in the line statement but it didn't seem to work. In terms of the link=, my understanding is that it only works in titles. Is this correct?

Regards

[Cynthia_sas]

Yes, you are correct. LINK= only works in a TITLE or FOOTNOTE statement. However, I tried URL= in both a CALL DEFINE, a STYLE(LINES)= override and neither worked for the compute before _page_ text.

I did note, from some papers, that a VARIABLE, whose value was a HYPERLINK would work with ExcelXP and Excel. So, I tried this:
[pre]
title;

ods tagsets.excelxp file='C:\temp\report8.xml'
options(doc='Help' sheet_name='Contents' embedded_titles='yes') ;

proc report data=sashelp.prdsale split='^' nowd
style(header)={background=pink url='#Class!A1'} ;
compute before _page_ /
style={protectspecialchars=off};
length tmpvar $100;
tmpvar = '=HYPERLINK("[C:\temp\report8.xml]contents!A1","CLICK HERE")';
line tmpvar $char100.;
endcomp;
column product;
define product / group 'Click Here For Next Page^Product';
run;

ods tagsets.excelxp options(sheet_name='Class');
proc report data=sashelp.class nowd split='^';
title link='#Contents!A1' "Click to Go To Contents";
run;

ods _all_ close;
[/pre]

And the URL= on the HEADER style override did work. And because the split character is honored in the header, I got 2 lines in my header cell.

The HYPERLINK in the COMPUTE before only sort of worked, in my opinion. When I opened the Excel file (Excel 2007) I could see the entire text of the hyperlink in the cell. The minute I clicked in the cell and hit enter, however, the hyperlink tag turned into an underlined hyperlink.

So, perhaps you could put some of your links in spanning headers and some of your links in titles and/or footnotes.

Cynthia

[ding_dong]

Hi Cynthia

Thanks for the reply. I've taken a slightly different root and I think I'm getting there. If successful, I shall post back for the benefit of others who may be experiencing the same issues.

proc report data=temp nowd split='#'
style(lines)={url="#contents!A1" foreground=blue font_face="arial" font_size=14pt};
title1 "#byval(channels) - #byval(product) &tle";
by channels product;
column x y z;
compute before _page_;
line 'Click here to return to contents page';
endcomp;