topic Re: Formatting "positive" zero values in SAS Procedures

Formatting "positive" zero values

deleted_user — Tue, 07 Dec 2010 14:24:04 GMT

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.]]

Re: Formatting "positive" zero values

SPR — Tue, 07 Dec 2010 15:10:28 GMT

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

Re: Formatting "positive" zero values

deleted_user — Tue, 07 Dec 2010 15:22:20 GMT

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.

Re: Formatting "positive" zero values

Cynthia_sas — Tue, 07 Dec 2010 16:32:42 GMT

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

Re: Formatting "positive" zero values

deleted_user — Tue, 07 Dec 2010 17:54:31 GMT

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;

Re: Formatting "positive" zero values

Cynthia_sas — Tue, 07 Dec 2010 18:13:12 GMT

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

Re: Formatting "positive" zero values

deleted_user — Tue, 07 Dec 2010 18:20:45 GMT

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]};

Re: Formatting "positive" zero values

ArtC — Tue, 07 Dec 2010 18:57:59 GMT

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.

Re: Formatting "positive" zero values

deleted_user — Tue, 07 Dec 2010 19:14:22 GMT

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.

Re: Formatting "positive" zero values

SPR — Tue, 07 Dec 2010 19:17:26 GMT

Alana,

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

SPR

Re: Formatting "positive" zero values

Cynthia_sas — Tue, 07 Dec 2010 19:57:50 GMT

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

-100000000 -< 0 = 'red';

in your format???

cynthia

Re: Formatting "positive" zero values

Ksharp — Mon, 13 Dec 2010 01:07:51 GMT

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

Ksharp