topic trying to sum a field in proc report-pdf in ODS and Base Reporting

trying to sum a field in proc report-pdf

GregINGov — Wed, 07 Nov 2007 14:02:52 GMT

I need to sum on one field in a report. I have tried a lot of different things in the code below and I am getting no summing at all. The only field I need a sum is “define perc/display'Percent'“

I do not need a sum on count and or any of the other fields.

I seem to be getting a line for the sum to go on with the contract number repeated, but instead of repeating the contract number I would
Like a label called "sub total" and than a space before the next contract number.

Can anyone help me out in working thru my problem of not knowing how to do this?

I am using SAS 9.1. Any help you can send me would be greatly appreciated.

ods pdf file='d:\greg1\CLEAN9.pdf';

proc report data=clean9 nowd headline headskip;

column cont_id prj_nbr count perc datevar;

define cont_id / group 'Contract';

define prj_nbr / display 'des number';

define count / display 'Number of Vouchers' ;

define perc/display'Percent';

define datevar/display'Last Voucher Date';

BREAK AFTER cont_id / SUMMARIZE DUL DOL ;

rbreak after / summarize;

run;

ODS PDF CLOSE;

Re: trying to sum a field in proc report-pdf

Cynthia_sas — Wed, 07 Nov 2007 15:09:05 GMT

Hi:
In order for PROC REPORT to give you a SUM, you need to tell it (in your DEFINE statement) to use a variable as an analysis variable. You have this:
[pre]
define perc/display 'Percent';
[/pre]
When PROC REPORT sees the usage of DISPLAY, it explicitly does NOT give you a SUM on that column. So you need to change the usage from DISPLAY to ONE of these DEFINE statements:
[pre]
define perc/analysis 'Percent';
define perc/analysis sum 'Percent';
define perc/sum 'Percent';
[/pre]

Any one of those versions of the statement would work for you.
1 -- because the default statistic for the analysis usage is SUM
2 -- because you are explicitly specifying the analysis usage and the statistic
3 -- because when you specify a statistic, the usage of analysis is assumed

In order to "rename" the contract id at the break, a compute block will essentially take what's in the field at the break and rename it (this code assumes that contract id is a character variable -- you'd have to do something different if it was a numeric variable):
[pre]
compute after cont_id;
cont_id = 'Subtotal';
endcomp;
[/pre]
This compute block can go anywhere in your PROC REPORT program. For readability purposes, I recommend putting it under the BREAK statement, so you know that a COMPUTE block will execute at the BREAK.

If you were sending output to the LISTING window, then you could add the SKIP option to your BREAK statement to get a skipped line after the summarized report row:

[pre]
BREAK AFTER cont_id / SUMMARIZE DUL DOL SKIP ;
[/pre]

However, SKIP is ignored for ODS HTML, RTF and PDF, so you have to make one more change to your COMPUTE block -- by adding a LINE statement to insert a blank line after the summary line:

[pre]
compute after cont_id;
cont_id = 'Subtotal';
line ' ';
endcomp;
[/pre]

For more help with PROC REPORT syntax, the on-line documentation and help files are very thorough and have some good examples. Or, you can contact Tech Support for help with your particular data and specific processing need.

cynthia

Re: trying to sum a field in proc report-pdf

GregINGov — Wed, 07 Nov 2007 16:22:00 GMT

Thanks Cynthia! What you told me is working great.