Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Ranking of Values Present in Different Positions

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
Souvik.Sinha

New User


Joined: 22 Apr 2005
Posts: 24
Location: New York, USA

PostPosted: Wed Jun 12, 2013 12:54 am    Post subject: Ranking of Values Present in Different Positions
Reply with quote

I have a file with the following structure:
Code:
Category   Reason1   Reason2   Reason3
--------   -------   -------   -------
001        AA1       DR1       BW9
001        CC3       AA1       EW9
001        BW7       DR1       AA1
002        AA1       CC3       BW9
003        EW9       TY1       CC3
003        BW9       DR7       FE8
777        AA1       CC3       BW9
777        AA1       CC3       DR1
777        AB1       EW9       BW9


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:
Code:
Category   Reason1   Reason2   Reason3
--------   -------   -------   -------
001        AA1(3)    DR1(2)    BW7(1)
002        AA1(1)    BW9(1)    CC3(1)
003        BW9(1)    CC3(1)    DR7(1)
777        AA1(2)    BW9(2)    CC3(2)

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).
Back to top
View user's profile Send private message

Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2158
Location: @my desk

PostPosted: Wed Jun 12, 2013 1:00 am    Post subject:
Reply with quote

Hello

Which DFSORT versiĆ³n you are at?
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Wed Jun 12, 2013 1:41 am    Post subject:
Reply with quote

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.
Code:

