Solved
Contributor
Posts: 74

# How to format negative million range?

Hello!

I want to format the negative value as K, million, billion, such as -100,000,0 as -\$1.0m, however the following code could not help me get it, would you please help me? Thank you so much!

proc format ;
picture social(round)
-1E09- < -1E06 = '000.9B' (prefix='-' mult=1E-11)
-1E06- < -1E03 = '000.9M' (prefix='-' mult=1E-08)
-1E03- < 0 = '000K' (prefix='-' mult=.001)
1E03-<1000000='000K' (prefix='\$' mult=.001 )
1E06-<1000000000='000.9M' (prefix='\$' mult=.00001)
1E09-<1000000000000='000.9B' (prefix='\$' mult=1E-08)
1E12-<1000000000000000='000.9T' (prefix='\$' mult=1E-11);
run;

/* test data */
data likes (keep=actual displayed);
format actual comma20.
displayed social.;
do i=1 to 12;
actual=-16**i;
displayed = actual;
output;
end;
run;

Accepted Solutions
Solution
‎08-03-2016 02:38 PM
SAS Super FREQ
Posts: 4,237

## Re: How to format negative million range?

I think this is what you are asking for. Note that the multiplier is one power less than what you might expect:

``````proc format ;
picture social(round default=32)
-1E15 -< -1E12 = '009.9T' (prefix='-' mult=1E-11)
-1E12 -< -1E09 = '009.9B' (prefix='-' mult=1E-8)
-1E09 -< -1E06 = '009.9M' (prefix='-' mult=1E-5)
-1E06 -< -1E03 = '009.9K' (prefix='-' mult=1E-2)
1E03 -<  1E06 = '009.9K' (mult=1e-2)
1E06 -<  1E09 = '009.9M' (mult=1e-5)
1E09 -<  1E12 = '009.9B' (mult=1E-8)
1E12 -<  1E15 = '009.9T' (mult=1E-11);
run;

/* test data */
data likes (keep=actual displayed);
format actual comma20.
displayed social.;
do i=1 to 12;
actual=-16**i;
displayed = actual;
output;
end;
run;

proc print; run;``````

All Replies
Super User
Posts: 13,498

## Re: How to format negative million range?

Provide an actual dataset in the form of a data step what you expect for the results for each value of your example data.

That will make it clearer what we need to look at.

Contributor
Posts: 74

## Re: How to format negative million range?

Do you know how to format all the negative values correctly?

proc format ;
picture social(round)
-1E09- < -1E06 = '000.9B' (prefix='-' mult=1E-11)
-1E06- < -1E03 = '099999.999M' (prefix='-' mult=1E-08)
-1E03- < 0 = '000K' (prefix='-' mult=.0000001)
1E03-<1000000='000K' (prefix='\$' mult=.001 )
1E06-<1000000000='000.9M' (prefix='\$' mult=.00001)
1E09-<1000000000000='000.9B' (prefix='\$' mult=1E-08)
1E12-<1000000000000000='000.9T' (prefix='\$' mult=1E-11);
run;

/* test data */
data likes ;
format actual social.;
input actual;
datalines;

-16
-256
-4096
-65536
-1048576
-16777216
-26843456
-4294967296
-68719476736
-1099511627776
-17592186044416
-281474976710656
0
4096
65536
1048576
16777216
268435456
4294967296
68719476736
1099511627776
17592186044416
281474976710656
;
run;
Solution
‎08-03-2016 02:38 PM
SAS Super FREQ
Posts: 4,237

## Re: How to format negative million range?

I think this is what you are asking for. Note that the multiplier is one power less than what you might expect:

``````proc format ;
picture social(round default=32)
-1E15 -< -1E12 = '009.9T' (prefix='-' mult=1E-11)
-1E12 -< -1E09 = '009.9B' (prefix='-' mult=1E-8)
-1E09 -< -1E06 = '009.9M' (prefix='-' mult=1E-5)
-1E06 -< -1E03 = '009.9K' (prefix='-' mult=1E-2)
1E03 -<  1E06 = '009.9K' (mult=1e-2)
1E06 -<  1E09 = '009.9M' (mult=1e-5)
1E09 -<  1E12 = '009.9B' (mult=1E-8)
1E12 -<  1E15 = '009.9T' (mult=1E-11);
run;

/* test data */
data likes (keep=actual displayed);
format actual comma20.
displayed social.;
do i=1 to 12;
actual=-16**i;
displayed = actual;
output;
end;
run;

proc print; run;``````
Contributor
Posts: 74

## Re: How to format negative million range?

Thank you so much! That is amazing!!
☑ This topic is solved.