BookmarkSubscribeRSS Feed
Cat_Collector
Calcite | Level 5

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!

9 REPLIES 9
Ksharp
Super User

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

data x;
input x;
format x fmt.;
cards;
-1
-2
4
7
;
run;
ChrisBrooks
Ammonite | Level 13

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
Calcite | Level 5

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
Ammonite | Level 13

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
Calcite | Level 5

@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
Ammonite | Level 13

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
Super User

@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
Calcite | Level 5

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

 SAS Add Format.JPG

 

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.

SAS Formating 0.JPG

ballardw
Super User
 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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 9 replies
  • 5003 views
  • 1 like
  • 4 in conversation