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 |
Hi,
ColPctSum gets the first two columns, I'm looking for the third column: the ratio of the two ColPctSums.
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
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.
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;
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.
Ah, got it! I thought you were saying that PROC REPORT didn't give you what you needed.
cynthia
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.