Page 1 of 1

Sort/Sum help

PostPosted: Thu Feb 18, 2010 2:56 am
by D9988
I'm having trouble with the sort/sum feature. I have an input dataset of transactions that is already sorted by account(1,5,PD,A), payment type(14,1,CH,A), and transaction date(6,8,CH,D). (The input file was sorted via SORT FIELDS=(1,5,PD,A,14,1,CH,A,6,8,CH,D)). All of these transactions are for the same account. There are 2 payments types (A and P), which are sorted by date descending.

----+----1----+----2----+----3
 -----------------------------
...ø.20060201APP .....<..... 
03170FFFFFFFFCDD400033400000 
9810F20060201177000008C0000F 
 -----------------------------
...ø.20060201APP ...ÅÁ...... 
03170FFFFFFFFCDD400066200000 
9810F20060201177000075C0000F 
 -----------------------------
 -----------------------------
...ø.20011101APP ....àð..... 
03170FFFFFFFFCDD400004800000 
9810F20011101177000014C0000F 
 -----------------------------
...ø.20010803APP ....øð..... 
03170FFFFFFFFCDD400017800000 
9810F20010803177000050C0000F 
 -----------------------------
...ø.20060301PU A.....ð..... 
03170FFFFFFFFDE4C00012800000 
9810F20060301740100013C0000F 
 -----------------------------
...ø.20060301PP A.....ý..... 
03170FFFFFFFFDD4C00012800000 
9810F20060301770100013D0000F 
 -----------------------------
...ø.20060201PP  .....<..... 
03170FFFFFFFFDD4400002400000 
9810F20060201770000197C0000F 
 -----------------------------
...ø.20011201PU S.....¸..... 
03170FFFFFFFFDE4E00000900000 
9810F20011201740200000D0000F 
 -----------------------------
...ø.20011102PU A.....æ..... 
03170FFFFFFFFDE4C00000900000 
9810F20011102740100000C0000F 
 -----------------------------



What I want to do is sum the "A" payment types and the "P" payment types, keeping the most recent transaction date for each payment type. I am able to succesfully sum the transaction amounts (18,6,PD) for each type, but sometimes the wrong date is written to the output file. In the above example, I expect it to keep the 20060201date for the A type, and 20060301 for the P type. I am using the following sort commands:

//SYSIN     DD *                   
  SORT FIELDS=(1,5,PD,A,14,1,CH,A),
  FILSZ=E5000000                   
  SUM FIELDS=(18,6,PD)


Here is my output dataset. It grabs the most recent date for the A type correctly, but for some reason it grabs an older date for the P type (20011201):

=COLS> ----+----1----+----2----+---
------------------------------------
574929  ø 20060201APP   &áæ 
       03170FFFFFFFFCDD400154900000
       9810F20060201177000205C0000F
------------------------------------
574930  ø 20011201PU S  /Ñ@ 
       03170FFFFFFFFDE4E00166700000
       9810F20011201740200219C0000F
------------------------------------


Any ideas?? Please let me know if you need any more info. Thank you.

Re: Sort/Sum help

PostPosted: Thu Feb 18, 2010 3:15 am
by Frank Yaeger
I'd guess that you have NOEQUALS in effect which means that the duplicate records can be output in any order.

Since your records are already sorted in the order you want, to ensure that you get the first record for each set of dups, you must have EQUALS in effect. Try adding this to SYSIN:

   OPTION EQUALS

Re: Sort/Sum help

PostPosted: Thu Feb 18, 2010 3:25 am
by D9988
I'll give this a try, thank you Frank :D

Re: Sort/Sum help

PostPosted: Fri Feb 19, 2010 2:43 am
by D9988
That took care of it, thank you!

Re: Sort/Sum help

PostPosted: Fri Feb 19, 2010 4:20 am
by Frank Yaeger
Glad I could help.