topic Re: Million Dollar format in SAS Programming

Million Dollar format

Cho8 — Wed, 25 Nov 2020 17:03:03 GMT

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

Re: Million Dollar format

Reeza — Wed, 25 Nov 2020 17:10:26 GMT

This can get you started

https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.5&docsetId=proc&docsetTarget=n0kl9qj532rbqln187us4ao371h7.htm&locale=en

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

Re: Million Dollar format

novinosrin — Wed, 25 Nov 2020 17:14:51 GMT

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

Re: Million Dollar format

yabwon — Wed, 25 Nov 2020 17:15:58 GMT

I had similar idea 🙂

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

B-)

Re: Million Dollar format

Cho8 — Thu, 26 Nov 2020 09:53:47 GMT

I need only million format and if is negative ('-') then it should display ($6354466...)

if its 629777257.72 it should give $ 630 Million

if its 3063114.557 it should give $3M.

i have to store it in dataset and use the dataset for further calculations..

if it is between 0 and 1..it should give 1 Million

FYI: the values are being imported from EXCEL..

Re: Million Dollar format

Cho8 — Thu, 26 Nov 2020 07:51:42 GMT

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

Re: Million Dollar format

yabwon — Thu, 26 Nov 2020 08:04:58 GMT

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

Re: Million Dollar format

Cho8 — Thu, 26 Nov 2020 08:24:34 GMT

-64684149.4007373

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

Re: Million Dollar format

yabwon — Thu, 26 Nov 2020 08:29:52 GMT

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-)

Re: Million Dollar format

Cho8 — Thu, 26 Nov 2020 14:15:48 GMT

Thanks..

its not converting ENotation into $M Format..

its giving Enotation value..

Re: Million Dollar format

yabwon — Thu, 26 Nov 2020 14:22:24 GMT

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

Re: Million Dollar format

Cho8 — Thu, 26 Nov 2020 15:08:02 GMT

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.

Re: Million Dollar format

yabwon — Thu, 26 Nov 2020 15:12:47 GMT

Could you share some code? It will be easier.

Bart

Re: Million Dollar format

Cho8 — Tue, 01 Dec 2020 14:31:04 GMT

Re: Million Dollar format

yabwon — Thu, 26 Nov 2020 19:28:46 GMT

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

Re: Million Dollar format

Reeza — Thu, 26 Nov 2020 20:21:31 GMT

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

Re: Million Dollar format

ChrisNZ — Fri, 27 Nov 2020 01:24:15 GMT

It'd be nice if you didn't change your requirements as you go.

This does what you asked. Modify to suit if your needs change again.

proc format;
  picture million (ROUND)
    low  -< 0    = '000,000,000,000,009)' (prefix="($ ")
    0            = 'Zero'
    0    -  1e6  = '1 Million'
    1e6 <-< 10e6 = '000,000,009 M'        (prefix=" $ " mult=0.000001)
    10e6 - high  = '000,000,009 Million'  (prefix=" $ " mult=0.000001)
  ;
run;

data _null_;
  A =          1.2; putlog A= million. ;
  A = -629777257.2; putlog A= million. ;
  A =  629777257.2; putlog A= million. ;
  A =    3063114.2; putlog A= million. ;
run;

A=1 Million
A=($ 629,777,257)
A=$ 630 Million
A=$ 3 M