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
- /
- ODS and Base Reporting
- /
- Ratio of two ColPctSums

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
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-26-2014 09:35 AM

Without creating a large number of data sets to handle this, I'm looking for a way through Tabulate or Report to produce a report which contains two pct of column values and also the ratio of the first to the second...

I'm trying to create this table with a list of roughly 20 class variables. I can macro the tables for everything except the ratio value (ideally, we'd subtract 1 from the ratio, but I'll take what I can get).

% of Delq | % of Bal | %Delq/%Bal |
---|---|---|

30 | 40 | .75 |

70 | 60 | 1.17 |

100 | 100 | 100 |

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

Posted in reply to wcp_fnfg

03-26-2014 02:38 PM

Hi,

COLPCTSUM is unique to PROC TABULATE, so it is unclear what your data looks like. You say that you have 20 "CLASS" variables, but don't show any in your "desired" output. For a question like this, the structure of your input data will need to be understood in order to determine the best approach. You can specify a custom denominator with TABULATE and you can perform more custom calculations with REPORT. Can you show what you've got for data and what your current program looks like? Also useful would be knowing what is your output destination.

cynthia

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

Posted in reply to Cynthia_sas

03-26-2014 02:41 PM

ColPctSum gets the first two columns, I'm looking for the third column: the ratio of the two ColPctSums.

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

Posted in reply to wcp_fnfg

03-26-2014 07:18 PM

Hi:

Still makes a difference what your input data looks like. Did you create an output data set from TABULATE and where are your CLASS variables? Do you need a report or a dataset with the final ratio? What is your destination of choice?

cynthia

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

Posted in reply to wcp_fnfg

03-27-2014 11:31 AM

I'd try one pass of the data with proc tabulate to create the individual column percent values then proc report on the resulting set to compute the desired ratios. Or since I tend to have issues with proc report, a data step to calculate the ratios and then use tabulate or print to display results.

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

Posted in reply to ballardw

03-27-2014 11:40 AM

Hi:

I don't understand what issues you have with PROC REPORT...the main issue that I see is that PROC REPORT does not automatically multiply the result of a percent division by 100 (hence the percent format will do the multiply by 100) and with PROC TABULATE, there is an automatic multiply by 100 with the percent-based keyword statistics.

Consider the following program using pre-summarized data. Personally, I think the PROC REPORT code is easier to "read" than the TABULATE code. No DATA step needed.

Cynthia

**data testratio;**

** infile datalines;**

** input cvar $ delq balq ;**

**return;**

**datalines;**

**a 30 40**

**b 70 60**

**c 100 100**

**;**

**run;**

**ods _all_ close;**

**ods html file='c:\temp\testratio.html';**

**proc tabulate data=testratio;**

** title 'PROC TABULATE will automatically multiply by 100';**

** class cvar;**

** var delq balq;**

** table cvar,**

** delq balq delq*pctsum<balq>;**

** keylabel sum=' ' pctsum=' ';**

**run;**

**proc report data=testratio;**

** title 'PROC REPORT will not automatically multiply by 100';**

** column cvar delq balq calcpct calc2;**

** define cvar / order;**

** define delq / sum;**

** define balq / sum;**

** define calcpct / computed;**

** define calc2 / computed f=percent9.2 "Calc2 uses PERCENT format" ;**

** compute calcpct;**

** calcpct = delq.sum / balq.sum;**

** endcomp;**

** compute calc2;**

** calc2 = delq.sum / balq.sum;**

** endcomp;**

**run;**

**title;**

**ods _all_ close;**

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

Posted in reply to Cynthia_sas

03-27-2014 12:26 PM

Cynthia;

My 'issue" with Report is mostly familiarity. About 95% of my clients report requirements involve multiple nested row and column layouts so i spend much more time with Tabulate and Proc freq usually works for most of the rest. So when I use Report I almost have to start from scratch.

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

Posted in reply to ballardw

03-27-2014 06:56 PM

Ah, got it! I thought you were saying that PROC REPORT didn't give you what you needed.

cynthia