Desktop productivity for business analysts and programmers

How to format a column of numbers with a new format

Reply
Occasional Contributor PNC
Occasional Contributor
Posts: 5

How to format a column of numbers with a new format

[ Edited ]

I have a column of numbers that I need to have in a specific format.  At this point in time it is set up as currency: $00.00.  I need it to read 00.00+.  I tried to find a format that is already available and didn't see one. I thought of the User Defined Format but couldn't figure out how to get it to work for this.  Any suggestions?

 

I am not a programmer, so if there is a way to do this without writing code that would be great.  If it can only be done in code I would need to get it from start to finish.  I have no code writing experience whatsoever.

 

This will be used in SAS Enterprise Guide.

 

Thank you!

Super User
Posts: 13,947

Re: How to format a column of numbers with a new format

If I understand what you want then this is one approach.

proc format library=work;
picture trailsign
low -<0  ='0000009.99-' 
0        = '0.00' (noedit)
0<- high ='0000009.99+'
;
run;

data example;
  do x= -10 to 10 by 0.15,0;
  output;
  end;
run;

proc print data=example noobs;
   format x trailsign.;
run;

Note that when discussing format it is a good idea to indicate the range of values to display, number of decimals required and actual breakpoints in the changes if needed.

 

The Picture type format uses digit selectors, 0 means that if that position in a value is not populated do not show anything, 9 (or any non-zero but 9 is traditional) forces the appearance of a 0 even when there is no value. 9.99 will display 1 as 1.00 for example. I used this option since that is what your example showed.

You did not mention negative values at all but I incleded one. values of 0 usually are an exception to most rules and will often not display as desired with digit selectors so used an explicit value with the (noedit) to tell the picture statement not to treat the 0.00 as digit selectors.

 

The format will have to be in your FMTSEARCH path when used. So you may need to execute the format code every session or learn about the options around the FMTSEARCH system option and how to create the format in a permanent library and point the path to that library.

Occasional Contributor PNC
Occasional Contributor
Posts: 5

Re: How to format a column of numbers with a new format

Thank you for walking me through this. I am just learining SAS EG and need all the help I can get. I didn't even think of any of what you suggested.

 

The range of values can be anywhere from 1 - 10,000

 

Out to 2 decimals please.

 

What do you mean by breapoints in the changes?

 

In this situation there will not be any negatives, but that is possible in other projects where I will need the same formatting.  It will need to show with the negative at the end of the number, just like the positive: 00.00-, so thanks for including.

 

I believe the Picture type format you reference will work for this situation.

 

Do I just add what you have below to the code tab in the step I need to use it?  Do I put it after the QUIT; that's already there?

What is the FMTSEARCH you are referencing?  Again, not familiar with writing code at all so I apolgize if this is something basic that I should already know.

Super User
Posts: 13,947

Re: How to format a column of numbers with a new format

Breakpoint for changes suppose you are dealing with a large range of values. Less than 10000 you may want to show the value

at greater you may want to show something like 125K instead to 125,000

or 6B instead of 6000000000.

So you would have a different appearance for specific ranges.

 

The Proc Format code has to be run before any use of the format. If you are going to use this format often then you would either want to execute it at the start of each SAS session or learn about permanent libraries and the option to look there for formats, FMTSEARCH.

 

Any time afte the format is available it can be used anywhere any of the SAS supplied formats would be used.

It sounds like you may be using Enterprise Guide. I really can't help with specifics as I don't use that.

Occasional Contributor PNC
Occasional Contributor
Posts: 5

Re: How to format a column of numbers with a new format

Thanks again.  Yes, I am using SAS EG.  There is no Breakpoint for changes for this project.  I don't believe it will ever be greater than 10,000. 

Ask a Question
Discussion stats
  • 4 replies
  • 165 views
  • 2 likes
  • 2 in conversation