Truncation of numeric data using SAS/ODS Exceltagsets

[deleted_user]

I have created an Excel worksheet using ODS and all the output is fine except for one column where the total exceeds 88 billion. The hundreds position is set to zero even though the full width of the total is shown with comma separators. I have tried different width and tagset options (e.g.DEFINE MATAMT/ STYLE={TAGATTR="FORMAT:###,###,###0"} WIDTH = 20 SUM RIGHT
"Matured*Amount"; ) but it doesn't work. I am using SAS 9.13, if I just use regular list output then it is working correctly. Any help would be appreciated.

[Cynthia_sas]

Hi:
I thought a billion was a 1 followed by 9 zeroes or 9 total digits after the high digit(s). So it looks to me like you have your commas in the wrong place.

However, when I try this, I see all the digits no matter which format specification I use. Remember that when you use TAGATTR, you are sending a MICROSOFT format to Excel, not a SAS format. So how it looks in the Listing window is not relevant --- Excel is notorious for ignoring the SAS format (which is one reason why TAGATTR was invented).

cynthia

[pre]
data billion;
set sashelp.class;
b_age = age * 1111111110;
run;

ods listing close;
ods tagsets.excelxp file='c:\temp\test_billion.xml' style=sasweb
options(embedded_titles='yes');
proc report data=billion nowd;
title 'This works FORMAT:##,###,###,##0';
column name age b_age;
define b_age /f=comma14.
style(column)={TAGATTR="FORMAT:##,###,###,##0"};
run;

proc report data=billion nowd;
title 'This works too FORMAT:###,###,###0';
column name age b_age;
define b_age / f=comma14.
style(column)={TAGATTR="FORMAT:###,###,###0"};
run;

proc print data=billion;
title 'PRINT works FORMAT:##,###,###,##0';
var name age;
var b_age /style(data)={TAGATTR="FORMAT:##,###,###,##0"};
run;

proc tabulate data=billion;
title 'TAB works FORMAT:##,###,###,##0';
class age;
var b_age;
table age,
b_age*mean*{style={TAGATTR="FORMAT:##,###,###,##0"}};
run;
title; footnote;

ods _all_ close;

[/pre]

[deleted_user]

Hi Cynthia, I tried that and get the same results the number of digits are okay but the ODS shows zeroes and not the hundreds values. I have shown a comparison between the listing and ODS.
SAS/ODS SAS/LISTING(Correct total)

1,411,090,000 1,411,090,110
9,932,440,000 9,932,440,055
2,295,394 2,295,394
5,680,890,000 5,680,891,725
6,804,520,000 6,804,520,632
2,063,440,000 2,063,438,984
3,197,820,000 3,197,819,561
30,160,000,000 30,160,000,674
19,029,000,000 19,029,377,784
4,820,240,000 4,820,241,629
5,535,050,000 5,535,047,089
88,637,000,000 ==============
88,637,163,636

[Cynthia_sas]

Hi,
I'd suggest that you put an explicit SAS format (such as comma14.) into your PROC REPORT code. If you notice, my PROC REPORT example used a SAS format and Microsoft formats.

If you look at the underlying XML with Notpad, I'm betting that you'll see something like this scientific notation in your XML file:
[pre]
<Cell ss:StyleID="data__r1" ss:Index="3">
<Data ss:Type="Number">1.4444E10</Data>
</Cell>
[/pre]

If you use a SAS format in your code, you should see something like this in your XML file:
[pre]
<Cell ss:StyleID="data__r1" ss:Index="3">
<Data ss:Type="Number">14444444443</Data>
</Cell>
[/pre]

Excel does not interpret scientific notation very well -- it knows the exponent, but not what the internally stored number originally was.

When SAS writes the LISTING output, it knows what the value of the internally stored number is -- although I'm surprised you didn't see scientific notation in the LISTING window too. But when SAS writes the number to the ODS file, without any instruction from you, it's using the BEST format -- which is scientific notation for big numbers. You need to get something other than scientific notation in your XML file for Excel to render correctly.

If you see the entire number in the XML when you look at the file in Notepad and Excel is still showing zeroes, then you will need to work with Tech Support on this issue. It was my understanding that if the XML showed a whole number, then Excel would display the up to whatever level of numeric precision Excel has. There is some point where Excel craps out -- but I thought it was more than 16 digits. Tech Support would know for sure.

cynthia

[deleted_user]

Cynthia, adding the SAS format =comma14.statement worked perfectly, thanks for your help. - Bob