I am having difficulty envisioning how to accomplish this.


I can create a hold file with data like this:

GROUP LINENO IN OUT
GROUP1 01 100 50
GROUP1 02 200 100
GROUP1 03 300 150
GROUP2 01 100 50
GROUP2 02 200 100
GROUP2 03 300 150
GROUP3 01 100 50
GROUP3 02 200 100
GROUP3 03 300 150


I need to create an excel spreadsheet as shown below.
GROUP1 LINENO IN OUT
DATA 01 100 50
DATA 02 200 100
DATA 03 300 150
DATA TOTAL 600 300
GROUP2 LINENO IN OUT
DATA 01 100 50
DATA 02 200 100
DATA 03 300 150
DATA TOTAL 600 300
GROUP3 LINENO IN OUT
DATA 01 100 50
DATA 02 200 100
DATA 03 300 150
DATA TOTAL 600 300

I have 2 questions

How do I get the column headers to REPEAT as shown and with the GROUP Name change?

Another difficulty is the row with the TOTAL (Which is the Sub-Total of LINENO’S 01,02 & 03) – How can I get the word DATA in the first column and word TOTAL in the second column?

Any suggestions would be appreciated - Thanks

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

I am working on a solution for you. Give me a few more minutes.

Here is my solution. You might have to adjust the numbers of the phony sort groups and the trick is to make all of the formats the same. This technique is useful in all kinds of situations.

APP FI GRPMAS DISK grplines.mas
-RUN
-WRITE GRPMAS FILENAME=GRPLINES,SUFFIX=FIX
-WRITE GRPMAS SEGNAME=GRPLINES,SEGTYPE=S0
-WRITE GRPMAS FIELDNAME=GROUPNAME,ALIAS=GRPNM,FORMAT=A6,ACTUAL=A6,$
-WRITE GRPMAS FIELDNAME=,ALIAS=,FORMAT=A1,ACTUAL=A1,$
-WRITE GRPMAS FIELDNAME=LINENO,ALIAS=LNNO,FORMAT=A2,ACTUAL=A2,$
-WRITE GRPMAS FIELDNAME=INAMT,ALIAS=,FORMAT=I4,ACTUAL=A4,$
-WRITE GRPMAS FIELDNAME=OUTAMT,ALIAS=,FORMAT=I4,ACTUAL=A4,$
APP FI GRPLINES DISK grplines.ftm
-RUN
-WRITE GRPLINES GROUP1 01 100 50
-WRITE GRPLINES GROUP1 02 200 100
-WRITE GRPLINES GROUP1 03 300 150
-WRITE GRPLINES GROUP2 01 100 50
-WRITE GRPLINES GROUP2 02 200 100
-WRITE GRPLINES GROUP2 03 300 150
-WRITE GRPLINES GROUP3 01 100 50
-WRITE GRPLINES GROUP3 02 200 100
-WRITE GRPLINES GROUP3 03 300 150
DEFINE FILE GRPLINES
GRPCOL/A6=GROUPNAME;
LNNOCOL/A6='LINENO';
INCOL/A5='IN';
OUTCOL/A5='OUT';
SORT2/I1=1;
END
TABLE FILE GRPLINES
SUM GRPCOL LNNOCOL INCOL OUTCOL
COMPUTE SORT1/I1=IF GROUPNAME NE LAST GROUPNAME THEN SORT1+1 ELSE SORT1; NOPRINT
BY TOTAL SORT1
BY SORT2
BY GROUPNAME NOPRINT
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS COLHEAD FORMAT ALPHA
END
DEFINE FILE GRPLINES
GRPCOL/A6='DATA';
LNNOCOL/A6='TOTAL';
SORT2/I1=9;
END
TABLE FILE GRPLINES
SUM GRPCOL LNNOCOL 
INAMT NOPRINT OUTAMT NOPRINT
COMPUTE INCOL/A5=EDIT(INAMT);
        OUTCOL/A5=EDIT(OUTAMT);
