BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
bstarr
Quartz | Level 8

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ
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

2 REPLIES 2
Cynthia_sas
SAS Super FREQ
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
Quartz | Level 8

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

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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