topic Re: Displaying a number as a number with the ExcelXP tagset in ODS and Base Reporting

Displaying a number as a number with the ExcelXP tagset

JackHamilton — Fri, 26 May 2006 01:33:31 GMT

I don't understand why numbers are showing up as text in the XML spreadsheet created by the ExcelXP tagset.

Here's some code:

=====
ods path WORK.TEMPLAT(WRITE) SASHELP.TMPLMST(READ);

/* Get latest copy of tagset from SAS web site */
filename tagset http
'http://support.sas.com:80/rnd/base/topics/odsmarkup/excltags.tpl';
%include tagset / nosource2;
filename tagset clear;

data VolumeExpense;
length PurchMC ProvMC Type $8.;
format Cost comma10.2;
do purchmc = 'Hay', 'Rch', 'Oak';
do provmc = 'Oak', 'Hay', 'Rch';
do type = 'Volume', 'Expense';
cost = round(ranuni(94612)*100, .01);
output;
end;
end;
end;
run;

ods tagsets.excelxp
file='c:\temp\ExcelNum.xls'
options(sheet_name='Output from REPORT'
frozen_headers='3'
row_repeat='1-3'
autofilter='2'
);

proc report data=VolumeExpense missing nofs nocenter
completerows completecols;
column purchmc type provmc, cost cost=totcost;
define purchmc / group 'Purch MC' width=8 order=data;
define type / group 'Type' width=8 order=data;
define provmc / across 'Prov MC' width=8 order=data;
define Cost / sum 'Cost'
style={tagattr='format:#,##0.00'};
define totcost / sum 'Total Cost' width=10 format=comma10.2
style={tagattr='format:#,##0.00'};
;
compute provmc;
if type = 'Expense' then
call define(_col_, 'style', 'style={background=yellow}');
endcomp;
run;

ods _all_ close;
=====

I added the style=tagattr=format:#,##0.00 in hopes of forcing Excel to treat it as a number, but no joy.

How do I make this work?

Re: Displaying a number as a number with the ExcelXP tagset

deleted_user — Fri, 26 May 2006 15:00:54 GMT

Call me john wayne, here, shooting from the hips and all, but I'll bet it something to do with the comma format. My guess is using something like 10.2 and I'll bet all will be right with your world.

I had something similar come up when I wrote out numbers as Percent7.2, say and surprised to find (well, actually it was my boss, and, yeah, surprised will work here) that it came up 12.2% - sure did funny things to the spreadsheet.

Like you, I'm still experimenting with the tagattr and format thingies, but so far I really like what I've seen.

Re: Displaying a number as a number with the ExcelXP tagset

JackHamilton — Fri, 26 May 2006 16:27:48 GMT

Alas, no, using 10.2 instead of comma10.2 doesn't solve the problem.

That wouldn't be a satisfactory solution even if it did work - I'd have to choose between getting commas in Excel and getting commas in PDF.

I think I'm overlooking something simple.

Re: Displaying a number as a number with the ExcelXP tagset

Eric_SAS — Fri, 26 May 2006 20:17:55 GMT

Hi jack,

There is a bug there. Part of it is that proc report says all those numbers are strings. There is a workaround.

tagattr now takes type: You can set type to Number. I do have a tagset
fix in hand as well. Here's an excerpt from the help for tagattr.

TagAttr Style Element: Default Value ''
Values: or
< datatype="">
< excelformat="">
< excelformula="">
< degrees="" of="" rotation.="">
This is not a tagset option but a style attribute that the tagset will
use to get formula's and column formats. The format and formula's given
must be a valid to excel. The rotation must be a valid angle for text.
90 through -90.
The Type should be General, String, Numeric, or DateTime
It should be unecessary to specify type except when DateTime is being used.

A single value without a keyword is interpreted as a format.
A formula, format and rotation can be specified together with keywords.
There should be no spaces except for those between the two values
The keyword and value must be separated by a ':' tagattr='format:###.## formula:SUM(R[-4]C:R[-1]C rotate:90').

Re: Displaying a number as a number with the ExcelXP tagset

deleted_user — Fri, 26 May 2006 21:07:04 GMT

Bang, bang, wrong again. One of these days, I'll get it right.