COMPUTE SORT1/I1=IF GROUPNAME NE LAST GROUPNAME THEN SORT1+1 ELSE SORT1; NOPRINT
BY TOTAL SORT1
BY SORT2
BY GROUPNAME NOPRINT
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS COLFOOT FORMAT ALPHA
END
DEFINE FILE GRPLINES
GRPCOL/A6='DATA';
LNNOCOL/A6=LINENO;
SORT1/I1=IF GROUPNAME NE LAST GROUPNAME THEN SORT1+1 ELSE SORT1;
SORT2/I1=2;
END
TABLE FILE GRPLINES
PRINT 
INAMT NOPRINT OUTAMT NOPRINT
COMPUTE INCOL/A5=EDIT(INAMT);
        OUTCOL/A5=EDIT(OUTAMT);
BY SORT1
BY SORT2
BY GROUPNAME NOPRINT
BY GRPCOL
BY LNNOCOL
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS COLDTL FORMAT ALPHA
END
TABLE FILE COLHEAD
PRINT GRPCOL AS '' LNNOCOL AS '' INCOL AS '' OUTCOL AS ''
BY SORT1 NOPRINT
BY SORT2 NOPRINT
MORE
FILE COLDTL
MORE 
FILE COLFOOT
END

To produce the output in Excel, add this line just before the first MORE in the last TABLE request:

ON TABLE PCHOLD FORMAT EXL2K

Ginny,

Thank You - this is just what I needed.

I only have to change the amounts so they are numeric - which I can do by changing the FMT on INAMT/OUTAMT. If it is the column header then 'A20' else 'D20'.

Oops - I replaced my earlier code using a multi-verb request because it is not necessary...

Here is another approach, using SUBHEADs, SUBFOOTs, and STYLESHEET. I borrowed Ginny's code for creating the test data and master.

APP FI GRPMAS DISK grplines.mas
-RUN
-WRITE GRPMAS FILENAME=GRPLINES,SUFFIX=FIX
-WRITE GRPMAS SEGNAME=GRPLINES,SEGTYPE=S0
-WRITE GRPMAS FIELDNAME=GROUPNAME,ALIAS=GRPNM,FORMAT=A6,ACTUAL=A6,$
-WRITE GRPMAS FIELDNAME=LINENO,ALIAS=LNNO,FORMAT=A2,ACTUAL=A2,$
-WRITE GRPMAS FIELDNAME=INAMT,ALIAS=,FORMAT=I4,ACTUAL=A4,$
-WRITE GRPMAS FIELDNAME=OUTAMT,ALIAS=,FORMAT=I4,ACTUAL=A4,$
APP FI GRPLINES DISK grplines.ftm
-RUN
-WRITE GRPLINES GROUP101 100 50
-WRITE GRPLINES GROUP102 200 100
-WRITE GRPLINES GROUP103 300 150
-WRITE GRPLINES GROUP201 100 50
-WRITE GRPLINES GROUP202 200 100
-WRITE GRPLINES GROUP203 300 150
-WRITE GRPLINES GROUP301 100 50
-WRITE GRPLINES GROUP302 200 100
-WRITE GRPLINES GROUP303 300 150
-RUN
-*
DEFINE FILE GRPLINES
 GROUPHEAD/A4 = 'DATA';
END
-*
TABLE FILE GRPLINES
  SUM GROUPHEAD AS ''
      LINENO    AS ''
      INAMT     AS ''
      OUTAMT    AS ''
   BY GROUPNAME NOPRINT
   BY LINENO    NOPRINT
   ON GROUPNAME SUBHEAD
      "<GROUPNAME<+0>LINENO<+0>IN<+0>OUT"
   ON GROUPNAME SUBFOOT
      "DATA<+0>TOTAL<ST.INAMT<ST.OUTAMT"
   ON TABLE NOTOTAL
   ON TABLE SET HTMLCSS ON
   ON TABLE PCHOLD FORMAT EXL2K
   ON TABLE SET STYLESHEET *
      TYPE=SUBHEAD, HEADALIGN=BODY, $
      TYPE=SUBFOOT, HEADALIGN=BODY, $
      TYPE=SUBFOOT, OBJECT=FIELD, JUSTIFY=RIGHT, $
   ENDSTYLE
END

This message has been edited. Last edited by: Dan Satchell,

I believe this will work too - I will choose the winner!

Thanks