My requirement is to provide the top 3 Reason Codes (based on maximum-number of occurences) along with their count for each Category. The final output should look like:
Please note that where there are ties (where the number of occurrences are same for different Categories), the tied Reason Codes should be in alphabetical order. However, if that's too hard to be achieved, any order is fine (for the tied Reason Codes).
Joined: 07 Dec 2007 Posts: 2205 Location: San Jose
Souvik.Sinha,
1. What is the LRECL and RECFM of the Input file?
2. What is the Position and format of the Key ( 001,002,003...)
3. What is the Position and format of the reason code1?
4. What is the Position and format of the reason code2?
5. What is the Position and format of the reason code3?
6. Does your input have the header or you just showed it for our reference?
7. How many bytes do you need for the counter? 1 (max of 9 ) 2 (max of 99) 3 (max of 999)..?
Last but not least run the following JCL and show us the complete sysout which will help us determine the level of DFSORT you have.
Joined: 07 Dec 2007 Posts: 2205 Location: San Jose
Souvik.Sinha,
You would need a minimum of 2 passes to get the desired results.
Pass1 : COPY the input file splitting each record into 3 records using OUTFIL"/" and records will look like this
i.e
Code:
Key Reason1 Reason2 Reason3
will be split into
Code:
Key Reason1
Key Reason2
Key Reason3
Pass2 : Now use JOINKEYS using the file created above for both JNF1 and JNF2. The key fields to match is 6 bytes from both files.
Use JNF1CNTL to remove dups using SUM FIELDS=NONE
Use JNF2CNTL to add a 9 byte constant value of 1 using INREC and SUM on that constant.
For the Maintask of joinkeys, the REFORMAT statement should have the 6 bytes from file1 and the summed counter value from file2.
Now SORT on the KEY(1,3) ascending and the COUNTER (7,9,ZD) descending.
Now you have the records in the order you want. You just need to build 1 single record for every key from this. And we do that using OUTREC.
Use OUTREC WHEN=GROUP to number the records within each key using SEQ.
Now validate the sequence number and if it is 1 PUSH the reason code and summed counter to the end of record (may be chose position 24) and push it only for RECORD=3
Repeat the above step now checking if the sequence is 2 and push the reason code and counter to end of the record (may be choose position 37) and push it only for RECORD=2
Now sequence 3 record has all the information you need. Using INCLUDE COND for sequence =3, you can build the final output like you desired.
This is pretty much the pseudo code and if you understand it correctly, you should be able to code the job in less than 30 minutes. Good Luck
Joined: 22 Apr 2005 Posts: 24 Location: New York, USA
Hi Kolusu,
Splitting the file into 1 entry for each Reason Code was the only solution that came to my mind and it will definitely work. I was wondering whether it can be achieved in one pass and posted my question here. Thank you very much for taking all the time and posting the detailed solution!
Joined: 07 Dec 2007 Posts: 2205 Location: San Jose
Souvik.Sinha wrote:
Hi Kolusu,
Splitting the file into 1 entry for each Reason Code was the only solution that came to my mind and it will definitely work. I was wondering whether it can be achieved in one pass and posted my question here. Thank you very much for taking all the time and posting the detailed solution!
Well you have 2 different layers of SORT. You need to COUNT reason codes and then Sort that counter descending. If you really wanted to DO in 1 pass then you can code an E15 Exit to sum the counter and E35 Exit to prepare the final output.
IMHO your focus need to be on coding an optimal solution rather than 1 Step Rule.
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
gylbharat,
If you look in the DFSORT manuals, they are very well described (along with other "exits") and the different languages that they may be written in are covered.
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
Souvik.Sinha,
If you could have got to the solution Kolusu suggested on your own, then you'd know enough not to ask the question about "one pass".
DFSORT itself can only create multiple output records from one input record by using OUTFIL. If data has to be sorted as well, perforce another step.
Since your data seems to be in order by category, you could do it in "one step" in A N Programming-Language which has "arrays". If your data is large (suggested by the desire for individual counts allowing for just shy of a billion) you really need to find the best way for the specifics of your requirement and your data.
Unless there is a fixed and not "unreasonable" number of Reasons.
.jpg)
