topic Re: Ratio of two ColPctSums in ODS and Base Reporting

Ratio of two ColPctSums

wcp_fnfg — Wed, 26 Mar 2014 13:35:03 GMT

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

Re: Ratio of two ColPctSums

Cynthia_sas — Wed, 26 Mar 2014 18:38:56 GMT

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

Re: Ratio of two ColPctSums

wcp_fnfg — Wed, 26 Mar 2014 18:41:31 GMT

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

Re: Ratio of two ColPctSums

Cynthia_sas — Wed, 26 Mar 2014 23:18:24 GMT

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

Re: Ratio of two ColPctSums

ballardw — Thu, 27 Mar 2014 15:31:15 GMT

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.

Re: Ratio of two ColPctSums

Cynthia_sas — Thu, 27 Mar 2014 15:40:22 GMT

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;

Re: Ratio of two ColPctSums

ballardw — Thu, 27 Mar 2014 16:26:29 GMT

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.

Re: Ratio of two ColPctSums

Cynthia_sas — Thu, 27 Mar 2014 22:56:11 GMT

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

cynthia