Help using Base SAS procedures

Formatting "positive" zero values

Reply
N/A
Posts: 0

Formatting "positive" zero values

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.]]
Super Contributor
Super Contributor
Posts: 365

Re: Formatting "positive" zero values

Posted in reply to deleted_user
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
N/A
Posts: 0

Re: Formatting "positive" zero values

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.
SAS Super FREQ
Posts: 8,862

Re: Formatting "positive" zero values

Posted in reply to deleted_user
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
N/A
Posts: 0

Re: Formatting "positive" zero values

Posted in reply to Cynthia_sas
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;
SAS Super FREQ
Posts: 8,862

Re: Formatting "positive" zero values

Posted in reply to deleted_user
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
N/A
Posts: 0

Re: Formatting "positive" zero values

Posted in reply to Cynthia_sas
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]};
Valued Guide
Posts: 634

Re: Formatting "positive" zero values

Posted in reply to deleted_user
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.
N/A
Posts: 0

Re: Formatting "positive" zero values

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.
Super Contributor
Super Contributor
Posts: 365

Re: Formatting "positive" zero values

Posted in reply to deleted_user
Alana,

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

SPR
SAS Super FREQ
Posts: 8,862

Re: Formatting "positive" zero values

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

-100000000 -< 0 = 'red';

in your format???

cynthia
Super User
Posts: 10,018

Re: Formatting "positive" zero values

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



Ksharp
Ask a Question
Discussion stats
  • 11 replies
  • 487 views
  • 0 likes
  • 5 in conversation