The SAS Output Delivery System and reporting techniques

Displaying a number as a number with the ExcelXP tagset

Reply
Frequent Contributor
Posts: 102

Displaying a number as a number with the ExcelXP tagset

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?
N/A
Posts: 0

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

Posted in reply to JackHamilton
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.
Frequent Contributor
Posts: 102

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

Posted in reply to deleted_user
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.
SAS Employee
Posts: 95

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

Posted in reply to JackHamilton
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:###.## formulaSmiley FrustratedUM(R[-4]C:R[-1]C rotate:90').
N/A
Posts: 0

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

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.
SAS Employee
Posts: 95

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

Posted in reply to deleted_user
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.
Frequent Contributor
Posts: 102

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

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.
SAS Employee
Posts: 95

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

Posted in reply to JackHamilton
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.
N/A
Posts: 0

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

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="TypeSmiley Frustratedtring"}. 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'?
SAS Employee
Posts: 88

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

Posted in reply to JackHamilton
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;
N/A
Posts: 0

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

Posted in reply to Chevell_sas
Thank you that works.
N/A
Posts: 0

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

Posted in reply to JackHamilton
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.
SAS Employee
Posts: 95

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

Posted in reply to deleted_user
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.
SAS Employee
Posts: 88

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

Posted in reply to JackHamilton
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.
SAS Employee
Posts: 88

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

Posted in reply to JackHamilton
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;
Ask a Question
Discussion stats
  • 16 replies
  • 1049 views
  • 0 likes
  • 6 in conversation