Page 1 of 1

Summarize field amounts by groups

PostPosted: Mon Sep 29, 2008 11:18 pm
by claywilly
Hello..
I have a flat file that contains an 11 byte voucher number field and 4 amount fields, each are ZD of 14.

I need to summarize or total up the 1st amount field if the voucher numbers are the same.

Sample records... The xxx's are the voucher number, the 999's is the field i want to sum up. Spaces are inserted for readability.

...............................xxxxxxxxxxx....99999999999999
PN N1000006689 N1000006689 003 XLR00004676 01 00000002550000 00000018989000 00000006545000
PN F2000002408 F2000002408 003 XJD00006212 09 00000000054018 00000011420000 00000000000000
PN F2000002408 F2000002408 003 XJD00006212 10 00000000015365 00000011420000 00000000000000
PN F2000002408 F2000002408 003 XJD00006212 08 00000000078448 00000011420000 00000000000000
PN F2000002408 F2000002408 003 XJD00006212 07 00000000041424 00000011420000 00000000000000
PN N1000005111 N1000005111 003 PJD00037451 01 00000000565704 00000007071301 00000007071300


Result..
...............................xxxxxxxxxxx....99999999999999
PN N1000006689 N1000006689 003 XLR00004676 01 00000002550000 00000018989000 00000006545000
PN F2000002408 F2000002408 003 XJD00006212 09 00000000189255 00000011420000 00000000000000
PN N1000005111 N1000005111 003 PJD00037451 01 00000000565704 00000007071301 00000007071300


Thanks,
clay

Re: Summarize field amounts by groups

PostPosted: Tue Sep 30, 2008 1:01 am
by Frank Yaeger
You can use a DFSORT job like this. I assumed that your output records could be sorted by the voucher number. If they really need to be sorted in their original order, let me know. I also assumed that the voucher number field starts position 32 and the amount field starts in positin 47.

//S1    EXEC  PGM=ICEMAN
//SYSOUT    DD  SYSOUT=*
//SORTIN DD DSN=...  input file (FB)
//SORTOUT DD DSN=...  output file (FB)
//SYSIN    DD    *
    SORT FIELDS=(32,11,CH,A)
    OPTION ZDPRINT
    SUM FIELDS=(47,14,ZD)
/*

Re: Summarize field amounts by groups

PostPosted: Tue Sep 30, 2008 1:51 am
by claywilly
Thanks Frank, That worked.

So how can I integrate your code with the rest of my sort? Should I use two steps, one with iceman, then the other with sort?

...
//S2 EXEC PGM=SORT,
// REGION=4096K
//*
//SORTIN DD DSN=...input
//*
//SYSOUT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SORTWK01 DD UNIT=SYSDA,...
//SORTWK02 DD UNIT=SYSDA,...
//*
//SORTOUT DD DSN=...output
//*
//SYSIN DD *
SORT FIELDS=COPY

OUTREC FIELDS=(1,02, . REF-TRANS-CODE(O)
3,11, . PA-NUMBER(P)
14,11, . REF-PO-NUMBER(O)
25,03, . VOUCHER-AGCY(O)
28,11, . VOUCHER-NUMBER(O)
39,02, . LINE-NO(O)
41,14, . VOUCHER-AMOUNT(O)
55,14, . AUTHORIZED-AMOUNT(P)
69,14, . ENCUMBERED-AMOUNT(P)
83,14, . EXPENDED-AMOUNT(P)
97,14, . CLOSED-AMOUNT(P)
111,14, . CLOSED-AMOUNT(O)
129,02,C'/', . START-DATE-MONTH(P)
131,02,C'/', . START-DATE-DAY(P)
125,04, . START-DATE-YEAR(P)
137,02,C'/', . END-DATE-MONTH(P)
139,02,C'/', . END-DATE-DAY(P)
133,04, . END-DATE-YEAR(P)
(55,14,ZD,SUB,97,14,ZD,SUB,111,14,ZD),
EDIT=(SIIIIIIIIIIIT.TT),
SIGNS=(+,-))
/*

*

Re: Summarize field amounts by groups

PostPosted: Tue Sep 30, 2008 2:33 am
by Frank Yaeger
So how can I integrate your code with the rest of my sort? Should I use two steps, one with iceman, then the other with sort?


That statement makes no sense. PGM=ICEMAN and PGM=SORT both call DFSORT.

It appears your job just copies the records and reformats them with an OUTREC statement. If you're ok with having the output in sorted order by the voucher number field, then you can just add your OUTREC statement to my job (OUTREC statement processing is performed after SORT and SUM processing). I've adjusted my positions per what you showed in your job.

//S1    EXEC  PGM=ICEMAN
//SYSOUT    DD  SYSOUT=*
//SORTIN DD DSN=...  input file (FB)
//SORTOUT DD DSN=...  output file (FB)
//SYSIN    DD    *
  SORT FIELDS=(28,11,CH,A)
  OPTION ZDPRINT
  SUM FIELDS=(41,14,ZD)
  OUTREC FIELDS=(1,02, . REF-TRANS-CODE(O)
     3,11, . PA-NUMBER(P)
    14,11, . REF-PO-NUMBER(O)
    25,03, . VOUCHER-AGCY(O)
    28,11, . VOUCHER-NUMBER(O)
    39,02, . LINE-NO(O)
    41,14, . VOUCHER-AMOUNT(O)
    55,14, . AUTHORIZED-AMOUNT(P)
    69,14, . ENCUMBERED-AMOUNT(P)
    83,14, . EXPENDED-AMOUNT(P)
    97,14, . CLOSED-AMOUNT(P)
   111,14, . CLOSED-AMOUNT(O)
   129,02,C'/', . START-DATE-MONTH(P)
   131,02,C'/', . START-DATE-DAY(P)
   125,04, . START-DATE-YEAR(P)
   137,02,C'/', . END-DATE-MONTH(P)
   139,02,C'/', . END-DATE-DAY(P)
   133,04, . END-DATE-YEAR(P)
   (55,14,ZD,SUB,97,14,ZD,SUB,111,14,ZD),
    EDIT=(SIIIIIIIIIIIT.TT),
    SIGNS=(+,-))
/*


If you want the output records in their original order (like a copy), let me know.