Order Data within groups?

[deleted_user]

I have a report that shows sales data broken down in groups as such:
GMM
>> Div
>>> Dept
>>> Dept total
>> Div total
GMM total

Currently my Proc Report is Ordered by GMM Div Dept
I would like to Order in descending order by the sales number so that the GMM with the largest value pops to the top and within that GMM the Div are ordered descending and within the Div the Depts are descending.
Is this possible?

[deleted_user]

This example might prove to be easier to follow. It uses the sashelp.prdsale dataset.
So in this example, USA should bubble to the top since this group has the greatest total. Within USA the order would be WEST and then EAST.
This group would be followed by Canada with subtotal EAST and then WEST, etc.

proc report data=sashelp.prdsale;
col country region predict actual;
define country / group;
define region / group ;
define predict / sum ;


break after country / summarize;
rbreak after / summarize;

[David_SAS]

Patrick,

I'm not aware of a way to do what you want using PROC REPORT or any other Base reporting procedure.

-- David Kelley, SAS

[Cynthia_sas]

David:
I was thinking (but haven't had a chance to try it out yet) that you could summarize with PROC MEANS -- then use aliasing and NOPRINT with PROC REPORT on the summary data set to achieve the right order.

It's an interesting problem. I'm in meetings this week but if I have time to play with it and come up with something, I'll post it.

cynthia

[Cynthia_sas]

Patrick:
Although PROC REPORT will not do this directly in one pass through the data, you CAN get this output from PROC REPORT:
[pre]
Using ORDVAR to Determine Order for PROC REPORT
Note that ORDVAR is based on sum of PREDICT var
You would have to change the logic if you wanted this based on ACTUAL

Predicted
Country Region Sales Actual Sales
U.S.A. EAST $120,587.00 $118,229.00
WEST $121,135.00 $119,120.00
U.S.A. $241,722.00 $237,349.00

CANADA EAST $120,646.00 $127,485.00
WEST $112,373.00 $119,505.00
CANADA $233,019.00 $246,990.00

GERMANY EAST $117,579.00 $124,547.00
WEST $113,975.00 $121,451.00
GERMANY $231,554.00 $245,998.00

[/pre]

... although not with the aliasing method that I had originally been toying around with. It turned out to be easier to just use PROC SQL to create a variable called ORDVAR that would then be used with PROC REPORT.

cynthia

[pre]
** the code;
ods listing close;
options nodate nonumber nocenter;
** first create a table with ONLY COUNTRY and sum of PREDICT;
** but the sum of PREDICT will be named ORDVAR;
proc sql;
create table newps as
select country, sum(predict) as ordvar
from sashelp.prdsale
group by country
order by ordvar descending;
quit;

** Review the desired order;
** probably could combine these 2 queries into 1 query, but I wanted to show;
** the data file from the first query before doing the join.;
ods listing;
proc print data=work.newps;
title 'Desired Order based on Predicted Sum';
run;

** Now make a table for PROC REPORT which will put ORDVAR on each;
** row in SASHELP.PRDSALE;
proc sql;
create table final as
select ordvar, b.country, region, predict, actual
from work.newps as a, sashelp.prdsale as b
where a.country = b.country;
quit;

** Review the data set that will be sent to PROC REPORT;
** Note the value of ORDVAR on every ROW;
proc print data=final(obs=50);
title 'Final data set for PROC REPORT';
title2 'get rid of this proc print step in production';
run;

** Final PROC REPORT;
** Because ORDVAR is on every ROW, I can use it as a NOPRINT variable to control;
** the overall order -- so USA comes first -- based on PREDICT;
proc report data=final nowd;
title 'Using ORDVAR to Determine Order for PROC REPORT';
title2 'Note that ORDVAR is based on sum of PREDICT var';
title3 'You would have to change the logic if you wanted this based on ACTUAL';
column ordvar country region predict actual;
define ordvar / group descending noprint;
define country /group ;
define region / group ;
define predict / sum;
define actual / sum;
break after country /summarize skip;
run;

[/pre]