Hi:
The trick with PROC REPORT is that you cannot use AMOUNT for a numeric purpose and for an ORDERing purpose.Usage specifications in PROC REPORT determine whether a variable can be summarized or not. If AMOUNT is a GROUP or ORDER variable, then the AMOUNT value ceases to be available for addition or summarization.
And, on top of that, PROC REPORT only writes one report row at a time. So, when PROC REPORT is writing the summary row for Apple, it has no visibility of the summary amounts for Kiwi or Pear. PROC REPORT only writes 1 report row at a time and if it needs to do a summarizing break (subtotal or grand total) at the end of a group or the end of the report, that break line is put on the report at the right location. If AMOUNT is a GROUP or ORDER variable, it would not be available at the break, either, for summarizing purposes. But, there is a way to deal with this situation. You have to make sure that PROC REPORT knows what the report order is -- either by creating an ordering variable or by pre-summarizing the data.
So, you have to make 2 passes through the data, somehow in order to make/create a new variable that can be used for ordering. You could pre-summarize the data with PROC TABULATE, PROC MEANS, even PROC REPORT or PROC SQL. These previous forum postings had similar questions:
http://support.sas.com/forums/thread.jspa?messageID=17088䋀
http://support.sas.com/forums/thread.jspa?messageID=6757ᩥ
The program below uses a PROC SQL to create a new variable called ORDVAR in Approach #1. The code shows ORDVAR on the actual report, but once you understand how ORDVAR is working, you can put the NOPRINT option on the DEFINE statement for ORDVAR in order to use it, but hide it, on the final report. The original number of observations for WORK.TEST was preserved in Approach 1, which might be necessary, if you needed the detail rows or detail observations to do other types of processing or if you wanted a DETAIL report (#1b) as well as a SUMMARY report. Other approaches, such as PROC SQL, PROC MEANS or the PROC TABULATE approach would result in a completely summarized dataset (as shown in Approach #2).
cynthia
[pre]
** Approach 1: Put ORDVAR on every row, where ORDVAR is the total # for the group;
** Descending order happens in PROC REPORT step;
proc sql;
create table work.ordfruit as
select sum(amount) as ordvar, fruit, amount
from work.test
group by fruit;
quit;
proc print data=work.ordfruit;
title 'What does ORDVAR look like on every row';
run;
proc report data=ordfruit nowd;
title '1a) PROC REPORT Summary report showing ORDVAR';
title2 'Use the NOPRINT option to hide ORDVAR';
column ordvar fruit amount;
define ordvar / group descending;
** define ordvar / group descending noprint;
define fruit / group;
define amount / sum;
rbreak after / summarize;
compute after;
fruit = 'Total';
endcomp;
run;
proc report data=ordfruit nowd;
title '1b) PROC REPORT Detail report showing ORDVAR';
column ordvar fruit amount;
define ordvar / order descending noprint;
define fruit / order;
define amount / sum;
rbreak after / summarize;
break after fruit / summarize;
compute after fruit;
line ' ';
endcomp;
compute after;
fruit = 'Total';
endcomp;
run;
** Approach 2: completely summarize and order work.test;
** Descending order happens in PROC SQL step;
** PROC REPORT respects the order with ORDER=DATA;
proc sql;
create table work.sumfruit as
select fruit, sum(amount) as totamt
from work.test
group by fruit
order by totamt descending;
quit;
proc print data=work.sumfruit;
title 'what does summary data look like';
run;
proc report data=sumfruit nowd;
title '2) PROC REPORT Summary report using "pre-summarized" data';
column fruit totamt;
define fruit / order order=data;
define totamt / sum;
rbreak after / summarize;
compute after;
fruit = 'Total';
endcomp;
run;
[/pre]