XML and tagsets.excelXP

[deleted_user]

I was wondering.......one of the things I found handy using the HTML or MSOFFICE2K ODS destinations was that I could very easily throw html tags into my actual data. So if I had a data value that I wanted to show up as bold or italic, I could conditionally do something like var1 = '' || var1 || '' ... Do you know if there's a similar way of doing things with XML?

A way to indent a value a few spaces would be handy as well, if anybody knows how...

There appears to be a way to do it spelled out in the tagsets.excelxp help, but I can't get it to work. The '" bold "' just seems to show up as actual text.

[Cynthia_sas]

If you were using proc report, proc print or proc tabulate, one method to accomplish bolding (or italicizing) that will work for any destination and not just HTML or XML (excelxp) is this example. Consider PROC PRINT:
[pre]
ods tagsets.excelxp file='koala.xls' style=sasweb;
ods msoffice2k file='wombat.xls' style=sasweb;
ods rtf file='oz.rtf' style=sasweb;
ods pdf file='lyptus.pdf' style=sasweb;

proc print data=sashelp.shoes(obs=10) noobs;
var region product;
var sales / style(data)={font_weight=bold};
run;
ods _all_ close;
[/pre]

The results of this program will have every data cell for sales in a bolder font than the data cells for region or product.

You said in your question, however, that you wanted to "conditionally" highlight a number and this is also possible with PROC REPORT, PROC PRINT and PROC TABULATE and with custom table templates for use with DATA _NULL_. You have 2 ways to do it with PROC REPORT (using a CALL DEFINE statement or with a user-defined format) and the user-defined format method will also work for proc print and proc tabulate and DATA _NULL_. Consider the following code:
[pre]
ods tagsets.excelxp file='koala2.xls' style=sasweb;
ods msoffice2k file='wombat2.xls' style=sasweb;
ods rtf file='oz2.rtf' style=sasweb;
ods pdf file='lyptus2.pdf' style=sasweb;

proc format;
value salebk low-50000='yellow'
75000-high='pink'
other='white';
value salefw 75000-high='bold'
other='medium';
run;
proc print data=sashelp.shoes(obs=10) noobs;
title 'with proc print and traffic lighting';
var region product;
var sales / style(data)={background=salebk.
font_weight=salefw.};
run;
ods _all_ close;
[/pre]

What happens in the above example is that the background color is now set by the salebk format and font_weight is set conditionally by the salefw format, based on the value in the sales cell. This kind of traffic lighting is a very powerful feature of ODS. I hope this helps you get started with your highlighting task.
Cynthia

[deleted_user]

Thanks Cynthia. The proc format-driven highlighting is definitely a powerful feature. However I'm not sure it'd do exactly what it is I'm trying to accomplish.

Say I've got a table with 5 variables. Depending upon the value in var1, I want to make all data in that row (as opposed to column), bold. So with HTML, it was easy to just do it in a data step by throwing those HTML tags in around the var1-5.

I'm not sure I see how to do this with traffic lighting though. I guess there's no equivalent XML tag to bold data?

[Cynthia_sas]

Darryl:
Well, I can't say that there's NO XML tag to accomplish bolding -- because XML is so openended that
there are ways you could write your own XML application and your own XSL transform that you could accomplish that kind of bolding.

On the other hand, IF you are asking whether there's a way with the ExcelXP XML tagset to accomplish bolding on a conditional level, then the answer is...it depends. There is absolutely a way to change the tagset to recognize cell values and then turn on bolding (assuming you know how Microsoft does the bolding).

But, making that change at the tagset level is not required if you could use PROC REPORT. Consider the following code:
[pre]
ods html file='rep_hilite.html' style=sasweb;
proc report data=sashelp.shoes nowd
style(lines)=Header
style(summary)={font_weight=bold};
title 'Highlight the Whole Row if';
title2 'some condition is met';
where region in ('Asia', 'Canada');
column region product sales;
define region / group;
define product / group;
define sales / sum;
break after region / summarize;
compute sales;
if sales.sum ge 300000 and sales.sum le 1000000 then
call define(_ROW_,'STYLE',
'style={background=pink font_weight=bold}');
else if sales.sum le 75000 then
call define(_ROW_,'STYLE',
'style={background=yellow font_weight=bold}');
endcomp;
run;
ods html close;
[/pre]

...which produces pink and yellow bolded rows based on the value of the sales variable.

cynthia
ps...did HTML for simplicity.

[deleted_user]

Cynthia....... You're awesome.

Thank you very much for your help. This does exactly what I'm looking for!

Take care,
Darryl

[deleted_user]

Lastly... Is there a way to preserve leading blanks in a text string? Sort of faking an 'indent'.

I.E.

(good)
'abcdefg'
' abcdefg'

(bad)
'abcdefg'
'abcdefg'

[Cynthia_sas]

Darryl:
Just like I could specify background=pink, I can also specify asis=on in the style override for a variable. BACKGROUND and ASIS are style attributes. What comes after the equal sign is the attribute value.

However, a few warnings...I would set ASIS on a variable/column and not on a whole row. Second, there is an indent= style attribute that -might- work for you.

Last, but not least ... there's Excel to consider .. if you are going to open this file in Excel and not in a browser. I don't believe that Excel always plays nice with asis=on or indent=. Test it out. If your indents do not seem to 'stick' in Excel, then you can resort to the old HTML entity for a non-breaking space (ampersand NBSP semicolon) -- which may or may not show up correctly below.
[pre]
 
[/pre]
If you do NOT see ampersand NBSP semicolon in the above line, that's because there is some funkiness going on with the way messages get parsed and posted. If you will send an email to cynthia.zender@sas.com, I can send you a program that uses the HTML non-breaking space entity in a program. But I can't post the program code here because it contains many #nbsp; (imagine a & where I have a #).
Thanks, cynthia

[OS2Rules]

Hey Darryl:

I've had some of these problems and here is what I've managed to come up with...

SAS has provided a new Tagset for ExcepXP this month that corrects problems with leading zeros on text fields. I'm not sure if it helps with leading blanks, but you may want to give it a try.

Also Cylthia's post is right on regarding changes to attributes for specific purposes. You need to use the COMPUTE statements with CALL DEFINE to alter any of the attributes. I'm waiting for SAS to get back to me on just which attributes can be changed.

Only problem with this is that if you change an attribute with the CALL DEFINE, all other attributes that you have set for the cell seem to be lost. For example, if you set the background to 'yellow' for the column, and then use the COMPUTE to have a subtotal font set to bold (in the same column), the background colour gets reset.

BTW: Howard C. over at 44 gave me your e-mail after the TASS meeting - if you want I can contact you directly.