Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Extract records using sort based on the maximum occurence

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

New User


Joined: 15 Mar 2010
Posts: 9
Location: Chennai

PostPosted: Thu May 06, 2010 11:01 am    Post subject: Extract records using sort based on the maximum occurence
Reply with quote

I have a scenario wherein i have to extract records from a file based on the maximum occurence of particular field for a given combination of key.

Can this be achieved by using DFSORT? RECFM=FB,RECL=80

Example: Input:

1111111111111xxxx1 04/24/2010 Arun
1111111111111xxxx1 04/23/2010 Ragu
1111111111111xxxx1 04/24/2010 Arun
1111111111111xxxx1 04/24/2010 Arun
2222222222222xxxx2 04/01/2010 raju
2222222222222xxxx2 04/01/2010 raju
2222222222222xxxx2 04/05/2010 raju
2222222222222xxxx2 04/01/2010 Raju
2222222222222xxxx3 04/01/2010 prav

Output:

1111111111111xxxx1 04/24/2010
2222222222222xxxx2 04/01/2010
2222222222222xxxx3 04/01/2010

For the key '1111111111111xxxx1 ' , date '04/24/2010' has the maximum occurence. so the output should have the key and the maximum occuring date.There should be only one occurence for a particular key. Please advise.
Back to top
View user's profile Send private message

Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Thu May 06, 2010 9:23 pm    Post subject:
Reply with quote

Quote:

2222222222222xxxx2 04/01/2010


The maximum date here would be 04/05/2010. Given that you just made a mistake and that's what you really want, here's a DFSORT/ICETOOL job that will do what you asked for:

Code:

//S1    EXEC  PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG  DD SYSOUT=*
//IN DD DSN=...  input file (FB/80)
//OUT DD DSN=...  output file (FB/80)
//TOOLIN DD *
SELECT FROM(IN) TO(OUT) ON(1,18,CH) FIRST USING(CTL1)
/*
//CTL1CNTL DD *
  SORT FIELDS=(1,18,CH,A,27,4,CH,D,21,5,CH,D)
  OUTFIL FNAMES=OUT,BUILD=(1,30)
/*


OUT would have:

Code:

1111111111111xxxx1  04/24/2010   
2222222222222xxxx2  04/05/2010   
2222222222222xxxx3  04/01/2010   
Back to top
View user's profile Send private message
sguhan

New User


Joined: 15 Mar 2010
Posts: 9
Location: Chennai

PostPosted: Fri May 07, 2010 10:29 am    Post subject: Reply to: Extract records using sort based on the maximum oc
Reply with quote

Frank,

Thanks for your reply.

I have not made a mistake. I want the date field to be appear in the output, which has more number of occurrence for the given key, not the maximum value of date for a particular key.
Back to top
View user's profile Send private message
sguhan

New User


Joined: 15 Mar 2010
Posts: 9
Location: Chennai

PostPosted: Fri May 07, 2010 10:34 am    Post subject: Extract records using sort based on the maximum occurence
Reply with quote

Frank,

Date fields will also be in sorted order in the inout file...

Example:

1111111111111xxxx1 04/23/2010 Ragu
1111111111111xxxx1 04/24/2010 Arun
1111111111111xxxx1 04/24/2010 Arun
1111111111111xxxx1 04/24/2010 Arun
2222222222222xxxx2 04/01/2010 raju
2222222222222xxxx2 04/01/2010 raju
2222222222222xxxx2 04/01/2010 Raju
2222222222222xxxx2 04/05/2010 raju
2222222222222xxxx3 04/01/2010 prav
2222222222222xxxx3 04/01/2010 prav
Back to top
View user's profile Send private message
CICS Guy

Senior Member


Joined: 18 Jul 2007
Posts: 2150
Location: At my coffee table

PostPosted: Fri May 07, 2010 1:11 pm    Post subject:
Reply with quote

Quote:
Example:

1111111111111xxxx1 04/23/2010 Ragu
1111111111111xxxx1 04/24/2010 Arun
1111111111111xxxx1 04/24/2010 Arun
1111111111111xxxx1 04/24/2010 Arun
2222222222222xxxx2 04/01/2010 raju
2222222222222xxxx2 04/01/2010 raju
2222222222222xxxx2 04/01/2010 Raju
2222222222222xxxx2 04/05/2010 raju
2222222222222xxxx3 04/01/2010 prav
2222222222222xxxx3 04/01/2010 prav

Small questions please?

Your output would contain three records?

The first output:
1111111111111xxxx1 04/24/2010 Arun
This because there are more Aruns than Ragas?

But which one for the second:
2222222222222xxxx2 04/01/2010 raju
2222222222222xxxx2 04/01/2010 raju
2222222222222xxxx2 04/01/2010 Raju
2222222222222xxxx2 04/05/2010 raju
Is the capitalized Raju the same as the non-capitalized raju?
In either case, which date if there are different dates such as 04/05 and 04/01?
Or with multiple date (since they are already sorted), the first or last?

And the third:
2222222222222xxxx3 04/01/2010 prav
Back to top
View user's profile Send private message
Escapa

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA

PostPosted: Fri May 07, 2010 1:11 pm    Post subject:
Reply with quote

Check this and modify accordingly
http://www.ibmmainframes.com/viewtopic.php?p=234530#234530
Back to top
View user's profile Send private message
sguhan

New User


Joined: 15 Mar 2010
Posts: 9
Location: Chennai

PostPosted: Fri May 07, 2010 3:26 pm    Post subject:
Reply with quote

It is working.

Thanks for your help.
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 OC4 in SORT Mohan Kothakota DFSORT/ICETOOL 2 Tue Sep 12, 2017 6:30 pm
No new posts BUILD OUTFIL based on condition other... balaji81_k DFSORT/ICETOOL 13 Fri Sep 08, 2017 11:06 pm
This topic is locked: you cannot edit posts or make replies. Extract all "IF" Statements... Adarsh Damodaran CLIST & REXX 1 Wed Sep 06, 2017 9:28 am
No new posts SORT Trailer Count - LRECL Output co... amorante DFSORT/ICETOOL 5 Tue Aug 29, 2017 8:57 pm
No new posts Join records from 2 files with No Dup... Poha Eater DFSORT/ICETOOL 21 Sun Aug 27, 2017 10:35 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us