topic Re: SAS EG: Numeric Format add positive (+) sign to values in SAS Enterprise Guide

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

Cat_Collector — Mon, 23 Oct 2017 13:32:17 GMT

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!

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

Ksharp — Mon, 23 Oct 2017 14:40:40 GMT


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

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

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

ChrisBrooks — Mon, 23 Oct 2017 14:44:50 GMT

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.

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

Cat_Collector — Mon, 23 Oct 2017 14:53:35 GMT

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?

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

ChrisBrooks — Mon, 23 Oct 2017 14:55:49 GMT

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

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

Cat_Collector — Mon, 23 Oct 2017 20:31:10 GMT

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

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

ChrisBrooks — Mon, 23 Oct 2017 23:10:27 GMT

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?

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

ballardw — Tue, 24 Oct 2017 19:00:09 GMT

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

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

Cat_Collector — Wed, 01 Nov 2017 12:48:05 GMT

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

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

ballardw — Wed, 01 Nov 2017 14:20:46 GMT

 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.