How to format proc tabulate to produce decimal millions?

Question

My data is in the format of million and I need to tabulate my data as decimal millions in SAS.

I have data containing rent in millions of dollars i.e. 1,260,678.21 which I hope to tabulate as decimal millions i.e "1.3".

I attempted to create my own function to divide by as follows:

           /***/
* create a function to round 
*;

      proc fcmp outlib=work.function.sample;
      function round1000x(value);
      return(round(value/1000000,1));
      endsub; 
      run; 

  *
  * make function available
  *;
  options cmplib=work.func;

   *
   * create a format that uses the function
   *;
   proc format; 
     value round1000x.;
   run; 


 /*use this format in a proc tabulate statement*/

 proc tabulate data=my_data format=round1000x.;
var rent_cost;
table rent_cost;
  run;

However I am getting the error:

ERROR: The format ROUND1000X has a label that defines another format to be 
 loaded (named ROUND1000X), but this format could not be 
   successfully loaded (possibly for the same reason).

Would anyone be familiar with this problem?

Your VALUE statement does NOT look correct to me. Are you sure that syntax is correct? — Tom, Aug 07 '19 at 15:54
As @Tom pointed out, your code has syntactical errors. There is a straight of the bat example in the SAS documentation site that you could tweak and use: https://go.documentation.sas.com/?docsetId=proc&docsetTarget=n0kl9qj532rbqln187us4ao371h7.htm&docsetVersion=9.4&locale=en — SAS2Python, Aug 07 '19 at 16:57

score 0 · Answer 1 · answered Aug 07 '19 at 16:25

You can create a view that scales the value to the desired magnitude and tabulate that.

Example:

data have;
  do street = 1 to 50;
    rent = 7e5 + street * 1e5;
    output;
  end;
run;

data forTabulate / view=forTabulate;
  set have;
  rentM = round (rent / 1e6, 0.01);
run;

proc tabulate data=forTabulate;
  class street;
  var rentM;
  table street, rentM*sum='($M)'*f=5.2;
run;

How to format proc tabulate to produce decimal millions?

1 Answers1