So, Eric, I understand you to say that Jack should change his code to something like:
define totcost / sum 'Total Cost' width=10 format=comma10.2
style={tagattr='type:number format:#,##0.00'};

The format=comma10.2 would stay the same.

In my case, I would do something similar?
define totcost / sum 'Total Cost' width=10 format=percent10.2

style={tagattr='type:number format:#,##0.00%'}; say (assuming I wanted a really, really big percent here).

And, since I downloaded the tagset last week, along with with bunch of pdf papers and presentations, the tagset would be current.

Re: Displaying a number as a number with the ExcelXP tagset

JackHamilton — Tue, 30 May 2006 20:31:50 GMT

When I replace the two statements containing a TAGATTR with

define Cost / sum 'Cost' style={tagattr='type:numeric format:#,##0.00'};

define totcost / sum 'Total Cost' width=10 format=comma10.2 style={tagattr='type:numeric format:#,##0.00'};
;

I get an error when I try to open the resulting file in Excel.

The error log has many entries of the form

=====

XML ERROR in Table
REASON: Bad Value
FILE: C:\Temp\ExcelNum.xls
GROUP: Cell
TAG: Data
ATTRIB: Type
VALUE: numeric
=====

I tried editing the file to change"numeric" to "Numeric", but that didn't help.

Re: Displaying a number as a number with the ExcelXP tagset

Eric_SAS — Wed, 31 May 2006 14:47:40 GMT

Well, I documented it incorrectly. It should be Number, not numeric. I changed the documentation and I'll add some error checking so that you'll get a message if an invalid type is used.

Number worked for me. number did not.

Re: Displaying a number as a number with the ExcelXP tagset

Eric_SAS — Wed, 31 May 2006 14:54:35 GMT

If you use a percent format the tagset will figure that out and do the right thing. That has been in place for over a year. I don't know of any problems with getting it to set the type correctly when using percent formats inside SAS. The tagset will set the format to percent and divide the number by 100 automatically. This bug may affect it.

The most current tagset will be available sometime tomorrow.

Re: Displaying a number as a number with the ExcelXP tagset

deleted_user — Mon, 05 Jun 2006 16:17:54 GMT

I downloaded the latest tagset and it seems to display all the numbers by default as numbers. I am trying to get one column of numbers to stay strings. I set the style as style={tagattr="Type:String"}. This creates the following XML -- 3.5. That generates an error in Excel until I edit the XML file and change all occurrences of 'string' to 'String'. What is the trick to make the tagset generate 'String' instead of 'string'?

Re: Displaying a number as a number with the ExcelXP tagset

Chevell_sas — Tue, 06 Jun 2006 13:49:52 GMT

Try adding the text format "@" rather then setting the type to string. This will set the type to string automatically if you set the format to text.

data one;
x='0001';
run;

ods tagsets.excelxp file="temp.xls";

proc print data=one;
var x / style={tagattr="format:@"};
run;

ods tagsets.excelxp close;

Re: Displaying a number as a number with the ExcelXP tagset

deleted_user — Tue, 06 Jun 2006 15:18:49 GMT

Thank you that works.

Re: Displaying a number as a number with the ExcelXP tagset

deleted_user — Tue, 20 Jun 2006 15:58:51 GMT

I'm running into a similar problem... I have a column of data with mixed datatypes; so I've converted everything to a character string. For example, the data looks like:

'100,000'
'17.4%'
'75'

The problem I have is that when I output this, I lose the comma formatting. It treats it as a numeric. If I force the value to be a character string (style={tagattr="format:@"}; ) it still strips out my commas and in fact then messes with the percentage as well.

Is there a way to have it output the character strings as-is?

On a sidenote, I tried to get around this by including a single quote in the string as well. This worked to the extent that everything showed up as intended; however, the single quote wasn't interpreted by Excel. It stuck around as part of the display value.

Re: Displaying a number as a number with the ExcelXP tagset

Chevell_sas — Wed, 21 Jun 2006 14:03:31 GMT

It looks like we are stripping the value before applying the text format. Please open a tracking entry with Technical Support on this issue.

http://support.sas.com/techsup/contact/submit_emits2.html.

Re: Displaying a number as a number with the ExcelXP tagset

