The SAS Output Delivery System and reporting techniques

Proc Report order by total of across variable

Accepted Solution Solved
Reply
Contributor
Posts: 30
Accepted Solution

Proc Report order by total of across variable

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!


Accepted Solutions
Solution
‎03-16-2018 02:33 PM
SAS Super FREQ
Posts: 9,431

Re: Proc Report order by total of across variable

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

View solution in original post


All Replies
Solution
‎03-16-2018 02:33 PM
SAS Super FREQ
Posts: 9,431

Re: Proc Report order by total of across variable

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
Contributor
Posts: 30

Re: Proc Report order by total of across variable

Posted in reply to Cynthia_sas

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 185 views
  • 0 likes
  • 2 in conversation