BookmarkSubscribeRSS Feed
wcp_fnfg
Obsidian | Level 7

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
3040.75
70601.17
100100100
7 REPLIES 7
Cynthia_sas
SAS Super FREQ

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
wcp_fnfg
Obsidian | Level 7

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

Cynthia_sas
SAS Super FREQ

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

ballardw
Super User

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.

Cynthia_sas
SAS Super FREQ

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;


tab_report_ratio.png
ballardw
Super User

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.

Cynthia_sas
SAS Super FREQ

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

cynthia

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1378 views
  • 0 likes
  • 3 in conversation