I thought I saw a post relating to this but now I can't find it, so sorry for the repeat...

I'm trying to sort by a specific column in the across values. For example, I'm trying to sort the following by the SEDAN column:

TABLE FILE CAR
SUM CAR
BY COUNTRY AS ''
ACROSS BODYTYPE AS ''
ON TABLE NOTOTAL
END

I've tried using a DEFINE to sort by (eg, IF BODYTYPE EQ 'SEDAN' THEN ...) but the way I tried it, it inserted a few extra rows.

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

Not quite sure what you want. Did you mean the ACROSS fieldname COLUMNS value1 value2 ... valuen?

Nope. Run the code above and you'll see that the rows are sorted by COUNTRY. I want the exact same output but instead of sorted by COUNTRY it should be sorted by the values in the SEDAN "column" which is actually a crosstab value.

I'm not sure really what you want either... But, my best interpretation may be something like this produces:

TABLE FILE CAR
SUM DCOST
ACROSS CAR
WHERE BODYTYPE EQ 'SEDAN'
ON TABLE NOTOTAL
ON TABLE SET PAGE OFF
END

Which SUMs the DCOST ACROSS Car only for SEDANs.

Run the code in my first post...you'll see the rows are sorted by country. I'm trying to sort the rows by the values in the SEDAN column, intead of COUNTRY. So instead of:

           CONVERTIBLE  COUPE    HARDTOP  ROADSTER    SEDAN
ENGLAND    JAGUAR                TRIUMPH              JENSEN
FRANCE                                                PEUGEOT
ITALY                   MASERATI          ALFA ROMEO  ALFA ROMEO
JAPAN                                                 TOYOTA
W GERMANY                                             BMW

...I want this...

           CONVERTIBLE  COUPE    HARDTOP  ROADSTER    SEDAN
ITALY                   MASERATI          ALFA ROMEO  ALFA ROMEO
W GERMANY                                             BMW
ENGLAND    JAGUAR                TRIUMPH              JENSEN
FRANCE                                                PEUGEOT
JAPAN                                                 TOYOTA

Here is one approach. The first TABLE FILE writes out the column names (BODYTYPE) for use in the final output. These values are read into variables via a -REPEAT loop. The second TABLE FILE extracts the necessary data and places it in a HOLD file. The final TABLE FILE sorts the data extract as desired and generates the report using the original column names.

TABLE FILE CAR
 SUM CAR NOPRINT
 BY BODYTYPE
 ON TABLE SAVE AS 'NAMES'
END
-*
-RUN
-SET &NAMECNT = &LINES + 1 ;
-*
-REPEAT ENDREPEAT1 FOR &I FROM 2 TO &NAMECNT
-READ NAMES, &NAME.&I
-ENDREPEAT1
-*
TABLE FILE CAR
 SUM CAR
 BY COUNTRY
 ACROSS BODYTYPE
 ON TABLE HOLD
END
-*
TABLE FILE HOLD
 PRINT E01 AS ''
-REPEAT ENDREPEAT2 FOR &I FROM 2 TO &NAMECNT
 E0&I AS '&NAME.&I'
-ENDREPEAT2
-SET &BYI = &I - 1 ;
 BY E0&BYI NOPRINT
END

... or if you do not want to use DM to loop around then you could extract the order and then join back using your hold as the parent and driver table -

APP PREPENDPATH IBISAMP
TABLE FILE CAR
  SUM CAR      AS MYSORT
   BY COUNTRY
WHERE BODYTYPE EQ 'SEDAN'
ON TABLE HOLD AS MYHOLD
END
JOIN CLEAR *
JOIN COUNTRY IN MYHOLD TO COUNTRY IN CAR AS J1
TABLE FILE MYHOLD
  SUM CAR       AS ''
   BY MYSORT    NOPRINT
   BY COUNTRY   AS ''
ACROSS BODYTYPE AS ''
ON TABLE SET HTMLCSS ON
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
  GRID=OFF, SIZE=9, $
ENDSTYLE
END

T

Is it this what you want ?

It is probably not the simplest way but it seems to work.

I don't know whether the content of 'car' in my release (7.1.6) is the same as in yours.
I noticed by trying this that max. and lst. don't work together with within and alpha-fields. Is that a bug or a feature ?.
So I had to find a workaround like "last country"

DEFINE FILE CAR
SORTX/A16 = IF COUNTRY NE LAST COUNTRY THEN ' ' ELSE SORTX;
SORTX = IF BODYTYPE EQ 'SEDAN' THEN CAR ELSE SORTX;
END


TABLE FILE CAR
SUM CAR

BY COUNTRY
BY HIGHEST SORTX
BY BODYTYPE

ON TABLE HOLD
END

DEFINE FILE HOLD
SORTY/A16 = IF COUNTRY NE LAST COUNTRY THEN SORTX ELSE SORTY;
END

TABLE FILE HOLD
SUM FST.CAR

BY SORTY NOPRINT
BY COUNTRY

ACROSS BODYTYPE

END

Tony
Nice. Also, your way would allow you to parameterize the value to sort by.

The only problem is if you use a different BODYTYPE, say 'ROADSTER'. Your first hold gives you a single record (the Alpha Romeo from Italy) which restricts the final result set. So I switched the from/to tables in the join so I could do a LEFT_OUTER and ran into indexing errors.

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

Another alternative. Note that the AS option for CAR is a space (' ') and not a null ('').

DEFINE FILE CAR
 SORT_SEDAN/A16 = IF BODYTYPE EQ 'SEDAN' THEN CAR ELSE ' ';
END
-*
TABLE FILE CAR
 SUM CAR AS ' '
 BY TOTAL MAX.SORT_SEDAN NOPRINT
 BY COUNTRY AS ''
 ACROSS BODYTYPE AS ''
END

Dan:
Awesome. That's EXACTLY what I was looking for. I had DEFINE'd a sort val, as you have, but totally missed the BY TOTAL MAX...etc. Also, the spaces are genius. This is awesome. And, like Tony's, this would allow for a parameterized sort as well.

Don't know if it matters or not, but the table you're showing is incomplete because you have summed up alpha values. You're missing the W GERMANY AUDI, the ENGLAND JAGUAR SEDAN, the ITALY ALPHA ROMEO COUPE, and the JAPAN DATSUN SEDAN. I understand that this is probably just an example of what you're trying to do, and may work for your actual need, but it just caught my eye that the code that you were accepting as functional was missing data. Especially since you're wanting to sort on the values in SEDAN, I would think that you would actually want to see all of the valid values.

You can see this by using

SET NODATA=''
TABLE FILE CAR
SUM CAR
BY CAR NOPRINT
BY COUNTRY
ACROSS BODYTYPE
END

Darin
Yes, it was just an example, but that's good to know as well. I had noticed that I would lose records periodically, depending on the grouping, etc.