Eric_SAS — Wed, 05 Jul 2006 14:25:38 GMT

This behavior is unavoidable. The tagset uses a complex regular expression to determine if numbers are numbers. The formatting must be stripped because Excel will turn it into a string if there are any currency symbols, comma's, percent signs etc. The appropriate excel format must then be used in order to get the format you want. Percentages are done for you. The tagset finds the % so it knows to change the format to percentage. In the other cases you will need to apply a format if the default format is not what you want.

Re: Displaying a number as a number with the ExcelXP tagset

Chevell_sas — Mon, 10 Jul 2006 16:02:53 GMT

Eric, we were able to come up with an example to do what he wanted here by-passing the regular expressions. Hopefully, this did not expose anything else by doing so.

proc template;
define tagset tagsets.test;
parent=tagsets.excelxp;
define event value_type;
set $format "General";
set $value strip(VALUE);

do /if $value;
do / if !tagattr;
eval $is_numeric prxmatch($number,$value);

do /if $is_numeric;
set $type "Number";
set $value compress($value,$punctuation);

do /if index(value, %nrstr("%%")) > 0;
set $format "Percent" /if index(value, %nrstr("%%")) > 0;
eval $tmp inputn($value,$test_format) / 100;
set $value $tmp;

else /if index(value, $currency) > 0;
set $format $currency_format /if index(value, $currency) > 0;
done;
done;

else;
set $type "String";
done;

done;

do /if $attrs["type"];
set $type $attrs["type" ];

else /if ^cmp( $type, "Number");
set $type "String";

do /if $is_numeric;
set $type "Number" /if cmp( type, "int");
set $type "Number" /if cmp( type, "double");
set $type "String" /if cmp( type, "string");
done;

done;

end;
end;
run;

data one;
x='100%';
y=20000;
z=3.3;
run;

ods tagsets.test file="temp.xls";

proc print data=one;
var x / style={tagattr="format:@"};
var y / style={tagattr="format:###,###"};
var z ;
run;

ods tagsets.test close;

Re: Displaying a number as a number with the ExcelXP tagset

JenHarper — Wed, 06 Dec 2006 23:32:48 GMT

Resurrecting an old thread...

I can USE tagsets.excelxp, but I can't follow the suggestions above. PROC TEMPLATE is all greek to me!

I'm creating an XLS file with 3 tabs from 3 PROC TABULATE statements. As is usual in TABULATE, missing values are shown as '.'. I use the misstext='0' to put zeros in HTML output and such. In Excel, the 0's are still formatted as text rather than numeric zero's.

My main question (and 2 bonuses):
* How can I get zero values in TABULATE to show up as numeric?
* [bonus1] How can I get rid of ODS styles to just have a plain Excel file with no ODS fomats (color, size, font, etc)?
* [bonus2] How do I get excel to show commas as the thousands seperators?

I used the code below borrowed from JackH to hopefully make sure I'm running the most recent excelxp tagset.
/* Get latest copy of tagset from SAS web site */
filename tagset http
'http://support.sas.com:80/rnd/base/topics/odsmarkup/excltags.tpl';
%include tagset / nosource2;
filename tagset clear;

My code looks like this:
ods tagsets.excelxp file='desktop/myfile.xls' ;

ods tagsets.excelxp options(sheet_name='All Heat Deaths');
proc tabulate data=lib.datafile;
class datayear ageyrs ;
tables all ageyrs=' ', all datayear=' ' / misstext='0';
keylabel n=' ' all='Total';
format ageyrs ageten.;
run;

ods tagsets.excelxp options(sheet_name='CA Res Heat Deaths');
/* another very similar proc tabulate */

ods tagsets.excelxp options(sheet_name='Population data');
/* another very similar proc tabulate */

ods tagsets.excelxp close;

Re: Displaying a number as a number with the ExcelXP tagset

Cynthia_sas — Fri, 08 Dec 2006 16:02:13 GMT

Jen:
There are many different pieces at work in your program. The zero as text issue IS a tagset issue and I understand the developers are working on an updated version of the ExcelXP tagset that fixes that issue. You will have to contact Tech Support about obtaining that tagset.

