Is there a way to round the output from proc tabulate to the nearest 100?

[Celia]

Hi everyone,

I was wondering if anyone knows of a way to display the output from proc tabulate to the nearest 100, or a format that I could apply?  At the moment, I'm outputting to an excel spreadsheet and using the rounding function in excel, but it's pretty tedious.

Cheers,

Celia.

[Kurt_Bremser]

You can create a picture format with the round option and use that in the respective table definition.

An example is given in this document: http://www2.sas.com/proceedings/sugi30/243-30.pdf (search for "round")

[BrunoMueller · Accepted solution]

You can take advantage that with SAS9.4 we can create formats, that use functions to return the value.

here is an example:

*
* create a function to round
*;
proc fcmp outlib=work.func.sample;
  function xround100(value);
    return( round(value, 100) );
  endsub;
run;

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

*
* create a format that uses the function
*;
proc format;
  value xround100x
    low - high = [xround100()]
  ;
run;

*
* apply the format
*;
proc tabulate data=sashelp.cars;
  class origin type;
  var invoice;

  table type, origin*invoice*f=xround100x.;
  table type, origin*invoice;
run;

[Peter_C]

Hi Celia

PROC FORMAT allows you to create your own format to do this.

The PICTURE statenent supports a multiplier which you could set as MULT= .01 to achieve that "divide by 100" and a ROUND option to complete the objective.

Something like:

proc format ;

picture hundth (round) 

       low-high = '0,000,009'(mult=.01) ;

run;

Use it in a tabulate table like

table ( rowclass all),( columnclass all)

       *analysisVar*sum=' '*f= HUNDTH. ;

good luck

peterC

[Celia]

Thanks Peter.C,

This gives me the right results. eg. if the number was 722, the output gives me 7.  Is there a way to make the output 700 rather than 7?

Cheers,

Celia.

[ChrisHemedinger]

For those looking for more details about how to use a custom function in a SAS format definition, see:

Base SAS(R) 9.4 Procedures Guide, Third Edition

And also Rick Langston's paper from SAS Global Forum 2012:

Using the New Features in PROC FORMAT

Chris