Getting wrong Count of the Combinations Between Two Fields of a File thru OUTFIL

Question

i need to include this condition:

 1) Total no.of records per combination of field1 and field3 (INCLUDE=(1,2,8,3,CH,A)

INPUT FILE: FIELD1 AND FIELD3 have 5 combinations,if you see in example below

 field1 field2 field3 field4
 AA     00000  123    ABC
 AA     00000  123    ABC
 AA     00000  456    ABC
 BB     00000  123    ABC
 BB     00000  123    ABC
 BB     00000  789    ABC
 AA     00000  567    ABC

OUTPUT FILE: gets 5 rows, one for each combination, gives no.of occurrences for it

 FIELD1 FIELD3  COUNT-OF-COMBINATION
 AA     123     2
 AA     456     1
 AA     567     1
 BB     123     2
 AA     789     1

My method is:
 //SYSIN    DD  *                                                       
   SORT FIELDS=COPY                                                     
   OPTION COPY                                                          
   OUTFIL REMOVECC,NODETAIL,                                            
   TRAILER1=(1,2,'ON',8,3,'=',COUNT=(M11,LENGTH=10)))

/*

Answer i got is:

           AA ON 123 = 7

which is wrong: its should have been

  AA  ON   123   =  2
  AA  ON   456   =  1
  AA  ON   567   =  1
  BB  ON   123   =  2
  AA  ON   789   =  1

hey bill ... i was going to upvote someone and it said u need 15 repu to get someone upvoted? — Agent Mahone, Nov 08 '13 at 06:25
You have asked 13 questions. If you feel one or more answers to one or more of those questions have been what you are looking for, you can Accept the answer. You will get a bit of reputation from Accept, and you may get near to 15 again if you can find enough which have been what you want. You can then Upvote any that you have found useful (including any you found useful enough to Accept). — Bill Woodger, Nov 08 '13 at 07:42
i have now edited the question with the solution upto now i have collected... will test it and let you know the solution — Agent Mahone, Nov 08 '13 at 07:48
The output format is slightly different from what you wanted originally, otherwise it is a good solution. If you complete it, you can post it as an Answer and later Accept it. If you can't quite get it to what you want, we can finish it off, but you're nearly there. Good searching I'd guess, but no problem in that. Well done. — Bill Woodger, Nov 08 '13 at 11:55
the count i am getting is wrong, it only shows the first sorted record and put total count of all records to tht single record, please see the edited question in description — Agent Mahone, Nov 08 '13 at 14:18

score 2 · Accepted Answer · answered Nov 08 '13 at 15:15

You have:

 SORT FIELDS=COPY                                                     
 OPTION COPY                                                          
 OUTFIL REMOVECC,NODETAIL,                                            
 TRAILER1=(1,2,'ON',8,3,'=',COUNT=(M11,LENGTH=10)))

First problem is you have SORT FIELDS=COPY and OPTION COPY. These mean the same thing. Remove one or the other (I tend to use OPTION COPY).

Next, you have a spare right parenthesis.

Then you are using TRAILER1. There are three types of TRAILERn: 1 is "at the end of the report"; 2 is at the end of a page; 3 is at a control break.

You use TRAILER1, so at the end of your file you get one record, containing file totals.

After that, your positions for the TRAILER1 match the output, not the input file.

Which brings us to the fact that you are not running those Sort control cards with that data. The control cards have a syntax error which means they don't run. Correcting the cards and retaining the TRAILER1 gets AAON567=0000000007.

Which brings us to the control break, which is what you have missing.

You define a control break with SECTIONS. TRAILER3 is part of SECTIONS.

Fixing everything except your output format:

 OPTION COPY 
 OUTFIL REMOVECC,NODETAIL, 
        SECTIONS=(1,2,
                  15,3, 
                  TRAILER3=(1,2,
                            'ON',
                             15,3,
                             '=',
                             COUNT=(M11,
                                    LENGTH=10)))

Which gives you:

 AAON123=0000000002
 AAON456=0000000001
 BBON123=0000000002
 BBON789=0000000001
 AAON567=0000000001

If you want column headings, look at how to use HEADER3 (HEADER1 and HEADER2 would also work in this simple case). If you want "page totals" look at TRAILER2. If you want file totals, use TRAILER1.

thats really helpful Sir! i was using: SECTIONS=(1,12,TRAILER3=(1,3,' ON ',8,3,' = ',COUNT=(M10,LENGTH=3))) — Agent Mahone, Nov 08 '13 at 16:05
but why SECTIONS=(1,2, 15,3, <------ the count is for 1,2 & 8,3. Why are we using 15,3 ? — Agent Mahone, Nov 08 '13 at 16:10
You want to do the control on the third column, you said :-). 8,3 is the first three bytes of the second column *on the input* file. Always 000 in your example. — Bill Woodger, Nov 08 '13 at 16:12
Hi Bill, IS it possible to use Multiple filler statements in one sort step, Having 3 input files, 1 output file, 3 colums each having count of records of some column of one of the file — Agent Mahone, Nov 11 '13 at 08:17
You should ask this as a new question. You can include a link to previous questions that are related. If we continue with changed questions, it gets a bit confusing for future readers hoping to benefit from questions/answers here. — Bill Woodger, Nov 11 '13 at 12:50

Getting wrong Count of the Combinations Between Two Fields of a File thru OUTFIL

1 Answers1