Joined: 10 Jan 2008 Posts: 29 Location: Chennai, India
Hi,
I have the following input file in which i need to select the max timestamp records based on the EMP# and TASK ID. We need to use SORT, SYNCSORT and not icetool. Please see the example below.
Input File
-----------
Code:
EMP# TASK ENTERED TASK ID TASK NAM MANAGER
22215687 2013-02-16-06.30.16.071719 0000001122 DESG CHG CAROL R
22215687 2013-02-16-06.30.16.631131 0000001122 DESG CHG CAROL R
22215687 2013-02-16-06.30.16.724981 0000001122 DESG CHG CAROL R
22215687 2013-02-14-23.32.52.919678 0000001122 DESG CHG CAROL R
22215687 2013-02-14-23.32.52.982218 0000001122 DESG CHG CAROL R
22215687 2013-02-14-23.32.52.982418 0000001122 DESG CHG CAROL R
22215687 2013-02-12-23.32.52.982418 0000001123 BONUS AD CAROL R
22215687 2013-02-12-23.32.52.982418 0000001123 BONUS AD CAROL R
22215687 2013-02-11-23.32.52.982418 0000001123 BONUS AD CAROL R
22215687 2013-02-11-23.32.52.982418 0000001123 BONUS AD CAROL R
Output File
------------
Code:
EMP# TASK ENTERED TASK ID TASK NAM MANAGER
22215687 2013-02-16-06.30.16.071719 0000001122 DESG CHG CAROL R
22215687 2013-02-16-06.30.16.631131 0000001122 DESG CHG CAROL R
22215687 2013-02-16-06.30.16.724981 0000001122 DESG CHG CAROL R
22215687 2013-02-12-23.32.52.982418 0000001123 BONUS AD CAROL R
22215687 2013-02-12-23.32.52.982418 0000001123 BONUS AD CAROL R
Joined: 28 Aug 2007 Posts: 1742 Location: Tirupur, India
Hello,
Quote:
select the max timestamp records based on the EMP# and TASK ID.
For the same EMP# and TASK ID there are multiple entries on the output, But you said you wanted only the maximum timestamp.
A little more explanation would certainly help.
Code:
22215687 2013-02-16-06.30.16.071719 0000001122 DESG CHG CAROL R
22215687 2013-02-16-06.30.16.631131 0000001122 DESG CHG CAROL R
22215687 2013-02-16-06.30.16.724981 0000001122 DESG CHG CAROL R
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
You might like to try this:
Code:
//STEP01 EXEC PGM=SORT
//SYSPRINT DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SORTOUT DD SYSOUT=*
//SYSIN DD *
OPTION COPY
* OPTION EQUALS
* SORT FIELDS=(1,8,CH,A,42,10,CH,A,14,10,CH,D)
OUTREC IFTHEN=(WHEN=INIT,
OVERLAY=(81:
001,008,
042,010,
SEQNUM,3,ZD,
RESTART=(81,18))),
IFTHEN=(WHEN=GROUP,BEGIN=(99,3,CH,EQ,C'001'),
PUSH=(103:14,10))
OUTFIL INCLUDE=(14,10,CH,EQ,103,10,CH),BUILD=(1,80)
//*
//SORTIN DD *
22215687 2013-02-16-06.30.16.071719 0000001122 DESG CHG CAROL R
22215687 2013-02-16-06.30.16.631131 0000001122 DESG CHG CAROL R
22215687 2013-02-16-06.30.16.724981 0000001122 DESG CHG CAROL R
22215687 2013-02-14-23.32.52.919678 0000001122 DESG CHG CAROL R
22215687 2013-02-14-23.32.52.982218 0000001122 DESG CHG CAROL R
22215687 2013-02-14-23.32.52.982418 0000001122 DESG CHG CAROL R
22215687 2013-02-12-23.32.52.982418 0000001123 BONUS AD CAROL R
22215687 2013-02-12-23.32.52.982418 0000001123 BONUS AD CAROL R
22215687 2013-02-11-23.32.52.982418 0000001123 BONUS AD CAROL R
22215687 2013-02-11-23.32.52.982418 0000001123 BONUS AD CAROL R
This gives:
Code:
22215687 2013-02-16-06.30.16.071719 0000001122 DESG CHG CAROL R
22215687 2013-02-16-06.30.16.631131 0000001122 DESG CHG CAROL R
22215687 2013-02-16-06.30.16.724981 0000001122 DESG CHG CAROL R
22215687 2013-02-12-23.32.52.982418 0000001123 BONUS AD CAROL R
22215687 2013-02-12-23.32.52.982418 0000001123 BONUS AD CAROL R
It uses RESTART (as SyncSort does not have KEYBEGIN) to identify the first record of a "group" (emp and task) then identifies the first record to form a GROUP, putting the date from the first record to all members of the GROUP.
Then OUTFIL selects the records whose date is the same as the date of the first record.
If your data is not already in sequence, uncomment the SORT statement and the OPTION EQUALS and delete the OPTION COPY. If it is in sequence, delete the commented SORT statement and OPTION EQUALS, and you can also change OUTREC to INREC, though not vital.