DATA Step, Macro, Functions and more

Million Format

Reply
Frequent Contributor
Posts: 91

Million Format

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

 

 

Super User
Posts: 9,560

Re: Million Format

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
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Frequent Contributor
Posts: 91

Re: Million Format

Posted in reply to KurtBremser
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;
Super User
Posts: 9,560

Re: Million Format

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; 

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Community Manager
Posts: 3,353

Re: Million Format

Posted in reply to KurtBremser

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;

socialcount_output (1).png

 

 

Ask a Question
Discussion stats
  • 4 replies
  • 168 views
  • 1 like
  • 3 in conversation