BookmarkSubscribeRSS Feed
JackHamilton
Lapis Lazuli | Level 10
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?
16 REPLIES 16
deleted_user
Not applicable
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.
JackHamilton
Lapis Lazuli | Level 10
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.
Eric_SAS
SAS Employee
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').
deleted_user
Not applicable
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.
Eric_SAS
SAS Employee
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.
JackHamilton
Lapis Lazuli | Level 10
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.
Eric_SAS
SAS Employee
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.
deleted_user
Not applicable
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'?
Chevell_sas
SAS Employee
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;
deleted_user
Not applicable
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.
Eric_SAS
SAS Employee
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.
Chevell_sas
SAS Employee
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.
Chevell_sas
SAS Employee
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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 16 replies
  • 3072 views
  • 0 likes
  • 6 in conversation