topic Re: Million Format in SAS Programming

Million Format

Ronein — Mon, 26 Feb 2018 06:50:22 GMT

Hello

I am trying to create million format.

Please look at this example

proc format;
picture million (round fuzz=0)
0 -< 50000 = '00.0' (prefix='' mult=0.0)
50000 -< 1000000 = '00.0' (prefix='' mult=0.00001)
1000000 -< 1000000000='0000.0' (prefix='' mult=.00001);
run;

data tbl1 ;
x = 1200000;
y = 230700000;
z=1000;
format _all_ million. ;
run;

title;
proc print noobs;run;

In the print we can see that value of z is null but we need to see value of 0.1.

What is the siolution and why did it happen?

One more question: I saw in internet using statment : put (_all_)(=million.);

What is it doing ?

thanks

Ron

Re: Million Format

Kurt_Bremser — Mon, 26 Feb 2018 08:53:30 GMT

First of all, a mult factor of 0.0 will automatically set everything to zero.

Second, you need to force the display of a leading zero before the dot by using '9' instead of '0':

proc format;
picture million (round fuzz=0)
0 -< 50000 = '09.0' (prefix='' mult=0.001)
50000 -< 1000000 = '00.0' (prefix='' mult=0.00001)
1000000 -< 1000000000='0000.0' (prefix='' mult=.00001);
run;

If you run that with your other code, you get this result:

 x       y       z

1.2    230.7    0.1

Re: Million Format

Ronein — Mon, 26 Feb 2018 12:03:55 GMT

Thank you
If we add another value than after proc print we are getting an error value
Please look what I need
ActualValue_In_USD
1,200,000
230,700,000
1,000
49,000
Desired Values In Millions
1.2
230.7
0.001
0.049

IF you run this code then you get for W value 4.9 but it is not
correct and need to be 0.049

proc format;
picture million (round fuzz=0)
0 -< 50000 = '09.0' (prefix='' mult=0.001)
50000 -< 1000000 = '00.0' (prefix='' mult=0.00001)
1000000 -< 1000000000='0000.0' (prefix='' mult=.00001);
run;
data tbl1 ;
x = 1200000;
y = 230700000;
z=1000;
w=49000;
format _all_ million. ;
run;
title;
proc print noobs;run;

Re: Million Format

Kurt_Bremser — Mon, 26 Feb 2018 12:13:13 GMT

Just tinker around with the mult factors and the number of decimal places, represented by the format characters 0 or 9:

proc format;
picture million (round fuzz=0)
  0 -< 1000000 = '9.000' (prefix='' mult=0.001)
  1000000 - high ='0000.0' (prefix='' mult=.00001)
;
run;

data test;
input x;
format x million. ;
cards;
1200000
230700000
1000
49000
;
run;

title;
proc print noobs;
run;

Re: Million Format

ChrisHemedinger — Mon, 26 Feb 2018 12:43:18 GMT

Here's another approach that applies the "social media" rounding format: "k" for thousands, "M" for millions, etc. I described it in this blog post. Example code here:

/* Format your numbers like a social media superstar      */
/* Provided for example purposes only by Chris Hemedinger */
libname library (work);
proc format lib=library;
  value linkedin    
	500 - high ='500+'
	;
run;

data followers;
  length name $ 40 followers 8 displayed 8;
  format displayed linkedin.;
  infile datalines dsd;
  input name followers;
  displayed = followers;
datalines;
Chris Hemedinger, 776
Kevin Bacon, 100543
Norman Newbie, 3
Colleen Connector, 499
;
run;


proc format lib=library;
picture social (round)
 1E03-<1000000='000K' (mult=.001  )
 1E06-<1000000000='000.9M' (mult=.00001)
 1E09-<1000000000000='000.9B' (mult=1E-08)
 1E12-<1000000000000000='000.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;