topic Re: negative percent8. format in excelXP in ODS and Base Reporting

negative percent8. format in excelXP

Peter_C — Mon, 16 Aug 2010 13:04:22 GMT

Hi
has my forum search missed a posting describing how to proc print negative percentages in excelXP?
I would like similar appearance in both html and excelxp for negatives, like example (5.25%)
Syntax follows to create and use a few negative numbers[pre]ods _all_ close ;
ods listing ;
data test_neg ;
do num = -10 to 11 by 1.23 ;
perc = num /100 ;
output ;
end ;
format num comma8.2 perc percent8.2 ;
run ;
filename rep '!temp\test_neg.html' ;
filename xlp '!temp\test_neg.xml' ;
ods html file= rep style= egdefault ;
ods tagsets.excelxp file= xlp style= egdefault ;
ods tagsets.excelxp options( sheet_name="test_NEG" Convert_Percentages='Yes' ) ;
ods listing ;
proc print data= test_neg ;
id num / style={tagattr='format:#,##0.00'};
var perc / style={tagattr='format:0.00%;[Red]\(0.00%\);'};
run ;
ods _all_ close ;
[/pre]
Is it something to do with how tagsets.excelXP handles negative values output from the percent. format? [pre]( 5.67)[/pre]Is it handled better in SAS9.2?
Using the option Convert_Percentages='Yes' seems to make no difference for negative values. 😞
peterC

p.s.
versions etc, from SASlog
NOTE: This session is executing on the XP_PRO platform.
NOTE: SAS 9.1.3 Service Pack 4
NOTE: This is the Excel XP tagset (SAS 9.1.3, v1.86, 04/15/08).

Re: negative percent8. format in excelXP

Andre — Mon, 16 Aug 2010 14:06:10 GMT

Peter,
same results (see your mails)
but if you use the new

format num comma8.2 perc percentn8.2 ;

under 9.2.2 or 9.2.3
it is OK

Andre

Re: negative percent8. format in excelXP

Peter_C — Mon, 16 Aug 2010 14:19:30 GMT

thank you Andre

in my SAS913, percentN8. is better than best8.
It solves the problem in excel by taking the parentheses out of the HTML - That is why I asked about making these destinations appear the same
> I would like similar appearance in both html and excelxp for negative percentages
, but I don't think it is possible in the latest releases of tagset.excelXP.

regards
peterC referring to my release of SAS Message was edited by: Peter.C

Re: negative percent8. format in excelXP

Andre — Mon, 16 Aug 2010 14:55:05 GMT

Peter
i answer too quickly
i agree even with the last tagsets
Compatible with SAS 9.1.3 and above, v1.94, 09/09/12

you have under xml a number for percentn
ss:Type="Number">-0.1
in place of a string
ss:Type="String">(10.00%)
if you use percent

tagattr is very difficult to guess and more in locale variation!!!!

Andre

Re: negative percent8. format in excelXP

Cynthia_sas — Mon, 16 Aug 2010 15:57:46 GMT

Hi, Peter:
The issue, (and you may want to double check this with Tech Support), is that TAGATTR is not the method by which you send a Microsoft format to an HTML file that you intend to open with Excel. Instead of TAGATTR, you use HTMLSTYLE style attribute. In my experience, this means that you generally don't have 1 code block for both destinations. I also use ODS MSOFFICE2K instead of ODS HTML -- because ODS HTML creates HTML 4 tags (which Microsoft doesn't like as well as its "flavor" of HTML) -- MSOFFICE2K creates Microsoft flavor HTML.

For an example of using HTMLSTYLE, see this previous forum posting:
http://support.sas.com/forums/thread.jspa?messageID=40547鹣

And this paper has some more examples of using HTMLSTYLE:
http://support.sas.com/rnd/base/ods/templateFAQ/Excel1.pdf

cynthia

Re: negative percent8. format in excelXP

Peter_C — Mon, 16 Aug 2010 16:55:07 GMT

Thank you Cynthia

my client was receiving a daily-changes report in html quite happily, until we improved a different report placing it in excel through tagsets.excelxp. Now (as normal with customers, who are always right) they would like it both ways.
I shall use Andre's proposal format percentN. for the closest match between html and excel surfaces. Not perfect, but sufficient (until the next significant release of tagsets.excelxp - once QA have finished).

ok for now
peterC