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
 

 

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 SORT to unpack a feild shr_amar DFSORT/ICETOOL 7 Fri Jul 07, 2017 4:32 am
No new posts How to write Specific Fields from Mul... Padhu SYNCSORT 6 Thu Jul 06, 2017 10:26 am
No new posts Optimize sort inrec parse vishwakotin DFSORT/ICETOOL 6 Mon Jun 26, 2017 11:15 pm
No new posts What is the Extract Function in JCL dharmaraok JCL & VSAM 3 Wed Jun 14, 2017 9:12 am
No new posts Reduce CPU Times for Join Sort santoshn SYNCSORT 12 Sat Jun 10, 2017 1:40 pm


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