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|
03-26-2014 02:38 PM
03-26-2014 07:18 PM
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?
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.
03-27-2014 11:40 AM
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.
input cvar $ delq balq ;
a 30 40
b 70 60
c 100 100
ods _all_ close;
ods html file='c:\temp\testratio.html';
proc tabulate data=testratio;
title 'PROC TABULATE will automatically multiply by 100';
var delq balq;
delq balq delq*pctsum<balq>;
keylabel sum=' ' pctsum=' ';
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" ;
calcpct = delq.sum / balq.sum;
calc2 = delq.sum / balq.sum;
ods _all_ close;
03-27-2014 12:26 PM
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.