How to format negative million range?

Accepted Solution Solved
Reply
Contributor
Posts: 74
Accepted Solution

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: 3,631

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;

View solution in original post


All Replies
Super User
Posts: 11,134

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: 3,631

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 336 views
  • 0 likes
  • 3 in conversation