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
- /
- How to calculate ratio with PROC TABULATE?

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

07-04-2013 12:22 PM

Hello,

is there any way to create a user-defined column either with "Summary Tables" Report in SAS Enterprise Guide or with SAS PROC TABULATE procedure?

I didn't find the solution so far.

What I would like to get, is following:

Total | Errors | Ratio | |

Year | Sum | Sum | Errors/Total |

2010 | 1000 | 10 | 1,00% |

2011 | 3000 | 25 | 0,83% |

2012 | 2000 | 32 | 1,60% |

Total | 6000 | 67 | 1,12% |

I have two variables TOTAL and ERRORS, I am looking for an option in SAS EG to implement my column RATIO, that would show ratio values.

I tried the possibility to calculate this column in a data set, and then to implement it as a separate value, Works OK, apart from the TOTAL value for RATIO. (I do not want this value to be summarized. It should show the ratio between SUM(Errors) / SUM(Total))

Thanks in advance for your ideas!

Regards

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

Posted in reply to Miron

07-04-2013 01:52 PM

What does your input data look like.

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

Posted in reply to Miron

07-04-2013 02:02 PM

Easy enough to do with PROC REPORT. Not sure how to get EG to do it.

data have;

input year total errors;

cards;

2010 1000 10

2011 3000 25

2012 2000 32

;

proc report nofs ;

column year total errors ratio ;

define year / order order=internal ;

define total / sum ;

define errors / sum ;

define ratio / computed format=percent8.2;

compute ratio ;

ratio = errors.sum / total.sum ;

endcomp;

rbreak after / ol summarize ;

run;

year total errors ratio

2010 1000 10 1.00%

2011 3000 25 0.83%

2012 2000 32 1.60%

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

6000 67 1.12%

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

07-04-2013 06:28 PM

Hi, Tom:

Your method is the only way with REPORT. The challenge with the List Report Wizard in EG is that you cannot do a COMPUTE block. So while it's good for getting the basic report structure down, the code in the COMPUTE block has to be added. Which means you have to export the REPORT code and add the COMPUTE block. (which means you have to understand the compute block).

There is also a way to do this with TABULATE (using your data), but it requires using a special denominator definition -- so also needs code.

cynthia

**proc format;**

** picture pct (round) low-high='009.99%';**

**run;**

**proc tabulate data=have;**

** class year;**

** var total errors;**

** table year all,**

** total errors errors*pctsum<total>*f=pct.;**

**run;**

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

Posted in reply to Cynthia_sas

07-05-2013 05:52 AM

Thanks Cynthia,

it is a very helpful solution as well. I ,personally, find the solution with PROC REPORT a bit strightforward.

Thank you all for proposed solutions!

Regards!

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

07-05-2013 05:51 AM

Thank you Tom!

That's exactly what I was looking for! As Cynthia mentioned, you do not have the COMPUTE block option in EG, so I wrote a code instead!