BookmarkSubscribeRSS Feed
Peter_C
Rhodochrosite | Level 12
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).
5 REPLIES 5
Andre
Obsidian | Level 7
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
Peter_C
Rhodochrosite | Level 12
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
Cynthia_sas
SAS Super FREQ
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
Peter_C
Rhodochrosite | Level 12
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
Andre
Obsidian | Level 7
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

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
  • 5 replies
  • 1189 views
  • 0 likes
  • 3 in conversation