</>

TABLE FILE ...
SUM ...
BY ...
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET BYPANEL ON
END

</>

TABLE FILE CAR
SUM DEALER_COST/D8 AS 'MEAS'
BY COUNTRY
BY TOTAL COMPUTE DUMROW /I2   = 1;
BY TOTAL COMPUTE DUMTXT /A20V = 'Dealer Cost';
BY CAR
ON TABLE HOLD AS FILROW1
END
-RUN

TABLE FILE CAR
SUM RETAIL_COST/D8 AS 'MEAS'
BY COUNTRY
BY TOTAL COMPUTE DUMROW /I2   = 2;
BY TOTAL COMPUTE DUMTXT /A20V = 'Retail Cost';
BY CAR
ON TABLE HOLD AS FILROW2
END
-RUN

TABLE FILE CAR
SUM SEATS/D8 AS 'MEAS'
BY COUNTRY
BY TOTAL COMPUTE DUMROW /I2   = 3;
BY TOTAL COMPUTE DUMTXT /A20V = 'Seats';
BY CAR
ON TABLE HOLD AS FILROW3
END
-RUN

TABLE FILE FILROW1
SUM MEAS
BY COUNTRY
BY DUMROW
BY DUMTXT
BY TOTAL COMPUTE COLID /I2 = 1;
BY CAR
ON TABLE HOLD AS DETDATA
MORE
FILE FILROW2
MORE
FILE FILROW3
END
-RUN

TABLE FILE DETDATA
SUM MEAS
BY COUNTRY
BY DUMROW
BY DUMTXT
BY TOTAL COMPUTE COLID /I2  = 99;
BY TOTAL COMPUTE CAR   /A16 = 'Total';
ON TABLE HOLD AS TOTDATA
END
-RUN

TABLE FILE DETDATA
SUM MEAS
BY COUNTRY
BY DUMROW
BY DUMTXT
BY COLID
BY CAR
ON TABLE HOLD AS RPTDATA
MORE
FILE TOTDATA
END
-RUN

TABLE FILE RPTDATA
SUM MEAS     AS ''
BY COUNTRY
BY DUMROW    NOPRINT
BY DUMTXT    AS ''
ACROSS COLID NOPRINT
ACROSS CAR   AS ''
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET BYPANEL ON
END
-RUN

  
-SET &ECHO=ALL;
-* &N - Number of columns per bucket
-DEFAULT &N=7
-* Data
FILEDEF JARVES DISK JARVES.TXT
-RUN
-WRITE JARVES Row1     100  100  100  100  100
-WRITE JARVES Row2     200  200  200  200  200
-WRITE JARVES Row3     300  300  300  300  300
-WRITE JARVES Row4     400  400  400  400  400
-WRITE JARVES Row5     500  500  500  500  500
-WRITE JARVES Row6     600  600  600  600  600
-WRITE JARVES Row7     700  700  700  700  700
-WRITE JARVES Row8     800  800  800  800  800
-WRITE JARVES Row9     900  900  900  900  900
-WRITE JARVES Row10   1000 1000 1000 1000 1000
-RUN
-* Master file for the data
EX -LINES 10 EDAPUT MASTER,JARVES,C,MEM
FILENAME=JARVES, SUFFIX=FIX
 SEGNAME=JARVES, SEGTYPE=S0, $
  FIELDNAME=HEAD, USAGE=A7, ACTUAL=A7, $
  FIELDNAME=COL1, USAGE=I5, ACTUAL=A5, $
  FIELDNAME=COL2, USAGE=I5, ACTUAL=A5, $
  FIELDNAME=COL3, USAGE=I5, ACTUAL=A5, $
  FIELDNAME=COL4, USAGE=I5, ACTUAL=A5, $
  FIELDNAME=COL5, USAGE=I5, ACTUAL=A5, $
-RUN
-* Read the data. Change all formats to have the same length (here 7, because of the HEADING)
-* LL: Number each record and reset the numbering after &N records
-* PP: Number the buckets 
TABLE FILE JARVES
PRINT 
COMPUTE LL/I3=IF LAST LL EQ &N*10 THEN 10 ELSE LAST LL + 10;
COMPUTE PP/I3=IF LL EQ 10 THEN LAST PP + 1 ELSE LAST PP;
HEAD COL1/I7 COL2/I7 COL3/I7 COL4/I7 COL5/I7
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS J1 FORMAT ALPHA
END
-RUN
-* Now the tricky part: add a record containing the totals
-* It should be in the last bucket in the next column
-* Note: One should check if the number of rows is a multiple of 7. I didn't!
TABLE FILE J1
SUM MAX.PP LST.LL
ON TABLE HOLD AS PP
END
-RUN
-READFILE PP
-SET &LL=&LL+10;
-* Save the number of buckets
-SET &BUCKETS=&PP;
-RUN
-* Totals appended to the rows
FILEDEF J1 DISK J1.FTM (APPEND
TABLE FILE JARVES
SUM 
COMPUTE 
LL/I3=≪
PP/I3=&PP;
TOTS/A7='Totals';
COL1/I7 COL2/I7 COL3/I7 COL4/I7 COL5/I7
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE SAVE AS J1
END
-RUN
-* Even trickier: create column heading records, one per bucket
DEFINE FILE J1
XLL/I3 WITH LL=0;
XPP/I3=1 + LAST XPP;
XHEAD/A7='HEADING';
XCOL1/A7='COL1';
XCOL2/A7='COL2';
XCOL3/A7='COL3';
XCOL4/A7='COL4';
XCOL5/A7='COL5';
END
TABLE FILE J1
PRINT 
XLL
XPP
XHEAD
XCOL1
XCOL2
XCOL3
XCOL4
XCOL5
IF READLIMIT EQ &BUCKETS
ON TABLE HOLD AS J2 FORMAT ALPHA
END
-RUN
-* And append the rows
FILEDEF J2 DISK J2.FTM (APPEND
TABLEF FILE J1
PRINT *
ON TABLE SAVE AS J2
END
-RUN
-* Last trick: alternate master
-* this will allow to pivot columns to rows and create the buckets
EX -LINES 8 EDAPUT MASTER,JJ,C,MEM
 FILENAME=J2      , SUFFIX=FIX     
 SEGMENT=J2, SEGTYPE=S0, $
 FIELDNAME=XLL, ALIAS=E01, USAGE=I3, ACTUAL=A03, $
 FIELDNAME=XPP, ALIAS=E02, USAGE=I3, ACTUAL=A03, $
 SEGMENT=DATA, PARENT=J2, OCCURS=VARIABLE
 FIELDNAME=XVAL,  ALIAS=XVAL, USAGE=A7, ACTUAL=A07, $
 FIELDNAME=XNUM, ALIAS=ORDER, USAGE=I4, ACTUAL=I4, $
-RUN
-* OUTPUT!
FILEDEF JJ DISK J2.FTM
TABLE FILE JJ
SUM XVAL
BY XPP NOPRINT SUBFOOT
" "
BY XNUM NOPRINT
ACROSS XLL NOPRINT
END