topic Re: Problem to get grand total using proc report in SAS Enterprise Guide

Problem to get grand total using proc report

SanjayM — Thu, 06 Sep 2007 20:52:51 GMT

Hello,

I am not able to get grant totals for my report.
for example:-
My report looks like this

Supplier: ABC
Branch GRN Order_No Order_type Product_code Date Goods_value
XYZ 1 001 R 123 05/09/07 1000
----------
Total for ABC 1000

Supplier: PQR
Branch GRN Order_No Order_type Product_code Date Goods_value
IJK 2 0012 R 124 05/09/07 2000
----------
Total for PQR 2000
------------
Total for ALL suppliers 3000

I am not able to generate the above line for grand total for all suppliers.

Code which i am using to generate my output is this

proc report data=WORK.QUERY3051 nowd headskip split='*' spacing=1;
by Cf_Su_Name ;
title2 'SUPPLIER : #byval(Cf_Su_Name) ' ;
column Cf_Su_Name Gb_Br_Name
Pi_In_Grnno Pi_In_Grn Pi_In_Orderno Pi_In_Ordtype Pi_In_Partno
Pi_In_Grndate Pi_In_Recqty Pi_In_Recgoods Pi_In_Recprice;
define Cf_Su_Name /group noprint order=formatted width=25 ;
define Gb_Br_Name / group order=formatted width=25;

define Pi_In_Orderno / order width=6;
define Pi_In_Ordtype / 'Order*Type' flow width=6;
define Pi_In_Grndate / order width=10;
define Pi_In_Recqty / 'Quantity*Received' analysis SUM format=8.;
define Pi_In_Recgoods / 'Net*Goods*Value' SUM format=comma16.2;
define Pi_In_Recprice / 'Net*Purchase*Cost' SUM format=comma16.2;

*break after Gb_Br_Name / summarize OL skip;
break after Cf_Su_Name / summarize OL skip ;
*rbreak after / summarize;

compute after Cf_Su_Name ;
Gb_Br_Name = ' Total for ' || trim(Cf_Su_Name ) ;
endcomp;
run;

Can you modify this code to generate me the grant total of all suppliers.

Re: Problem to get grand total using proc report

Cynthia_sas — Thu, 06 Sep 2007 23:42:43 GMT

You are probably running into this issue:
http://support.sas.com/faq/005/FAQ00581.html where PROC REPORT does not summarize across BY groups. Every BY group is treated as a separate entity.

If you go back and look at this forum posting,
http://support.sas.com/forums/thread.jspa?threadID=1840&tstart=0
you will see that the examples that have BY group processing do NOT use the RBREAK statement.

If you have an immediate need, then your only other choice is to contact Tech Support for further help. If you are having problems with code that's been posted, once you modify the code for your particular data, then Tech Support can help you figure out how to modify these code examples for your particular need, using your data. There are at least 3 ways that I can think of that you could use to get your final grand total without using BY group processing -- and still have your header text (like REGION = XXXXX) on/in the table(s).

No BY group processing involved:
1) USE PAGE option on Break statement for the NOPRINT var + RBREAK After + COMPUTE BEFORE _PAGE_ + COMPUTE AFTER -- also will have to set some temporary variables and flags so you get the correct text string at the final grand total.
2) USE RBREAK After + COMPUTE BEFORE NOPRINT var + Break Statement on the NOPRINT var (but without PAGE option) + COMPUTE AFTER
3) Use RBREAK After + COMPUTE BEFORE NOPRINT var + with PAGE option on BREAK for NOPRINT var + COMPUTE AFTER

However, I am teaching class this week and my time to experiment with examples is limited. If you can't figure out the syntax from the documentation or by modifying previous examples, your best bet would be to seek help from Tech Support.

cynthia

Good PROC REPORT help topics are entitled:
Creating Detail and Summary Reports with the REPORT Procedure
Overview: REPORT Procedure
Concepts: REPORT Procedure
How PROC REPORT Builds a Report

Re: Problem to get grand total using proc report

SanjayM — Fri, 07 Sep 2007 07:34:55 GMT

Can you give me an example or can you modify my code given in my posting to use one of 3 options.
I am not able to get you how will I able to get grand total as well as supplier=xxxx
on the top without using by statement.

Re: Problem to get grand total using proc report

Cynthia_sas — Fri, 07 Sep 2007 13:35:07 GMT

Hi:
Usually, if I have a program in my folders, I can just cut and paste it in here. However, for this question, I'd have to modify several programs that were doing a lot of other stuff as well as what you want and the 3 programs I was thinking of were all using DIFFERENT data -- so I'd have to swap out the data, too. Since I'm teaching this week, I don't have time to devote to the task.

However, the folks in Tech Support have lots of experience with PROC REPORT and their job is to help with urgent or immediate issues. I really suggest that you contact Tech Support for help:
http://support.sas.com/techsup/contact/index.html

cynthia