turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- BI
- /
- Enterprise Guide
- /
- Prom Tabulate in EG

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-20-2006 01:46 PM

Is there a way to change the denominator in a pctsum within the table statement of a PROC TAB?

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to deleted_user

10-20-2006 05:49 PM

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

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Cynthia_sas

10-23-2006 12:02 PM

Thank you, but I am actually looking for how to do this within Enterprise Guide.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to deleted_user

10-24-2006 12:34 PM

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

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Cynthia_sas

10-24-2006 02:42 PM

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.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to deleted_user

10-24-2006 03:21 PM

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

[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