Million Dollar format

[Cho8]

Need below data in Million Dollar format.

Data:

629777257.7	3063114.557	47687382.31	31371401.09
548907467.6	7475029.274	30882553.57	26616491.86

Expected Format;

$     630 MM	$          3 MM	$        48 MM	$        31 MM
$     549 MM	$          7 MM	$        31 MM	$        27 MM

[Reeza]

This can get you started

https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.5&docsetId=proc&docsetTarget=n0kl9qj...

proc format;   
   picture bigmoney (fuzz=0)
      1E06-<1000000000='0000 M' (prefix='$' mult=.000001)
      1E09-<1000000000000='0000 B' (prefix='$' mult=1E-09)
      1E12-<1000000000000000='0000 T' (prefix='$' mult=1E-012);
run;
data mult;
   do i=5 to 12;
      x=16**i;
      put x=comma20. x= bigmoney.;
   end;
run;

Results:

LOG
Formatted Millions, Billions, and Trillions Dollar Amounts
x=1,048,576 x=$1 M
x=16,777,216 x=$16 M
x=268,435,456 x=$268 M
x=4,294,967,296 x=$4 B
x=68,719,476,736 x=$68 B
x=1,099,511,627,776 x=$1 T
x=17,592,186,044,416 x=$17 T
x=281,474,976,710,656 x=$281 T

This isn't exactly what you want but should be enough for you to figure out the solution from here.

---

@Cho8 wrote:

Need below data in Million Dollar format.

Data:

629777257.7	3063114.557	47687382.31	31371401.09
548907467.6	7475029.274	30882553.57	26616491.86

Expected Format;

$     630 MM	$          3 MM	$        48 MM	$        31 MM
$     549 MM	$          7 MM	$        31 MM	$        27 MM

---

[yabwon]

I had similar idea 🙂

proc format;
  picture milion (ROUND)
  low - high = '000,000,000 MM' (prefix="$ " mult=0.000001)
  ;
run;

B-)

[Cho8]

proc format;
picture bigm (ROUND)
low - high = '000,000,000,000 M' (prefix="$ " mult=0.000001)
;
run;

I have defined format like the above..

but iam not able to dispaly  the below number in Million format. Please advise

2.047219393619E-09

[yabwon]

Hi @Cho8 ,

You right, with the one I provided you won't be do display this number, sorry for that. I've mad an update.

proc format;
  picture milion (ROUND)
  low -< 0  = '000,000,009 MM' (prefix="-$ " mult=0.000001)
  0         = '0 MM'
  0 <- high = '000,000,009 MM' (prefix=" $ " mult=0.000001)
  ;
run;

data _null_;
  x = 2.047219393619E-09;
  put x = /
      x = 32.16 /
      x = milion. ;
run;

All the best

Bart

[Cho8]

-64684149.4007373

the negative number to be displayed like ($ 64,684,149). it should not take  Million format.

[yabwon]

Ok, one more update:

proc format;
  picture milion (ROUND)
  low -< 0  = '000,000,000,000,009)' (prefix="($ ")
  0         = '0 MM'
  0 <- high = '000,000,009 MM' (prefix=" $ " mult=0.000001)
  ;
run;

data _null_;
  x = 2.047219393619E-09;
  put x = /
      x = 32.16 /
      x = milion. ;

  x = -64684149.4007373;
  put x = /
      x = 32.16 /
      x = milion. ;

  x = 64684149.4007373;
  put x = /
      x = 32.16 /
      x = milion. ;
run;

B-)

[Cho8]

Thanks..

its not converting ENotation into $M Format..

its giving Enotation value..

[yabwon]

it does:

1    data _null_;
2      x = 6.46841494007373E7;
3      put x = /
4          x = 32.16 /
5          x = milion. ;
6    run;

x=64684149.401
x=64684149.4007373000000000
x=$ 65 MM
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

Bart

[Cho8]

i have to store it in datset alongside other variables,to use it for further calculations.

i have to use  FORMAT stmt..

but while using FORMAT it still showing Enotation..

is there a way to display using FORMAT..stmt.

[yabwon]

Could you share some code? It will be easier.

Bart

[yabwon]

When I run this:

proc format;
  picture bigm (ROUND)
  low -< 0  = '000,000,000,000,009)' (prefix="($ ")
  0         = '0 MM'
  0 <- high = '000,000,009 MM' (prefix=" $ " mult=0.000001)
  ;
run;

data test;
  Stress2008 = 2.047219393619E-09;
  Base2 = 600599374.795993;
  format Base2 Stress2008 bigm.;
run;
proc print data = test;
run;

I get the following output:

       The SAS System

Obs    Stress2008     Base2

 1       $ 0 MM      $ 601 MM

I don't understand what is not ok?

Bart

[Reeza]

It's very unusual to see three SET statements the way you've shown except in fairly complex programming for interleaving. Given the remainder of your code, I suspect you're doing something incorrectly at that stage.

---

@Cho8 wrote:

data euc.thirdrow(rename=(Base2=Base)); /* Should correct format of Base2*Base */
keep '$NCL'n Base2 Stress2008 ;
set euc.roll;
set euc.asofsep_sum1;
set euc.thirdrow_1 (drop='$NCL(Q3''20)'n);

Stress2008=(Stress/(Base2*Base));
format Base2  Stress2008 bigm.;

run;

Stress2008=2.047219393619E-09

Base2 =600599374.795993($601M)

i need Stress2008 also in $M format..

i can display Base2 as $601M using FOrmat bigm.,but not Stress2008

---

[novinosrin]

Hi @Cho8  Here is a solution by @ChrisNZ  that you may have already checked. Just in case-

https://communities.sas.com/t5/SAS-Programming/Format-Billions-as-0-0-B/td-p/491433