BookmarkSubscribeRSS Feed
deleted_user
Not applicable
Is there a way to change the denominator in a pctsum within the table statement of a PROC TAB?
5 REPLIES 5
Cynthia_sas
SAS Super FREQ
Hi!
There is a way to change the denominator using PROC TABULATE code. It involves the use of the angle brackets '<>' and understanding which of the class variables you list inside the angle brackets. It is described in the SAS Help files under the topic:
"Specifying a Denominator for the PCTSUM Statistic".

So if you go to SAS Help and search for the words, "denominator" and "PCTSUM", you should find a program that illustrates how you would write this code. This article/sample also has a more advanced example of using a special denominator definition with PROC TABULATE: http://support.sas.com/documentation/periodicals/obs/obswww16/index.html

However, if all you want is a percent of ROW or percent of COLUMN (instead of percent of Grand Total), keep in mind that you can select ROWPCTSUM or COLPCTSUM as the statistic for PROC TABULATE from within the EG Summary Table drag 'n drop interface.

One good way to test the ROWPCTSUM and COLPCTSUM statistics is to take a small dataset, like SASHELP.CLASS or SASHELP.SHOES and create several tables using these different statistics and compare the results to see which might be what you want. Be sure to use the ALL class variable in the ROW and COLUMN dimension so you can see where the 100%'s are showing up (lower right hand cell, along the rows or along the columns). Also, be aware of the fact that PROC TABULATE does an automatic multiply by 100 when calculating these statistics.
Good luck!
cynthia
deleted_user
Not applicable
Thank you, but I am actually looking for how to do this within Enterprise Guide.
Cynthia_sas
SAS Super FREQ
I did check with the EG folks in R&D and here's what I found out. The EG SUMMARY Tables interface is designed to handle most, but not all, of the things that you can do with PROC TABULATE.

Specifying a denominator is considered to be an advanced usage of PROC TABULATE, so it falls outside the realm of the drag and drop interface. When we cover PROC TABULATE in the reporting classes, it is covered in the advanced class -- so even among folks who write code -- it is an advanced and somewhat seldom used capability. Once ROWPCTSUM, COLPCTSUM, PAGEPCTSUM and REPPCTSUM appeared on the scene -- hardly anybody needed to learn about denominators anymore. And, a lot of folks use PROC REPORT and COMPUTE blocks when they have calculations like this that they need to do.

However, once you design your Summary task, using drag and drop interface, you can export the TABULATE code to a file. Once you have exported the code, then you can modify it using the techniques described in the links above. Tech Support could help you modify the exported code, and explain how to include the code back into your EG process as a code node.

cynthia
deleted_user
Not applicable
Thank you for your help. You are right about the ROWPCTSUM, COLPCTSUM, AND PAGEPCTSUM. Unfortunately, being able to change the denominator is very beneficial for calculating % such as delinquency, where you have a delinquent dollar and want to divide by the balance in your summary.
Cynthia_sas
SAS Super FREQ
I know. And this is exactly the kind of thing that a lot of my students use PROC REPORT for. Consider the difference between these two programs.
[pre]
ods html file='c:\temp\denom_tab.html' style=sasweb;
proc tabulate data=sashelp.prdsale;
title 'TABULATE: actual as percentage of predict';
class division;
var actual predict;
table division all,
actual*sum predict*sum actual*pctsum;
run;
ods html close;
[/pre]
With PROC TABULATE (above), I can calculate what % Actual is of Predicted amount using a denominator definition, but with PROC REPORT I can also do that AND have just about any other kind of calculation performed that I want.
[pre]
ods html file='c:\temp\denom_rep.html' style=sasweb;
proc report data=sashelp.prdsale nowd split='~';
title 'REPORT: actual as percentage of predict + MORE';
column division actual predict pctpred compcalc;
define division /group '~Division';
define actual /sum '~Actual';
define predict /sum '~Predict';
define pctpred /computed f=percent9.2
'Actual~is what~% of Predict';
define compcalc /computed
f=dollar14.2 'Complicated~Calc';
compute pctpred;
pctpred = (actual.sum / predict.sum );
endcomp;
compute compcalc;
compcalc = actual.sum * .001 + predict.sum /3.14167;
endcomp;
rbreak after / summarize;
run;
ods html close;
[/pre]
In the PROC REPORT example, I am calculating 2 columns, one is a fairly straightforward division of one variable's sum by another variable's sum. But the other column (COMPCALC) is a calculation that I could NOT perform using PROC TABULATE.

When you start to venture beyond routine report requirements, sometimes you have to use code nodes in EG to get the job done. In some industries, the standard EG point and click interfaces are sufficient for ALL their report needs. But in other industries, if EG does not meet all your report requirements through the standard interfaces, then you still have the alternative (code-based) method available to you. Since EG generates code behind the Wizard and Task screens, it is fairly easy to grab the code and make changes.

Good luck!
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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 5 replies
  • 885 views
  • 0 likes
  • 2 in conversation