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

- Subscribe to 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
- Subscribe to RSS Feed
- Highlight
- 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
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

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
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

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

Maxims of Maximally Efficient SAS Programmers

Solution

08-05-2014
04:24 AM

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

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
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

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
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

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
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

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