Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref

Author Message
siddheart22

New User

Joined: 09 Oct 2006
Posts: 19
Location: Toronto

Posted: Tue Dec 19, 2006 4:40 pm    Post subject: Select required records

Hi,

I want to achieve the following using SORT card, please suggest the optimal way/s of doing this.

My data looks as below:

I want the output such that, all duplicates in COL-D are removed and only the one corresponding to the highest value in COL-C for a given value in COL-D is retained.

Example:
For COL-D = 9300000024
Only one record should be present in output where COL-C = 8

Note: It could be possible that there are two records for the same value in COL-D where the highest value in COL-C is same. In such a case, I want both the records in the output.

Any help is appreciated.
Thanks,
Sid.

guptae

Moderator

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

Posted: Tue Dec 19, 2006 4:59 pm    Post subject:

Hi Sid,

We can acheive it in 2 step

First step sort card

 Code: SORT FIELDS=(15,10,ZD,A,14,1,ZD,D)

Second step sort card

 Code: OPTION EQUALS SORT FIELDS=(15,10,ZD,A) SUM FIELDS=NONE

 Quote: Note: It could be possible that there are two records for the same value in COL-D where the highest value in COL-C is same. In such a case, I want both the records in the output.

I am just try to give u solution . Frank can provide better solution.
siddheart22

New User

Joined: 09 Oct 2006
Posts: 19
Location: Toronto

Posted: Tue Dec 19, 2006 6:04 pm    Post subject:

Hi,

Forgot to mention earlier, that my input file is sorted on COL-D in ascending order.

Using a sort descending on COL-C and de-duping, partially solves my problem.

Is there any way in SORT where I can achieve -
 Quote: Note: It could be possible that there are two records for the same value in COL-D where the highest value in COL-C is same. In such a case, I want both the records in the output.

Thanks,
Sid.
guptae

Moderator

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

Posted: Tue Dec 19, 2006 6:11 pm    Post subject:

Hi Sid,

In that case change the first step sort card to

 Code: SORT FIELDS=(15,10,ZD,D,14,1,ZD,D)

Regarding this as i said earlier frank can provide solution

 Quote: Note: It could be possible that there are two records for the same value in COL-D where the highest value in COL-C is same. In such a case, I want both the records in the output.
siddheart22

New User

Joined: 09 Oct 2006
Posts: 19
Location: Toronto

 Posted: Tue Dec 19, 2006 9:07 pm    Post subject: Hi Frank, If possible, please suggest how can this be achieved using SORT? Thanks, Sid.
Frank Yaeger

DFSORT Moderator

Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

Posted: Tue Dec 19, 2006 10:04 pm    Post subject:

Sid,

Keeping dup records with the highest COL-C makes this a bit tricky, but here's a DFSORT/ICETOOL job that will do what you asked for. I assumed your input file has RECFM=FB and LRECL=80, but the job can be changed appropriately for other attributes.

OUT has:

 Code: 0023000334      USD     2               0023000334        9876201831      GEN     7               0873073159        9315023755      CAD     8               9300000024        9321113079      USD     8               9300018802        4391302155      USD     8               9300018802        9300018810      CAD     2               9300018810        9300018828      CAD     2               9300018828        9325963982      CAD     8               9300018836
 All times are GMT + 6 Hours
 Page 1 of 1

Search our Forum:

 Topic Author Forum Replies Posted Similar Topics How to select record x+7 or x+2 based... JoAnn Kulcyk SYNCSORT 0 Tue Jan 16, 2018 10:49 pm Skip records depends on count lakshmiibmmainframes DFSORT/ICETOOL 5 Sun Dec 24, 2017 9:51 pm Compare two PS files using ICE TOOL f... SENTHIL MURUGAAN DFSORT/ICETOOL 5 Sun Dec 24, 2017 1:26 pm Inefficient BUILD - VB records paddin... Daniel Prosser SYNCSORT 9 Thu Dec 14, 2017 3:52 pm ISSUE IN copying Sequential file reco... thesumitk JCL & VSAM 2 Wed Dec 13, 2017 3:07 pm

 © 2003-2017 IBM MAINFRAME Software Support Division
 Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us