Tabulate "all" losing significant digits

steve_citi — Wed, 23 Jun 2010 01:06:38 GMT

For some reason I am losing the significant digits in some of my totals.

Also, oddly some of the numbers in my rows are coming out as text. There does not appear to be any consistant reason to this occuring.

proc tabulate data=monthlevel;
class orig_yr_cohort asofyr;
var net_book_bal acct;
tables
(orig_yr_cohort='Vintage Year' ALL='Calendar Total'),(asofyr='Calendar Year' ALL='Vintage Total')*
(net_book_bal=''*sum ='Outstanding (Total) $MM' *f=mmfmt. *[style=[tagattr='Format:$###,###,###,##;[Red][<0]$-###,###,###,##']]
net_book_bal=''*colpctsum ='Outstanding (Total) %' *f=perc6.2 *[style=[tagattr='Format:000.00%']])
/ box="Vintage Year" rtspace=25 row=float;
title1 "&titl1.";
run;

Re: Tabulate "all" losing significant digits

Cynthia_sas — Wed, 23 Jun 2010 02:51:12 GMT

Hi:
One clue about possible problems with your formatted width for a variable is to look in the SAS log and see whether you have this message:
[pre]
NOTE: At least one W.D format was too small for the number to be printed. The decimal may be
shifted by the "BEST" format.
[/pre]

If you are not using a big enough width for your format, this could certainly cause the behavior you describe.

You do not show the full code, including ODS statements and PROC FORMAT statements (for the creation of f=mmfmt. Because you are using TAGATTR, I believe that you might be using TAGSETS.EXCELXP to create a Spreadsheet Markup Language XML file??? It is possible that once your label is applied to your value in a cell, that the value is being interpreted by Excel as character -- I've seen this happen, especially if your formatted number contains extra punctuation or characters, such as leading $ signs.

I also wonder whether you have enough # in the following TAGATTR value:
[pre]
Format:$###,###,###,##;
[/pre]

I see ,## at the end, where I would expect to see either ,### or ,###.00

Finally, be aware that symbols in your code, such as the GT sign > or the LT sign < do present a problem for the forum posting software. Generally, if you post code with either of these characters (such as you show in your TAGATTR value), the post will get truncated because the forum software thinks you are about to use an HTML tag and so it "eats up" the rest of your message looking for a valid HTML tag.

The way around this is to use the entities, < and > for < and > respectively, as described in this previous forum posting:
http://support.sas.com/forums/thread.jspa?messageID=27609毙

If you continue to have issues when you open your file in Excel, you might want to make sure that you have the most recent copy of the tagset template for EXCELXP and/ or work with Tech Support on your question.

cynthia

topic Re: Tabulate "all" losing significant digits in SAS Procedures

Tabulate "all" losing significant digits

Re: Tabulate "all" losing significant digits