topic Re: Is there a way to round the output from proc tabulate to the nearest 100? in SAS Procedures

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

Celia — Tue, 05 Aug 2014 05:27:25 GMT

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.

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

Kurt_Bremser — Tue, 05 Aug 2014 07:43:55 GMT

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

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

Peter_C — Tue, 05 Aug 2014 07:45:12 GMT

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

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

BrunoMueller — Tue, 05 Aug 2014 08:24:52 GMT

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;

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

Celia — Sun, 10 Aug 2014 22:12:21 GMT

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.

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

ChrisHemedinger — Thu, 14 Aug 2014 14:19:21 GMT

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