I thought this would be simple, but I can't seem to make something work except to incur overhead with a double pass and a join.

Starting with this code:

TABLE FILE CAR
  SUM 
    DEALER_COST 
  BY CAR
  ACROSS COUNTRY
  ON TABLE COLUMN-TOTAL
END

I would like to sort the Across Values by highest column-total.

The only way I found to do this is to make 2 passes like this:

-DEFAULT &ECHO=ALL
APP HOLD IBISAMP
SET ASNAMES=ON
TABLE FILE CAR
SUM DEALER_COST AS TTL
BY COUNTRY
ON TABLE HOLD AS TEMP FORMAT FOCUS INDEX COUNTRY
END
-RUN
JOIN COUNTRY IN CAR TO COUNTRY IN TEMP AS J0
END
TABLE FILE CAR
SUM DEALER_COST
BY CAR
ACROSS HIGHEST TTL NOPRINT
ACROSS COUNTRY
ON TABLE COLUMN-TOTAL
END

There's got to be a simpler way, yes?

This message has been edited. Last edited by: Anatess,

Hi Anatess,

Have you seen this document? The bottom section discusses why two passes are necessary, but the example eliminates the JOIN.

How to change ACROSS order sequence

Regards,

Kathryn

Anatess,

You will have to generate the column-total before you issue the ACROSS. This means that a HOLD file is necessary.
Here is another solution (without the JOIN), using a multi-set request:

  
TABLE FILE CAR
SUM DEALER AS TDEALER
BY COUNTRY
SUM DEALER
BY COUNTRY
BY CAR
ON TABLE SET ASNAMES ON
ON TABLE HOLD
END
TABLE FILE HOLD
SUM DEALER
BY CAR
ACROSS HIGHEST TDEALER NOPRINT
ACROSS COUNTRY
ON TABLE COLUMN-TOTAL
END

You guys rock. I have modified the code without the JOIN. It didn't have observable differences in response time but I'm sure it is much better internally.

Thanks!