Page 1 of 1

DFSORT Sum and count

PostPosted: Thu Feb 27, 2014 8:41 pm
by aerosmith
Good Morning,

Im new to DFSORT and im trying to sort/count and sum if possible something similar to Sum and count something similar to



ID                 Reversal        Original   RCode     Ocode
20-009407            0.00           18.00       0420           
20-009407           18.00           0.00               0210
20-009407           18.00           0.00               0210





My desired outcome would be one line for unique ID, Sum the dollar amounts and Count the Codes
20-009407           36.00        18.00       1         2

Re: DFSORT Sum and count

PostPosted: Thu Feb 27, 2014 9:38 pm
by BillyBoyo
Have a look at the OUTFIL reporting functions. SECTIONS and TRAILER3, specifically, if you want intermediate totals, or TRAILER2 if you just want a final total. Assuming that you want the count of the existence of values for your RCode and Ocode (why the different format?) then you'd extend your data by two fields, which contain a value of "0" for a blank value and otherwise "1" and then use TOT for those.

Re: DFSORT Sum and count

PostPosted: Fri Feb 28, 2014 8:25 am
by aerosmith
Thanks, i took your advise and the way the input file was causing me to confuse myself, so i decided to seperate them in two different files.

Im getting an error now

            SORT FIELDS=(COPY)                                       
            OUTFIL FNAMES=OUTDD1,INCLUDE=((44,2,CH;EQ;C'0-');AND;     
                                           (143,4,CH;EQ;C'0420'))     
                   SECTIONS=(43,9                                     
                   $                                                 
ICE005A 0 BLANK NEEDED IN COLUMN 1 OR OPERATION NOT DEFINED CORRECTLY
                   TRAILER3=(43,9,X,COUNT=(M11,LENGTH=9))




sorry im new to this , and finding it very interesting :)


i added the trailer3 part and getting the the error before that the seperation ran ok.

 //TOOLIN   DD *                                         
   COPY FROM(INDD1) USING(CTL1)                         
 //CTL1CNTL DD *                                         
   SORT FIELDS=(COPY)                                   
   OUTFIL FNAMES=OUTDD1,INCLUDE=((44,2,CH;EQ;C'0-');AND;
                                  (143,4,CH;EQ;C'0420'))
          SECTIONS=(43,9                                 
          TRAILER3=(43,9,X,COUNT=(M11,LENGTH=9))         
   OUTFIL FNAMES=OUTDD1,INCLUDE=((44,2,CH;EQ;C'0-');AND;
                                  (153,4,CH;EQ;C'0321'))



Sample of input
20-016600  TS2001660001      001  882  016  999999  18.00           0.00     0420
20-018713  TS2001871301      001  364  063  999999  14.00           0.00     0420
20-018713  TS2001871301      001  364  063  999999  29.00           0.00     0420
Position 43                                                                                               Position 143




Ideal output
20-016600    1
20-018713    2


Code'd

Re: DFSORT Sum and count

PostPosted: Fri Feb 28, 2014 4:03 pm
by BillyBoyo
Your initial problems are missing commas. Change all your semi-colons to commas, and ensure that each line you want to continue ends with a comma before the first space, ie:

   OUTFIL FNAMES=OUTDD1,INCLUDE=((44,2,CH,EQ,C'0-'),AND, <- after than space, everything just comments


You can get it working a bit at a time and then put things together. It is a good way to understand what is going on.