//S1 EXEC PGM=ICEMAN
//SYSOUT   DD SYSOUT=*
//SORTIN DD *
RECORD
/*
//SORTOUT DD DUMMY
//SYSIN   DD   *
  OPTION COPY
/*
Back to top
View user's profile Send private message
Souvik.Sinha

New User


Joined: 22 Apr 2005
Posts: 24
Location: New York, USA

PostPosted: Wed Jun 12, 2013 2:47 am    Post subject:
Reply with quote

Hi Kolusu,
The input file is 80 byte FB. I am giving you the file mentioning the positions below.
Code:
=COLS> ----+----1----+----2----+----3----+----4----+
****** ********************************* Top of Data
000001 001        AA1       DR1       BW9           
000002 001        CC3       AA1       EW9           
000003 001        BW7       DR1       AA1           
000004 002        AA1       CC3       BW9           
000005 003        EW9       TY1       CC3           
000006 003        BW9       DR7       FE8           
000007 777        AA1       CC3       BW9           
000008 777        AA1       CC3       DR1           
000009 777        AB1       EW9       BW9           

The input does not have headers - I just put them for your easy reference.

For counter, I need 9 bytes (max of 999,999,999).

Our shop uses DFSORT V1R12:
Code:
ICE805I 1 JOBNAME: USER111C , STEPNAME: STEP01                                                                     
ICE802I 0 BLOCKSET     TECHNIQUE IN CONTROL                                                                       
ICE201I H RECORD TYPE IS F - DATA STARTS IN POSITION 1                                                             
ICE751I 0 C5-K76982 C6-K90026 C7-K82419 C8-K67572 E9-K60824 C9-BASE   E5-K80744 E7-K79990                         
ICE143I 0 BLOCKSET     COPY  TECHNIQUE SELECTED                                                                   
ICE250I 0 VISIT http://www.ibm.com/storage/dfsort FOR DFSORT PAPERS, EXAMPLES AND MORE                             
ICE000I 1 - CONTROL STATEMENTS FOR 5694-A01, Z/OS DFSORT V1R12 - 22:01 ON TUE JUN 11, 2013 -                       
            OPTION COPY                                                                                           
ICE201I H RECORD TYPE IS F - DATA STARTS IN POSITION 1                                                             
ICE751I 0 C5-K76982 C6-K90026 C7-K82419 C8-K67572 E9-K60824 C9-BASE   E5-K80744 E7-K79990                         
ICE193I 0 ICEAM1 INVOCATION ENVIRONMENT IN EFFECT - ICEAM1 ENVIRONMENT SELECTED                                   
ICE088I 0 USER111C.STEP01  .        , INPUT LRECL = 80, BLKSIZE = 80, TYPE = FB                                   
ICE093I 0 MAIN STORAGE = (MAX,20971520,20958302)                                                                   
ICE156I 0 MAIN STORAGE ABOVE 16MB = (20880862,20880862)                                                           
ICE127I 0 OPTIONS: OVFLO=RC0 ,PAD=RC0 ,TRUNC=RC0 ,SPANINC=RC16,VLSCMP=N,SZERO=Y,RESET=Y,VSAMEMT=Y,DYNSPC=256       
ICE128I 0 OPTIONS: SIZE=20971520,MAXLIM=2097152,MINLIM=450560,EQUALS=N,LIST=Y,ERET=ABEND,MSGDDN=SYSOUT             
ICE129I 0 OPTIONS: VIO=N,RESDNT=ALL ,SMF=FULL ,WRKSEC=Y,OUTSEC=Y,VERIFY=N,CHALT=N,DYNALOC=N             ,ABCODE=MSG
ICE130I 0 OPTIONS: RESALL=4096,RESINV=0,SVC=109 ,CHECK=N,WRKREL=Y,OUTREL=Y,CKPT=N,COBEXIT=COB2                     
ICE131I 0 OPTIONS: TMAXLIM=20971520,ARESALL=0,ARESINV=0,OVERRGN=65536,CINV=Y,CFW=Y,DSA=0                           
ICE132I 0 OPTIONS: VLSHRT=N,ZDPRINT=N,IEXIT=N,TEXIT=N,LISTX=N,EFS=NONE    ,EXITCK=S,PARMDDN=DFSPARM ,FSZEST=N     
ICE133I 0 OPTIONS: HIPRMAX=512    ,DSPSIZE=512 ,ODMAXBF=0,SOLRF=Y,VLLONG=N,VSAMIO=N,MOSIZE=512                     
ICE235I 0 OPTIONS: NULLOUT=RC0                                                                                     
ICE236I 0 OPTIONS: DYNAPCT=10 ,MOWRK=Y                                                             
ICE084I 0 BSAM ACCESS METHOD USED FOR SORTOUT                                                     
ICE084I 0 BSAM ACCESS METHOD USED FOR SORTIN                                                       
ICE889I 0 CT=MAX     , SB=4, L=0, D=0000, CCW=1MAM                                                 
ICE902I 0 O AZ00  I AZ00                                                                           
ICE751I 1 EF-BASE   F0-K66717 E8-K79990                                                           
ICE900I 0 CON=1,MUV=0,VOL=1,ENU=0,SBK=0,SRC=0,VEM=0                                               
ICE090I 0 OUTPUT LRECL = 80, BLKSIZE = 80, TYPE = FB                                               
ICE055I 0 INSERT 0, DELETE 0                                                                       
ICE054I 0 RECORDS - IN: 1, OUT: 1                                                                 
ICE891I 1 20950128 WMAIN, 8168 CMAIN, MAX CALLOC, Y SCN, B BA, 0 AZ, 0 BZ, YY QC, 0 CZ, 0 DZ, 1 PLE
ICE892I 1 80 RIN  80 BLI  80 BLO  78 RUN  0 BUN  2817 CPU  00 CVC                                 
ICE893I 1 0 XIN 0 WIN 0 GIN NDEN PFP   B6D CM000 CIX UPTH LMD VS   RUX                             
ICE052I 0 END OF DFSORT
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Wed Jun 12, 2013 4:05 am    Post subject: Reply to: Ranking of Values Present in Different Positions
Reply with quote

Souvik.Sinha,

You need to answer ALL the questions I asked in my earlier post. I need the formats of key and reason codes.

so your desired output would be the following? ( Assuming the Reason Codes are sorted in the alphabetical order)

Code:

CATEGORY      REASON1        REASON2        REASON3     
--------   -------------  -------------  --------------
001        AA1(000000003) BW7(000000001) DR1(000000002)
002        AA1(000000001) BW9(000000001) CC3(000000001)
003        BW9(000000001) CC3(000000001) DR7(000000001)
777        AA1(000000002) BW9(000000002) CC3(000000002)
Back to top
View user's profile Send private message
Souvik.Sinha

New User


Joined: 22 Apr 2005
Posts: 24
Location: New York, USA

PostPosted: Wed Jun 12, 2013 7:07 pm    Post subject:
Reply with quote

Hi Kolusu,
I apologize for missing that information and appreciate your help. All the fields - Category, Reason1 thru Reason3 are of X(03).

And you are almost correct on the output - with just one correction for 001:
Code:
001        AA1(000000003) DR1(000000002) BW7(000000001)

Since DR1has higher occurrences (i.e. 2) than BW7 (i.e. 1), DR1 will come before BW7.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Wed Jun 12, 2013 7:50 pm    Post subject: Reply to: Ranking of Values Present in Different Positions
Reply with quote

So, order is Category, within category it is the first three Reason Codes by "popularity" then by alpha order (if possible).

Code:
=COLS> ----+----1----+----2----+----3----+----4----+
****** ********************************* Top of Data
000001 001        AA1       ZZ9       BW9           
000002 001        CC3       AA1       EW9           
000003 001        BW7       ZZ9       AA1           
000004 002        AA1       CC3       BW9           
000005 003        EW9       TY1       CC3           
000006 003        BW9       DR7       FE8           
000007 777        AA1       CC3       BW9           
000008 777        AA1       CC3       DR1           
000009 777        AB1       EW9       BW9       


Output would be

Code:
CATEGORY      REASON1        REASON2        REASON3     
--------   -------------  -------------  --------------
001        AA1(000000003) ZZ9(000000002) BW7(000000001)
002        AA1(000000001) BW9(000000001) CC3(000000001)
003        BW9(000000001) CC3(000000001) DR7(000000001)
777        AA1(000000002) BW9(000000002) CC3(000000002)


Where Category of 001 shows precedence of count over alpha-order for Reason, and the others (only) show alpha-order for those where count is equal.

And where this data:

Code:
004        ZZ8       ZZ9       BW7
004        ZZ8       BW7       ZZ9
004        AA1       AA2       ZZ8
005        AA1       BW9       CC3
005        AA1       XX9       MM3
006        AA1       BW9       CC3
006        BW9       CC3       AA2


Would give this output:

Code:
CATEGORY      REASON1        REASON2        REASON3     
--------   -------------  -------------  --------------
004        ZZ8(000000003) BW7(000000002) ZZ9(000000002)
005        AA1(000000002) BW9(000000001) CC3(000000001)
006        BW9(000000002) CC3(000000002) AA1(000000001)
Back to top
View user's profile Send private message
Souvik.Sinha

New User


Joined: 22 Apr 2005
Posts: 24
Location: New York, USA

PostPosted: Wed Jun 12, 2013 7:55 pm    Post subject:
Reply with quote

Hi Bill,
You are absolutely correct!
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Wed Jun 12, 2013 10:55 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
Souvik.Sinha

New User


Joined: 22 Apr 2005
Posts: 24
Location: New York, USA

PostPosted: Wed Jun 12, 2013 11:11 pm    Post subject:
Reply with quote

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!
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Wed Jun 12, 2013 11:30 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Wed Jun 12, 2013 11:51 pm    Post subject:
Reply with quote

Hi kolusu,

what is E15 and E35 Exit? Are these assembly language routines used internally by sort?
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Thu Jun 13, 2013 12:00 am    Post subject: Reply to: Ranking of Values Present in Different Positions
Reply with quote

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.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Thu Jun 13, 2013 12:31 am    Post subject: Reply to: Ranking of Values Present in Different Positions
Reply with quote

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.
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Thu Jun 13, 2013 1:32 am    Post subject:
Reply with quote

gylbharat wrote:
Hi kolusu,

what is E15 and E35 Exit? Are these assembly language routines used internally by sort?


As Bill pointed out, read chapter 5 in DFSORT Application Programming Guide

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/ICE1CA60/CCONTENTS
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Thu Jun 13, 2013 2:26 pm    Post subject:
Reply with quote

Thanks Kolusu..
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Overlaying one set of charater values... Kevin Lindsley SYNCSORT 7 Sat Nov 05, 2016 3:21 am
No new posts Low values Results from VARCHAR FORMAT balaji81_k DB2 10 Thu Oct 20, 2016 1:18 am
No new posts How can we create a flat file in JAVA... rakesh.v18 Java & MQSeries 7 Fri Sep 23, 2016 10:46 pm
No new posts Using 'parm' to vary SORTOUT record v... Sysaron DFSORT/ICETOOL 13 Wed Sep 07, 2016 9:24 pm
No new posts Unstring list of values into an array. Roshnii COBOL Programming 8 Mon Jun 27, 2016 6:25 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us