turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- SAS Procedures
- /
- Is there a way to round the output from proc tabul...

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-05-2014 01:27 AM

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.

Accepted Solutions

Solution

08-05-2014
04:24 AM

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to KurtBremser

08-05-2014 04:24 AM

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**;

* create a function to round

*;

function xround100(value);

return( round(value,

endsub;

*

* make function available

*;

options cmplib=work.func;

*

* create a format that uses the function

*;

value xround100x

low - high = [xround100()]

;

*

* apply the format

*;

class origin type;

var invoice;

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

table type, origin*invoice;

All Replies

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Celia

08-05-2014 03:43 AM

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

---------------------------------------------------------------------------------------------

Maxims of Maximally Efficient SAS Programmers

How to convert datasets to data steps

How to post code

Maxims of Maximally Efficient SAS Programmers

How to convert datasets to data steps

How to post code

Solution

08-05-2014
04:24 AM

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to KurtBremser

08-05-2014 04:24 AM

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**;

* create a function to round

*;

function xround100(value);

return( round(value,

endsub;

*

* make function available

*;

options cmplib=work.func;

*

* create a format that uses the function

*;

value xround100x

low - high = [xround100()]

;

*

* apply the format

*;

class origin type;

var invoice;

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

table type, origin*invoice;

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Celia

08-05-2014 03:45 AM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Peter_C

08-10-2014 06:12 PM

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.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Celia

08-14-2014 10:19 AM

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