Page 1 of 1

Sort,Sum and overlay record

PostPosted: Mon Dec 28, 2009 10:04 pm
by Sub7
Input fixed length file:
Col1    Col2     Col3    Col4 Col5  Col6
========================================
1234|111111111|000000002|0002|VAC|BEFGHIJ|
2345|333333333|000000001|0002|BUR|DEFGHIJ|
1234|222222222|000000003|0003|BUR|CDFGHIJ|
1234|111111111|000000001|0002|BAC|DEFGHIJ|
1234|111111111|000000003|0002|SPC|DEFGHIJ|
1234|222222222|000000003|0003|MST|DEFGHIJ|
1234|222222222|000000003|0003|KEF|EFFGHIJ|
2345|333333333|000000001|0002|KEF|DEFGHIJ|


Output:
Col1  Col2      Col3     Col4 Col5  Col6
========================================
1234|111111111|000000015|0002|BAC|DEFGHIJ|
1234|111111111|000000015|0002|VAC|BEFGHIJ|
1234|111111111|000000015|0002|SPC|DEFGHIJ|
1234|222222222|000000015|0003|MST|DEFGHIJ|
1234|222222222|000000015|0003|BUR|CDFGHIJ|
1234|222222222|000000015|0003|KEF|EFFGHIJ|
2345|333333333|000000002|0002|BUR|DEFGHIJ|
2345|333333333|000000002|0002|KEF|DEFGHIJ|

OUTPUT condition:

A.Output need to be in sorted order on Col1,Col2
B.Col3 - Must be sum of amount in Col3 and to overlay;for matching Col1
C.Must have all records as input file as is.

-Neo

Re: Sort,Sum and overlay record

PostPosted: Tue Dec 29, 2009 11:30 pm
by Alissa Margulies
Assuming there is a space or field separator between each column, as you had displayed in your sample data, try this:
//STEP1 EXEC PGM=SORT
//SORTIN  DD DSN=ORIGINAL.INPUT,DISP=SHR
//SORTOUT DD DSN=&&TEMP,DISP=(NEW,PASS)
//SYSOUT  DD SYSOUT=*                                       
//SYSIN   DD *                                               
  INREC FIELDS=(1,4,16,9)                                 
  SORT FIELDS=(1,4,CH,A)                                       
  SUM FIELDS=(5,9,ZD)                                                       
/*                                                           
//STEP2  EXEC PGM=SORT
//SORTJNF1 DD DSN=ORIGINAL.INPUT,DISP=SHR
//SORTJNF2 DD DSN=&&TEMP,DISP=SHR
//SORTOUT  DD DSN=FINAL.OUTPUT,DISP=...             
//SYSOUT   DD SYSOUT=*                                       
//SYSIN    DD *                                             
  JOINKEYS FILE=F1,FIELDS=(1,4,A)                             
  JOINKEYS FILE=F2,FIELDS=(1,4,A),SORTED                     
  REFORMAT FIELDS=(F1:1,15,F2:5,9,F1:25,40)                   
  SORT FIELDS=(1,15,CH,A)                                                                       
/*   

Otherwise, you will need to modify the starting field positions to accomodate your actual data accordingly.

Re: Sort,Sum and overlay record

PostPosted: Thu Dec 31, 2009 2:20 pm
by Sub7
Thanks Alissa and appreciate .Saw this only after some time.

-Neo.