IBM Mainframe Forum

Posted: **Tue May 20, 2008 6:26 pm**

Hello, I'm am tring to solve a sorting problem that I have, I've have been reading the getting started guide and I think I need to use some kind of omit or include statement, but i'm not entirely sure..

I have made a simplified example of the data I am working with:

Select all

BRANCH MONTH SALES

Select all

hastings    1 3453
hastings    2 3443
hastings    3 4754
hastings    4 4232
hastings    5 4124
hastings    6 5345
hastings    7 5235
hastings    8 6325
hastings    9 5213
hastings    10 4867
hastings    11 3325
hastings    12 2274
bexhill    1 3345
bexhill    2 3263
bexhill    3 4754
bexhill    4 7658
bexhill    5 2345
bexhill    6 5123
bexhill    7 5342
bexhill    8 6432
bexhill    9 5543
bexhill    10 4723
bexhill    11 3234
bexhill    12 2236
eastbourne 1 3326
eastbourne 2 3643
eastbourne 3 4765
eastbourne 4 4965
eastbourne 5 4089
eastbourne 6 5803
eastbourne 7 5534
eastbourne 8 6754
eastbourne 9 5367
eastbourne 10 4347
eastbourne 11 3374
eastbourne 12 2345

The field ranges are like this:

BRANCH 1,12
MONTH 17,2
SALES 25,5

I have written this sort statement to sort the data:

Select all

//CTL2CNTL DD *
SORT FIELDS(17,2,ZD,A,25,5,ZD,D)
/*

This statement first sorts the SALES in decending order like this:

Select all

bexhill 4 7658
eastbourne 8 6754
bexhill    8 6432
hastings    8 6325
eastbourne 6 5803
bexhill    9 5543
eastbourne 7 5534
eastbourne 9 5367
hastings    6 5345
bexhill    7 5342
hastings    7 5235
hastings    9 5213
bexhill    6 5123
eastbourne 4 4965
hastings    10 4867
eastbourne 3 4765
hastings    3 4754
bexhill    3 4754
bexhill    10 4723
eastbourne 10 4347
hastings    4 4232
hastings    5 4124
eastbourne 5 4089
eastbourne 2 3643
hastings    1 3453
hastings    2 3443
eastbourne 11 3374
bexhill    1 3345
eastbourne 1 3326
hastings    11 3325
bexhill    2 3263
bexhill    11 3234
bexhill    5 2345
eastbourne 12 2345
hastings    12 2274
bexhill    12 2236

Then it sorts the MONTHS in accending order, to produce the final output shown below:

Select all

hastings    1 3453
bexhill    1 3345
eastbourne 1 3326
eastbourne 2 3643
hastings    2 3443
bexhill    2 3263
eastbourne 3 4765
hastings    3 4754
bexhill    3 4754
bexhill    4 7658
eastbourne 4 4965
hastings    4 4232
hastings    5 4124
eastbourne 5 4089
bexhill    5 2345
eastbourne 6 5803
hastings    6 5345
bexhill    6 5123
eastbourne 7 5534
bexhill    7 5342
hastings    7 5235
eastbourne 8 6754
bexhill    8 6432
hastings    8 6325
bexhill    9 5543
eastbourne 9 5367
hastings    9 5213
hastings    10 4867
bexhill    10 4723
eastbourne 10 4347
eastbourne 11 3374
hastings    11 3325
bexhill    11 3234
eastbourne 12 2345
hastings    12 2274
bexhill    12 2236

My problem is that I only want to keep the data for the highest sales location for each month. I want to get an output like this, so i can see the best performing location:

Select all

hastings1 3453
eastbourne 2 3643
eastbourne 3 4765
bexhill 4 7658
hastings 5 4124
eastbourne 6 5803
eastbourne 7 5534
eastbourne 8 6754
bexhill 9 5543
hastings 10 4867
eastbourne 11 3374
eastbourne 12 2345

I'm not sure how to tell the sort only to keep the highest sales row for each month, can somebody help me please?

Posted: **Tue May 20, 2008 6:34 pm**

Sorry that post came out a bit of a mess, I didn't realise post cannot be edited after posting, sorry.

Here's a .txt file that shows the fomating of the above post correctly,

http://e66n06.fu8.com/sortquestion.txt

XD

Posted: **Tue May 20, 2008 9:04 pm**

Here's a DFSORT/ICETOOL job that will do what you asked for:

Select all

//S1 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN DD DSN=... input file
//OUT DD DSN=... output file
//TOOLIN DD *
SELECT FROM(IN) TO(OUT) ON(17,2,ZD) FIRST USING(CTL1)
/*
//CTL1CNTL DD *
SORT FIELDS(17,2,ZD,A,25,4,ZD,D)
/*

If you're not familiar with DFSORT and DFSORT's ICETOOL, I'd suggest reading through "z/OS DFSORT: Getting Started". It's an excellent tutorial, with lots of examples, that will show you how to use DFSORT, DFSORT's ICETOOL and DFSORT Symbols. You can access it online, along with all of the other DFSORT books, from:

http://www.ibm.com/servers/storage/supp ... tmpub.html

Posted: **Tue May 20, 2008 11:56 pm**

Thank you, that was really helpful, just what I needed

IBM Mainframe Forum

Omitting records question

Omitting records question

Re: Omitting records question

Re: Omitting records question

Re: Omitting records question