SORT input file based on latest record

maxsubrat · Active User Joined: 27 Feb 2008 Posts: 110 Location: india

I have a input file which need to be sorted.
suppose I have data in input file in below format:
PO-NBR PO-TIMESTAMP SEQ-ID
099955 2015-09-16-00-00-00-000000 21
099955 2015-09-15-00-00-00-000000 21
099955 2015-09-11-00-00-00-000000 21
077777 2015-09-15-10-00-00-000000 25
077777 2015-09-19-05-00-00-000000 25
077777 2015-09-10-12-00-00-000000 25
077777 2015-09-10-15-00-00-000000 25

so my sorted output file should be like that:
PO-NBR PO-TIMESTAMP SEQ-ID
099955 2015-09-16-00-00-00-000000 21
077777 2015-09-19-05-00-00-000000 25

that means I want the latest record for the PO-NBR & SEQ-ID based on PO-TIMESTAMP.

Thanks
Maxsubrat

RahulG31 · Active User Joined: 20 Dec 2014 Posts: 446 Location: USA

It's funny but the post doesn't have any question to be answered ;)

Rohit Umarjikar · Posted: Wed Dec 02, 2015 9:16 pm

Someone should give him a WARNING.
This has been his 3rd post on the same question since yesterday and the height is inspite of having it answered he is still posting the same question in wrong section( I guess or he changed his mind) and he is still denied to use a code tag or he doesn't know even after 7 years on this forum.
ibmmainframes.com/viewtopic.php?t=64603&highlight=
ibmmainframes.com/viewtopic.php?t=64602

Rohit Umarjikar · Posted: Wed Dec 02, 2015 9:35 pm

Anyways, This is should give you what you want. Now you have a DB2 and SORT both the solution, no more post on the same topic please.

Rohit Umarjikar · Posted: Wed Dec 02, 2015 10:51 pm

Use this one with some more samples and added seq-id too which I did not add in my earlier post.

boyti ko · New User Joined: 03 Nov 2014 Posts: 78 Location: Malaysia

Hi Rohit,

It's OPTION EQUALS that preserve arrangement, not the OPTION COPY.

Bill Woodger · Posted: Thu Dec 03, 2015 12:44 pm

Despite the topic title, a SORT is not wanted. Rohit has "turned off" the automatic sequencing which SELECT would do otherwise by using OPTION COPY. So the file is in the same order as the input, just less of it.

OPTION EQUALS preserves the order to the input sequence in the case of duplicate keys in a SORT or MERGE.

maxsubrat · Active User Joined: 27 Feb 2008 Posts: 110 Location: india

I thought of using it thru DB2, but some other conflict issues with some data.
so after unloading data from DB2 table, then i can take the latest(max) record for each PO will be better.
But using the above SORT I am getting some error..
Here is the SORT:
================
//STEP03 EXEC PGM=SORT
//IN DD DSN=NA.ACS.TEST.INPUT1,
// DISP=SHR
//*
//OUT DD DSN=NA.ACS.TEST.INPUT1,
// DISP=OLD
//*
//TOOLIN DD *
SELECT FROM(IN) TO(OUT) ON(1,6,CH) FIRST USING(CTL1)
//CTL1CNTL DD *
OPTION EQUALS
//*
//SYSOUT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSABEND DD SYSOUT=*

GETTING THE BELOW ERROR
================
ICE802I 0 BLOCKSET TECHNIQUE IN CONTROL

ICE063A 1 OPEN ERROR SYSIN

ICE751I 0 C5-I21470 E7-I22412

ICE052I 3 END OF DFSORT

Nic Clouston · Posted: Thu Dec 03, 2015 5:29 pm

Code Tags

maxsubrat · Active User Joined: 27 Feb 2008 Posts: 110 Location: india

I am using VDI to access mainframe, so difficult to copy paste the code tags...

Garry Carroll · Posted: Thu Dec 03, 2015 8:36 pm

Rohit Umarjikar · Posted: Thu Dec 03, 2015 9:29 pm

Bill , Thanks and boyti,I knew OPTION EQUALS preserves the order but wanted to check the if copy too does the same. Hence I choose to go with OPTION COPY

With the above input samples I get following output, So I question does EQUAL really preserved the order?

RahulG31 · Active User Joined: 20 Dec 2014 Posts: 446 Location: USA

Rohit Umarjikar · Posted: Fri Dec 04, 2015 3:18 am

All Right, Thanks for the clarity.
EQUALS isn't useful here.

maxsubrat · Active User Joined: 27 Feb 2008 Posts: 110 Location: india

Thanks all for the inputs.

Here one point to mention:

For the PO: 077777, it's not picking the latest(max) TS record.
it's picking the 1st record for the PO: 077777 2015-09-15-10-00-00-000000 25

but the latest(max) record for 077777 is:
077777 2015-09-19-05-00-00-000000 25

If the above SORT is based on the order(picking the 1st record), then I need to put the records in an DESC order before using the SORT..
Please correct me if I am wrong..

Thanks
Subrat

Rohit Umarjikar · Posted: Fri Dec 04, 2015 10:02 pm

Use this please,

Rohit Umarjikar · Posted: Fri Dec 04, 2015 11:51 pm

Also, you can use this too.