So, for your other questions about 1) getting rid of styles and 2) getting the comma in Excel, I'm going to answer the second question first (because the first question will take more code...)
[pre]
tables all*{s={tagattr='format:#,###,#00'}} ageyrs=' ',
all datayear=' ' / rts=20 misstext='0'; [/pre]

the TAGATTR style override is sending a MICROSOFT format to Excel. This style override is telling ODS to send commas as the thousands separator ('format:#,###,#00'). You don't need the 'format:' if you're just sending a format, but I thought it looked better to explicitly send 'format:' for completeness. You would have to use that same style attribute override for any table components that you thought would need the comma format.

How you minimize the ODS styles is by using a modified style template. The key to modifying the style template is the fact that you either specify a value for certain attributes, like background color, foreground color and borderwidth (to name a few attributes) OR you turn them off. Turning them off is accomplished by using the _UNDEF_ style attribute. It's like saying -- I don't care what attributes are used, just use the default attributes of the rendering software. So, in my code below, there's a PROC TEMPLATE step that modifies the Statistical style to remove just about all the style attributes except the bold on the HEADER cells. Since I didn't have your data, I had to make some by using SASHELP.PRDSALE and then fiddling with variable values until I got some missing values.[pre]
ods path sasuser.templat(update)
sashelp.tmplmst(read);

options missing=.;
data dfile;
set sashelp.prdsale;
datayear = year;
ageyrs = (today()-month)/365.25;
if year = 1994 and division = 'CONSUMER' then datayear = datayear - 2;
if division = 'CONSUMER' then ageyrs=ageyrs-5;
if division = 'EDUCATION' and prodtype='FURNITURE' then ageyrs=.;
run;

proc format;
value ageten low-<10 = 'Group 1'
10-20 = 'Group 2';
run;

** Modify the Minimal style to get rid of;
** all border lines, colors, etc;
proc template;
define style styles.XLStatistical;
parent = styles.Statistical;
style Body from Body/
background=_undef_;
style table from Output /
background=_undef_
foreground=_undef_
borderwidth=_undef_
font_size=10pt;
style Header from Header /
background=_undef_
foreground=_undef_
borderwidth=_undef_
font_size=10pt;
style RowHeader from RowHeader /
background=_undef_
foreground=_undef_
borderwidth=_undef_
font_size=10pt;
style Data from Data /
background=_undef_
foreground=_undef_
borderwidth=_undef_
font_size=10pt;
end;
run;

ods tagsets.excelxp file='c:\temp\use_style.xls'
style=styles.XLStatistical;
ods tagsets.excelxp options(sheet_name='All Heat Deaths');
options missing=0;
proc tabulate data=dfile f=comma9.;
class datayear ageyrs;
tables all*{s={tagattr='format:#,###,#00'}} ageyrs=' ',
all datayear=' ' / rts=20 misstext='0';
keylabel n=' ' all='Total';
format ageyrs ageten.;
run;
ods tagsets.excelxp close; [/pre]
Once I create styles.XLStatistical, I have to use in the ODS invocation for tagsets.excelxp.

That still doesn't address your zero as text issue, but I did check with the developers and they are working on getting an updated version of the ExcelXP tagset approved now. When they do, it will be posted here:
http://support.sas.com/rnd/base/topics/odsmarkup/
(scroll down the page an look for the heading "Updated SAS 9.1 Tagsets to Download Individually" and you will find a place to download an updated tagset. Right now, the ExcelXP tagset is dated June 2006 -- that one still has the zero as text issue so you either have to wait for an updated one (should have an October or November date) or contact Tech Support to get the updated tagset before it's posted.

Once you have the updated tagset, you have to do just 3 things:
1) use an ODS PATH statement to point to your template storage location
2) run the code to update your template storage location with the new tagset for ExcelXP and the new style template
3) use the updated tagsets.excelxp tagset and the new style template

Using the ODS PATH statement will ensure that the updated tagset and style gets written to SASUSER.TEMPLAT template storage location. Since I don't know whether you have used the ODS PATH statement before, I included one in my code: [pre]
ods path sasuser.templat(update)
sashelp.tmplmst(read);[/pre]

Of course, if you don't want to use the SASUSER library, that's fine, you just have to put some other permanent location in your ODS PATH statement so ODS knows where to look for the updated templates.

cynthia