BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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.]]
11 REPLIES 11
SPR
Quartz | Level 8 SPR
Quartz | Level 8
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
Not applicable
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
SAS Super FREQ
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
Not applicable
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
SAS Super FREQ
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
Not applicable
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
Rhodochrosite | Level 12
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
Not applicable
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
Quartz | Level 8 SPR
Quartz | Level 8
Alana,

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

SPR
Cynthia_sas
SAS Super FREQ
I agree with Art. Have you tried this:
Another specification of the range could be:

-100000000 -< 0 = 'red';

in your format???

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



Ksharp

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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