SAS EG: Numeric Format add positive (+) sign to values

[Cat_Collector]

Currently, my data set is returned negative and positive values. The negative values display as follows: -1, -10, -11, etc. Meanwhile the positive values are bare: 1, 3, 4, etc. I would like the positive values to display with the positive sign before the value: +1, +3, +4, etc. Is there a way to create this formatting. 

Thank you!

[Ksharp]

proc format;
picture fmt
0<-high='00000'( prefix='+');
run;

data x;
input x;
format x fmt.;
cards;
-1
-2
4
7
;
run;

[ChrisBrooks]

You'll need to create your own picture format e.g.

proc format;
	picture signed
	low - < 0 = '00000.0'(prefix='-')
	0 = '9'
	0 < - high = '00000.0' (prefix='+');
run;

data _null_;
	format x y z signed.;
	x=-33.4;
	y=0;
	z=3;
	put x= y= z=;
run;

The number of zeros in the format statement should be increased if you need larger numbers to accommodate their width - and the 9 against the number 0 indicates you want a zero rather than a missing value.

[Cat_Collector]

I have created a program with the code:

proc format;
	picture signed
	low - < 0 = '00000.0'(prefix='-')
	0 = '9'
	0 < - high = '00000.0' (prefix='+');
run;

How do I implement the format to my data sets? 

[ChrisBrooks]

This paper tells you how to save formats to a permanent catalog and make them available for use in your data sets http://support.sas.com/resources/papers/proceedings12/048-2012.pdf

[Cat_Collector]

@ChrisBrooks May I ask how you know to use 0= '9' to signify 0 (or is it no change in this instance)?

I found additional documentation explaining that this is a digit selector. However, the value that I am using the format on is a computed column with coalesce expression. In this case, when I change the 0= '9.9', I do not return a 0.0. My data set displays it as '0' anyways. Do you know how to get around that? I have tried changing the expression to coalesce(t1.field_name,0.0). Still the formatting does not make a difference when the field is 0.

[ChrisBrooks]

I must confess I'm not sure what you mean when you say that you're formatting a "computed column" - can you give the relevant code and data so I can see exactly what it is you're trying to do?

[ballardw]

---

@Cat_Collector wrote:

@ChrisBrooks May I ask how you know to use 0= '9' to signify 0 (or is it no change in this instance)?

I found additional documentation explaining that this is a digit selector. However, the value that I am using the format on is a computed column with coalesce expression. In this case, when I change the 0= '9.9', I do not return a 0.0. My data set displays it as '0' anyways. Do you know how to get around that? I have tried changing the expression to coalesce(t1.field_name,0.0). Still the formatting does not make a difference when the field is 0.

---

This sounds like you are creating a new variable but not applying the format to result. SAS will default to using a Best format if you do not explicitly assign a format. Which will display 0 as 0 with no decimals by default.

Or show the code of what you are doing with input example data.

Since your initial format named signed was defined with no actual digit selectors no value other than 0 would actually display it is very hard to determine which elements you are changing, what values you using for input, what you do to them and what you actually expect for a result.

[Cat_Collector]

@ballardw @ChrisBrooks Sorry for the delay in response.

@ballardw, you are correct in saying that we have created a new variable/computed column. It's actually a metric that adds multiple fields. However, the fields/columns used in the calculation are have null records. So, to get the computed record to calculate properly, we added coalesce in the expression like such:

(COALESCE(t4.COUNT_of_something1)+COALESCE(t2.COUNT_of_something2,0)+COALESCE(t1.COUNT_of_something3,0))-COALESCE(t3.Goal,0)

The result of this newly created variable/column are integers (negative, 0, and positive values). We wanted to format the result to display with a positive sign when the value is positive: +3, +7, etc.. I ran the code provided in a program and then added the format to the newly created metric in the project:

proc format;
	picture signed
	low - < 0 = '00000.0'(prefix='-')
	0 = '9'
	0 < - high = '00000.0' (prefix='+');
run;

 

The computed metric ('Diff') mostly appears fine with the exception when the value is 0. I was hoping it would display as '0.0' like positive and negative values. I assumed the decimal places were added using this portion of the code: 0 = '9'. So, I played around with making it '9.9' and '0.0' but the new variable still displays zero as '0' without a decimal place. Would you all happen to know whether I can fix this? Thank you.

[ballardw]

 proc format;
	picture signed
	low - < 0 = '00000.0'(prefix='-')
	0 = '0.0' (noedit)
	0 < - high = '00000.0' (prefix='+')
   ;
run;

The NOEDIT option means display text as written, not as digit selectors.

If your values are integers why do you care about showing a trailing 0? And if the values are not integers you are not going to get your expected display for values like 0.1  with that format.