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
 

 

retreiving max count records using DFSORT

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

New User


Joined: 06 Aug 2008
Posts: 22
Location: chennai

PostPosted: Fri May 07, 2010 12:16 pm    Post subject: retreiving max count records using DFSORT
Reply with quote

Hi,

I've a file with the records like below.

111 xx ....
111 xx ...
111 xx ...
111 yy ....
111 yy ...
222 yy ..
222 yy ...
222 zz ..
333 xx ...

And want the output like below.

111 xx....( as 111 is having max times xx than others)
222 yy .. ( as 222 is having max times yy thn others)
333 xx ...

We want to accomplish this thru DFSORT.

Can anyone suggest how to do this ..

Thanks in Advance,
hari
Back to top
View user's profile Send private message

enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10203
Location: italy

PostPosted: Fri May 07, 2010 12:24 pm    Post subject: Reply to: retreiving max count records using DFSORT
Reply with quote

Your question is the same as this one
http://ibmmainframes.com/viewtopic.php?t=48563&highlight=

are You in the same class, or working together on the same problem ???
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:06 pm    Post subject:
Reply with quote

You can use below sortstep....
Code:

//S1    EXEC  PGM=SORT                                           
//SYSOUT    DD  SYSOUT=*                                         
//SORTIN DD *                                                     
111 XX ...                                                       
111 XX ...                                                       
111 XX ...                                                       
111 YY ...                                                       
111 YY ...                                                       
222 YY ...                                                       
222 YY ...                                                       
222 ZZ ...                                                       
333 XX ...                                                       
/*                                                               
//SORTOUT DD SYSOUT=*                                             
//SYSIN    DD    *                                               
  SORT FIELDS=(1,3,CH,A,81,8,ZD,D)                               
  INREC OVERLAY=(81:SEQNUM,8,ZD,RESTART=(1,5))                   
  OUTREC OVERLAY=(81:SEQNUM,8,ZD,RESTART=(1,3))
  OUTFIL BUILD=(1,80),INCLUDE=(81,8,ZD,EQ,1)           
/*                                                     
Back to top
View user's profile Send private message
hariharan4

New User


Joined: 06 Aug 2008
Posts: 22
Location: chennai

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

Hi,

It is working fine.
Can you please explain the RESTART command used in the above code.

Thanks in Advance,
Hari
Back to top
View user's profile Send private message
guptae

Moderator


Joined: 14 Oct 2005
Posts: 1187
Location: Bangalore,India

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

Hello Hari,

RESTART=(1,5)
It will restart the seq number form 1 depending upon first 5 bytes
means if ur i/p is

11111
11111
22222
22222
33333

so o/p will be

11111 1
11111 2
22222 1
22222 2
33333 1

Hope its clear now
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 3:37 pm    Post subject:
Reply with quote

hariharan4 wrote:

Can you please explain the RESTART command used in the above code.

RTFM
Back to top
View user's profile Send private message
hariharan4

New User


Joined: 06 Aug 2008
Posts: 22
Location: chennai

PostPosted: Fri May 07, 2010 4:17 pm    Post subject:
Reply with quote

//S1 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD *
111 11 ...
111 11 ...
111 11 ...
111 12 ...
111 12 ...
222 11 ...
222 11 ...
222 12 ...
333 11 ...
/*
//SORTOUT DD SYSOUT=*
//SYSIN DD *
SORT FIELDS=(1,3,CH,A,81,8,ZD,D)
INREC OVERLAY=(81:SEQNUM,8,ZD,RESTART=(1,6))
OUTREC OVERLAY=(81:SEQNUM,8,ZD,RESTART=(1,3))
OUTFIL BUILD=(1,80),INCLUDE=(81,8,ZD,EQ,1)
/*

The output for the above code is

111 12 ... (wrong)
222 12 ...
333 11 ...

The expected Output should be as below

111 11 ... (correct)
222 12 ...
333 11 ...

Thanks in advance
Hari
Back to top
View user's profile Send private message
guptae

Moderator


Joined: 14 Oct 2005
Posts: 1187
Location: Bangalore,India

PostPosted: Fri May 07, 2010 4:44 pm    Post subject:
Reply with quote

Hello Hari,

Above sort card providing correct output

Code:
111 11
222 11
333 11


Are you sure that 222 12 instead of 222 11 ... ?
Back to top
View user's profile Send private message
hariharan4

New User


Joined: 06 Aug 2008
Posts: 22
Location: chennai

PostPosted: Fri May 07, 2010 9:24 pm    Post subject:
Reply with quote

Hi,

very sorry to bother you again and again!!!
we can understand the RESTART command given in INREC.
But what is the purpose of RESTART = (1,3) in the OUTREC? what it exactly does in the above code? It is only bit confusing for us.

Yes ur correct. The expected output should be:

111 11 ...
222 11 ...
333 11 ...

Thanks In Advance,
Hari
Back to top
View user's profile Send private message
Skolusu

Senior Member


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

PostPosted: Fri May 07, 2010 9:30 pm    Post subject: Reply to: retreiving max count records using DFSORT
Reply with quote

Hari,

Unless I have misunderstood the requirement isn't it just getting the FIRST record based on the first 3 bytes as key?

IF so SELECT operator with FIRST parm should give the desired results

Code:

//STEP0100 EXEC PGM=ICETOOL                   
//TOOLMSG  DD SYSOUT=*                       
//DFSMSG   DD SYSOUT=*                       
//IN       DD *                               
111 XX ...                                   
111 XX ...                                   
111 XX ...                                   
111 YY ...                                   
111 YY ...                                   
222 YY ...                                   
222 YY ...                                   
222 ZZ ...                                   
333 XX ...                                   
//OUT      DD SYSOUT=*                       
//TOOLIN   DD *                               
  SELECT FROM(IN) TO(OUT) ON(1,3,CH) FIRST   
//*
Back to top
View user's profile Send private message
hariharan4

New User


Joined: 06 Aug 2008
Posts: 22
Location: chennai

PostPosted: Fri May 07, 2010 9:39 pm    Post subject:
Reply with quote

Hi Kolusu,

I think you mistook the requirement. It is not just getting the FIRST record based on the first 3 bytes as key.
Actually we are almost done with the output but we couldn't understand the part of the code
OUTREC OVERLAY=(81:SEQNUM,8,ZD,RESTART=(1,3))
That too especially RESTART command in the above code.


Thanks in Advance,
Hari
Back to top
View user's profile Send private message
Skolusu

Senior Member


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

PostPosted: Fri May 07, 2010 9:53 pm    Post subject:
Reply with quote

hariharan4,

Hmm so you want to get the max repeated key but you just need 1 key in the output?

If that is the case then you can use the following JCL

Code:

//STEP0100 EXEC PGM=ICETOOL                               
//TOOLMSG  DD SYSOUT=*                                     
//DFSMSG   DD SYSOUT=*                                     
//IN       DD *                                           
111 XX ...                                                 
111 XX ...                                                 
111 XX ...                                                 
111 YY ...                                                 
111 YY ...                                                 
111 YY ...                                                 
111 YY ...                                                 
222 YY ...                                                 
222 YY ...                                                 
222 ZZ ...                                                 
333 XX ...                                                 
//OUT      DD SYSOUT=*                                     
//TOOLIN   DD *                                           
  SELECT FROM(IN) TO(OUT) ON(1,3,CH) FIRST USING(CTL1)     
//CTL1CNTL DD *                                           
  INREC OVERLAY=(81:SEQNUM,8,ZD,RESTART=(1,6))             
  SORT FIELDS=(1,3,CH,A,81,8,ZD,D)     
  OUTFIL FNAMES=OUT,BUILD=(1,80)                   
//*
Back to top
View user's profile Send private message
sguhan

New User


Joined: 15 Mar 2010
Posts: 9
Location: Chennai

PostPosted: Mon May 10, 2010 1:58 pm    Post subject: Reply to: retreiving max count records using DFSORT
Reply with quote

//S1 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD *
111 XX ...
111 XX ...
111 XX ...
111 YY ...
111 YY ...
222 YY ...
222 YY ...
222 ZZ ...
333 XX ...
/*
//SORTOUT DD SYSOUT=*
//SYSIN DD *
SORT FIELDS=(1,3,CH,A,81,8,ZD,D)
INREC OVERLAY=(81:SEQNUM,8,ZD,RESTART=(1,5))
OUTREC OVERLAY=(81:SEQNUM,8,ZD,RESTART=(1,3))
OUTFIL BUILD=(1,80),INCLUDE=(81,8,ZD,EQ,1)
/*


Could someone please explain how this code logically obtains the record which occurs maximum number of times for a particular key?
Back to top
View user's profile Send private message
Escapa

Senior Member


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

PostPosted: Mon May 10, 2010 2:06 pm    Post subject:
Reply with quote

Ekta has already explained part of this.
You can ask something specific, you did not get in it.
Back to top
View user's profile Send private message
hariharan4

New User


Joined: 06 Aug 2008
Posts: 22
Location: chennai

PostPosted: Fri May 14, 2010 10:10 am    Post subject:
Reply with quote

Hi,

The above ICETOOL and SORT code is not working fine for the below input:

//STEP0100 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD *
1111111111111XXXX1 04/24/2009 ARUN
1111111111111XXXX1 04/24/2009 ARUN
1111111111111XXXX1 04/24/2009 ARUN
1111111111111XXXX1 04/25/2009 ARUN
1111111111111XXXX1 04/25/2009 ARUN
1111111111111XXXX1 04/25/2009 ARUN
1111111111111XXXX1 04/24/2010 ARUN
1111111111111XXXX1 04/24/2010 ARUN
1111111111111XXXX1 04/24/2010 ARUN
1111111111111XXXX1 04/23/2010 RAGU
2222222222222XXXX2 04/01/2010 RAJU
2222222222222XXXX2 04/01/2010 RAJU
2222222222222XXXX2 04/05/2010 RAJU
2222222222222XXXX3 04/01/2010 PRAV
2222222222222XXXX2 04/01/2010 RAJU
2222222222222XXXX3 04/01/2010 PRAV
//SORTOUT DD SYSOUT=*
//SYSIN DD *
SORT FIELDS=(1,18,CH,A,81,8,ZD,D)
INREC OVERLAY=(81:SEQNUM,8,ZD,RESTART=(1,29))
OUTREC OVERLAY=(81:SEQNUM,8,ZD,RESTART=(1,18))
OUTFIL BUILD=(1,88),INCLUDE=(81,8,ZD,EQ,1)
/*

The output is :
1111111111111XXXX1 04/24/2009
2222222222222XXXX2 04/01/2010
2222222222222XXXX3 04/01/2010

The expected output should be as follows:
1111111111111XXXX1 04/24/2009
1111111111111XXXX1 04/25/2009(since this also occurs the maximum)
1111111111111XXXX1 04/24/2010(since this also occurs the maximum)
2222222222222XXXX2 04/01/2010
2222222222222XXXX3 04/01/2010

Thanks in Advance,
Hari
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 14, 2010 10:22 am    Post subject:
Reply with quote

Yes you are right, It will give first record in case of matching max count.
Do you want all of the records in that case?
Back to top
View user's profile Send private message
hariharan4

New User


Joined: 06 Aug 2008
Posts: 22
Location: chennai

PostPosted: Fri May 14, 2010 11:31 am    Post subject:
Reply with quote

Hi,

Yes we need all the matching maximum records for the given input. In the below input for the given key 1111111111111XXXX1 the maximum occurrance records are 04/24/2009 , 04/25/2009 and 04/24/2010. similarly for the other keys also. Hope your clear with the requirement now.

1111111111111XXXX1 04/24/2009 ARUN
1111111111111XXXX1 04/24/2009 ARUN
1111111111111XXXX1 04/24/2009 ARUN
1111111111111XXXX1 04/25/2009 ARUN
1111111111111XXXX1 04/25/2009 ARUN
1111111111111XXXX1 04/25/2009 ARUN
1111111111111XXXX1 04/24/2010 ARUN
1111111111111XXXX1 04/24/2010 ARUN
1111111111111XXXX1 04/24/2010 ARUN
1111111111111XXXX1 04/23/2010 RAGU
2222222222222XXXX2 04/01/2010 RAJU
2222222222222XXXX2 04/01/2010 RAJU
2222222222222XXXX2 04/05/2010 RAJU
2222222222222XXXX3 04/01/2010 PRAV
2222222222222XXXX2 04/01/2010 RAJU
2222222222222XXXX3 04/01/2010 PRAV

Thanks in Advance,
Hari
Back to top
View user's profile Send private message
Arun Raj

Moderator


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

PostPosted: Fri May 14, 2010 12:17 pm    Post subject:
Reply with quote

Is your input data already sorted on the first 2 fields?
Back to top
View user's profile Send private message
hariharan4

New User


Joined: 06 Aug 2008
Posts: 22
Location: chennai

PostPosted: Fri May 14, 2010 12:28 pm    Post subject:
Reply with quote

Yes
Back to top
View user's profile Send private message
Skolusu

Senior Member


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

PostPosted: Fri May 14, 2010 9:10 pm    Post subject:
Reply with quote

hariharan4,

Use the following DFSORT JCL which will give you the desired results
Code:

//STEP0100 EXEC PGM=SORT                                             
//SYSOUT   DD SYSOUT=*                                               
//SORTIN   DD *                                                     
1111111111111XXXX1 04/24/2009 ARUN                                   
1111111111111XXXX1 04/24/2009 ARUN                                   
1111111111111XXXX1 04/24/2009 ARUN                                   
1111111111111XXXX1 04/25/2009 ARUN                                   
1111111111111XXXX1 04/25/2009 ARUN                                   
1111111111111XXXX1 04/25/2009 ARUN                                   
1111111111111XXXX1 04/24/2010 ARUN                                   
1111111111111XXXX1 04/24/2010 ARUN                                   
1111111111111XXXX1 04/24/2010 ARUN                                   
1111111111111XXXX1 04/23/2010 RAGU                                   
2222222222222XXXX2 04/01/2010 RAJU                                   
2222222222222XXXX2 04/01/2010 RAJU                                   
2222222222222XXXX2 04/05/2010 RAJU                                   
2222222222222XXXX3 04/01/2010 PRAV                                   
2222222222222XXXX2 04/01/2010 RAJU                                   
2222222222222XXXX3 04/01/2010 PRAV                                   
//SORTOUT  DD SYSOUT=*                                               
//SYSIN    DD *                                                     
  SORT FIELDS=(1,18,CH,A,89,8,CH,A,81,8,CH,D),EQUALS                 
  INREC IFTHEN=(WHEN=INIT,OVERLAY=(81:SEQNUM,8,ZD,RESTART=(1,30))), 
  IFTHEN=(WHEN=GROUP,BEGIN=(81,8,ZD,EQ,1),PUSH=(89:ID=8))           
  OUTREC IFTHEN=(WHEN=INIT,                                         
  OVERLAY=(89:SEQNUM,8,ZD,RESTART=(1,18),                           
           97:SEQNUM,8,ZD,RESTART=(1,30))),                         
  IFTHEN=(WHEN=GROUP,BEGIN=(89,8,ZD,EQ,1),PUSH=(105:81,8),HIT=NEXT),
  IFTHEN=(WHEN=GROUP,BEGIN=(97,8,ZD,EQ,1),PUSH=(81:81,8))           
  OUTFIL INCLUDE=(105,8,CH,EQ,81,8,CH),BUILD=(1,80)                 
//*
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 Removing Duplicates based on certain ... chandracdac DFSORT/ICETOOL 8 Fri Dec 09, 2016 4:40 am
No new posts Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
No new posts How to get the remainder and quotient... vnktrrd DFSORT/ICETOOL 2 Mon Oct 31, 2016 10:59 am
No new posts How to split the records using the am... vnktrrd DFSORT/ICETOOL 24 Fri Oct 28, 2016 7:33 pm
No new posts Sort records based on numeric field. Alks SYNCSORT 2 Wed Oct 19, 2016 10:14 pm


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