Proc Report order by total of across variable

[bstarr]

Hello SAS experts,

I am trying to produce a report where I generate columns using an ACROSS variable with a calculated total. Ideally, each row of the report would then be sorted descending by the calculated total. I am having trouble getting this to work as expected, and am thinking it might not be possible. Is there a way to get PROC REPORT to sort by a calculated total variable by groups using ACROSS? When I add ORDER to the DEFINE TOT statement, I get a stairstepping report, which I do not want. 

The code below produces an example of the un-sorted report. The report I am looking to generate is exactly this, except I am trying to sort the "Total" column descending by Subsidiary. If this were a PROC SORT the equivalent sort I'd be looking for is:

BY Region descending tot;

proc report data=sashelp.shoes nowd spanrows;
    where Region in ('Africa' 'Asia');
    column ("Summary of Shoes" (Region Subsidiary) (Sales,Product Sales=tot));
        define Region / '' group noprint;
        define Subsidiary / 'Subsidiary' group;
        define Product / across;
        define Sales / analysis mean '';
        define tot / analysis sum 'Total';
    compute before Region / style=[just=l font_style=italic font_weight=bold background=Lightblue foreground=black];
        if Region="Africa" then do;
            text="Africa Region";
        end;
        if Region="Asia" then do;
            text="Asia Region";
        end;
        line text $50.;
    endcomp;
run;

Before I resort to pre-processing the data and then generating the report, I wanted to see if it was indeed not possible to sort in this manner.

Thanks in advance!

[Cynthia_sas · Accepted solution]

Hi:
You cannot "sort" on a variable that is used as an analysis variable. However, I had an example of this in my Creating Complex Reports paper, which you will find here. Look on page 10 at Complex example 3: http://www2.sas.com/proceedings/forum2008/173-2008.pdf

The report doesn't use ACROSS variables, but the concept is the same, you'll have to make 2 passes one way or another.

Programs for the 2008 paper are available on the support.sas.com web site, here: http://support.sas.com/rnd/papers/#SGF2008 and look in the Alpha list for 2008 for the paper title. There's a link to the zip file that has the code that produced the screen shots. You could use PROC MEANS or PROC REPORT or PROC SQL to calculate the totals you want before you run the second PROC REPORT step.
cynthia

[bstarr]

Got it, thank you Cynthia! I will give this a shot.