Formatting "positive" zero values

[deleted_user]

I currently have a format that makes anything with a negative value red with ( ) around them. For the most part this works, but every once in a while there is a "positive" zero value that gets highlighted red. I am not sure why this is happening. Below is a sample of the code.

Example of Format:
*********************************;
proc format;
value difffmt
-100000000 - 0.0001 = 'red';
picture NegDol (multilabel)
-100000000 - 0.00001 = '000,000,000,000)' (prefix='($')
0.00001 - 100000000000 = '000,000,000,000' (prefix='$');

Example of what's being formatted:
Prcnt_Balance_Growth *f=percent7.2* [style=[foreground=difffmt.]]

[SPR]

Hello Alana,

Could you please give an example on this format usage? What I noticed is that the first range definition includes 0:
[pre]
-100000000 - 0.0001 = 'red';
[/pre]
To exclude 0 it should be changed to
[pre]
-100000000 - -0.0001 = 'red';
[/pre]
So the final format definition I think should be like this:
[pre]
proc format;
value difffmt
-100000000 - -0.0001 = 'red';
picture NegDol (multilabel)
-100000000 - -0.00001 = '000,000,000,000)' (prefix='($')
0 - 100000000000 = '000,000,000,000' (prefix='$');
run;
[/pre]

Sincerely,
SPR

[deleted_user]

Sure. I guess I should mention that the red 0 values without the ( ) are percentages. Here's more code. Hope this helps.

table
Total_Beginning_Open_accts *f=negparen20.
New_accts *f=negparen20.
Converted_Accts *f=negparen20.
ReOpened_Accts *f=negparen20.
Account_Attrition *f=negparen20.*[style=[foreground=red]]
Total_Ending_Open_Accts *f=negparen20.
Prcnt_Acct_Attrition *f=percent10.2* [style=[foreground=difffmt.]]
Beginning_Balance *f=NegDol.

[Cynthia_sas]

Hi:
This looks like PROC TABULATE code. But you have only posted part of your TABLE statement. Are all of these variables in the ROW dimension?? Are you using any formats in the COL dimension??? Could you post ALL your code, including the ODS "sandwich" statements so we can see your destination of interest. Also, do you have any options to control missing values???
OPTIONS MISSING=0; or MISSTEXT='0'??? in your code??

cynthia

[deleted_user]

Here's the whole Proc Tabulate with the ODS top and bottoms.

options orientation=landscape nobyline ;
ods listing close;
ods results=off;
ods escapechar='^';
ODS PDF file='C:\Code Output.PDF' style=PrinterSmaller bookmarklist=hide bookmarkgen=yes;

proc tabulate s=[font_size=3] data=DMYM.Output_BL_CB f=6.2 S=[foreground=black] NOSEPS missing;
title color=black 'Solutions';
title2 color=black "Monthly Balance - #byval1";
footnote1 color=black "^S={font_style=italic}" '* Note: Volume, Otherwise.';
* footnote1 color=black "^S={font_style=italic}" '*Note: Fields marked.';
by &BL_CB notsorted;
class Dte;

classlev Dte/S=[background=white foreground=black font_size=3 font_face=times];

var Total N_accts C_Accts RO_Accts Account_F
Total_Ending Prcnt_F Space A_Accts Act_Rate Balance Payments;


table Total *f=negparen20.
N_accts *f=negparen20.
C_Accts *f=negparen20.
RO_Accts *f=negparen20.
Account_F *f=negparen20.*[style=[foreground=red]]
Total_Ending *f=negparen20.
Prcnt_F *f=percent10.2* [style=[foreground=difffmt.]]
Space
A_Accts *f=negparen20.
Act_Rate *f=percent10.2
Space
Balance *f=NegDol.
Payments *f=NegDol.*[style=[foreground=difffmt.]]
Dte={label=' ' s=[background=black foreground=white font_face=times]}
/ box={label=_Page_ s=[just=c background=white foreground=black font_weight=bold font_face=times]};

keylabel sum = ' ';
quit;
title;
footnote;

ODS PDF close;
ods listing;

[Cynthia_sas]

Hi:
Is there a comma missing in your TABLE statement??? Does DTE variable appear in a ROW underneath PAYMENTS variable or does DTE appear in the COLUMNS of the TABLE???

cynthia

[deleted_user]

Yes I apologize. There is supposed to be a comma after Payments. The date appears at the top of the report going across.

Payments *f=NegDol.*[style=[foreground=difffmt.]] ,

Dte={label=' ' s=[background=black foreground=white font_face=times]}
/ box={label=_Page_ s=[just=c background=white foreground=black font_weight=bold font_face=times]};

[ArtC]

I am still intrigued by part of the original post e.g. "positive zero". since, as SPR pointed out, your 'red' range includes 0. There is likely some rounding involved as well as the range issue. Another specification of the range could be:
[pre]-100000000 - < 0 = 'red';[/pre]
Depending on how tight your tolerances are, you may also want to take a look at the FUZZ option.

[deleted_user]

Another thing I forgot to mention is that, the "positive zeros" are percentages. They have been rounded to the thousdands. Hope that helps even more.

[SPR]

Alana,

I did not understand. Did you dry my or ArtC's suggestion?

SPR

[Cynthia_sas]

I agree with Art. Have you tried this:
Another specification of the range could be:

-100000000 -< 0 = 'red';

in your format???

cynthia

[Ksharp]

Hi.
I think you need another option ( Fuzz=.00001 ) to avoid to conflict with labels.



Ksharp