Is there a way to sort an across by sum value type ? Like a pivot table ?

SUM
USD
LCL
BY BUSINESS_NAME
ACROSS CALENDAR_YYYY


for example...

currently the data shows..
2007 2008
Company A USD LCL USD LCL
Company B USD LCL USD LCL

but i would like...
2007 2008
Company A USD USD LCL LCL
Company B USD USD LCL LCL

Stephen,
Here is a possibility (using the CAR file):

  
-* File Stephen.fex
TABLE FILE CAR
SUM RCOST DCOST
BY COUNTRY
BY SEATS
ON TABLE SAVE AS STEPHEN
END
DEFINE FILE STEPHEN
TYP/A5=IF NUM EQ 1 THEN 'RCOST' ELSE 'DCOST';
END
TABLE FILE STEPHEN
SUM COST
BY COUNTRY
ACROSS NUM NOPRINT
ACROSS SEATS
ACROSS TYP AS ''
END

And here is the stephen.mas master file:

  
 FILENAME=STEPHEN    , SUFFIX=FIX     , $
 SEGMENT=STEPHEN
 FIELDNAME=COUNTRY, ALIAS=E01, USAGE=A10, ACTUAL=A10, $
 FIELDNAME=SEATS, ALIAS=E02, USAGE=I3, ACTUAL=A03, $
 SEGMENT=COST, PARENT=STEPHEN, OCCURS=VARIABLE
 FIELDNAME=COST, ALIAS=E03, USAGE=D7, ACTUAL=A07, $
 FIELDNAME=NUM, ALIAS=ORDER, USAGE=I2, ACTUAL=I4, $

There are several ways to do this.

SET ASNAMES=ON
DEFINE FILE XXX
AMCODE/A3='USD';
END
TABLE FILE XXX
SUM USD AS 'AMOUNTX'
BY CALENDAR_YYYY
BY BUSINESS_NAME
BY AMCODE
ON TABLE HOLD AS HOLDUSD
END
DEFINE FILE XXX
AMCODE/A3='LCL';
END
TABLE FILE XXX
SUM LCL AS 'AMOUNTX'
BY CALENDAR_YYYY
BY BUSINESS_NAME
BY AMCODE
ON TABLE HOLD AS HOLDLCL
END
-RUN
TABLE FILE HOLDUSD
PRINT *
ON TABLE HOLD AS HOLDTOTAL
MORE
FILE HOLDLCL
END
-RUN
TABLE FILE HOLDTOTAL
SUM AMOUNTX AS ''
BY BUSINESS_NAME
ACROSS CALENDAR_YYYY
ACROSS HIGHEST AMCODE NOPRINT
END

I supposed the USD and LCL have the same size.

Thanks fellas -

Daniel, ill give it a try.

Frank - the one drawback to this solution that i couldnt get around is that the user wants the values formatted differently between the two columns (ie LCL with 2 decimals, USD with 0). When i put them both into the same field, i couldnt split the formatting.

Well Stephen, that different format is also in Daniel's solution a problem.

But you can solve that too.

In your define you create a field like

FMAT/A8=IF AMCODE EQ 'USD' THEN 'D12.2S' ELSE 'D7.1%';

now change the the line
"SUM AMOUNTX AS ''" to
SUM AMOUNTX/FMAT AS ''