[How to ] Find Max of the given records through Sort

IBM's flagship sort product DFSORT for sorting, merging, copying, data manipulation and reporting. Includes ICETOOL and ICEGENER

[How to ] Find Max of the given records through Sort

Hi ,

I have a flat file which contains policyno(8) and due date(8) . It should return policy no with its max duedate .

Input :

00112345 20051201
00112345 20101205
00112345 20110120
00112345 20120112
00125692 20040801
00125692 20090912
00136759 20100509
00136759 20110406
00136759 20120101

O/p:

00112345 20120112
00125692 20090912
00136759 20120101

I know its possible through cobol .but is it possible through dfsort or icetool .Thanks in advance .
gauthamnagpur18

Posts: 93
Joined: Sat Oct 23, 2010 1:28 pm
Location: Chennai, India
Has thanked: 3 times
Been thanked: 2 times

Re: [How to ] Find Max of the given records through Sort

Hello gauthamnagpur18,

you can do this with two simple SORT-Steps.

First you sort your data ascending for policyno and descending for due date.
After that you delete entries with duplicate policyno.

//SORT1   EXEC PGM=SORT
//SYSOUT    DD SYSOUT=*
//SYSPRINT  DD SYSOUT=*
//SORTIN    DD *
00112345 20051201
00112345 20101205
00112345 20110120
00112345 20120112
00125692 20040801
00125692 20090912
00136759 20100509
00136759 20110406
00136759 20120101
/*
//SORTOUT   DD DSN=&&TEMP,DISP=(NEW,CATLG,DELETE)
//SYSIN     DD *
SORT FIELDS=(1,8,A,10,8,D),FORMAT=BI
/*
//SORT2   EXEC PGM=SORT
//SYSOUT    DD SYSOUT=*
//SYSPRINT  DD SYSOUT=*
//SORTIN    DD DSN=&&TEMP,DISP=SHR
//SORTOUT   DD SYSOUT=*
//SYSIN     DD *
SORT FIELDS=(1,8,A),FORMAT=BI
SUM FIELDS=NONE
/*

As an alternative you could use ICETOOL and do this in one step.

Regards,

Oliver
olivermf

Posts: 53
Joined: Tue Feb 08, 2011 4:28 pm
Has thanked: 3 times
Been thanked: 0 time

Re: [How to ] Find Max of the given records through Sort

Thanks Oliver ..Its working !!
gauthamnagpur18

Posts: 93
Joined: Sat Oct 23, 2010 1:28 pm
Location: Chennai, India
Has thanked: 3 times
Been thanked: 2 times

Re: [How to ] Find Max of the given records through Sort

You're welcome!
Always happy to help
olivermf

Posts: 53
Joined: Tue Feb 08, 2011 4:28 pm
Has thanked: 3 times
Been thanked: 0 time

Re: [How to ] Find Max of the given records through Sort

You should be able to do it in one pass of the data, if you look at using GROUP on OUTREC with OUTFIL INCLUDE/OMIT.
BillyBoyo
Global moderator

Posts: 3733
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 254 times

Re: [How to ] Find Max of the given records through Sort

gauthamnagpur18,

The easiest way to do this use the SELECT operator of ICETOOL and get the desired results. The following DFSORT/ICETOOL JCL will give you the desired results

//STEP0100 EXEC PGM=ICETOOL
//TOOLMSG  DD SYSOUT=*
//DFSMSG   DD SYSOUT=*
//IN       DD *
----+----1----+----2----+----3----+----4----+----5----+
00112345 20051201
00112345 20101205
00112345 20110120
00112345 20120112
00125692 20040801
00125692 20090912
00136759 20100509
00136759 20110406
00136759 20120101
//OUT      DD SYSOUT=*
//TOOLIN   DD *
SELECT FROM(IN) TO(OUT) ON(01,8,CH) FIRST USING(CTL1)
//CTL1CNTL DD *
SORT FIELDS=(1,8,CH,A,10,8,CH,D)
//*

The output from this job is
00112345 20120112
00125692 20090912
00136759 20120101
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
skolusu

Posts: 586
Joined: Wed Apr 02, 2008 10:38 pm
Has thanked: 0 time
Been thanked: 39 times