IBM Mainframe Forum

Mainframe Technical Support Forums

Skip to content


Omitting records question



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

Omitting records question

Postby e66n06 » 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?
e66n06
 
Posts: 7
Joined: Wed May 14, 2008 3:53 pm
Has thanked: 0 time
Been thanked: 0 time
Top

Re: Omitting records question

Postby e66n06 » 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
e66n06
 
Posts: 7
Joined: Wed May 14, 2008 3:53 pm
Has thanked: 0 time
Been thanked: 0 time
Top

Re: Omitting records question

Postby Frank Yaeger » 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
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
User avatar
Frank Yaeger
Global moderator
 
Posts: 1079
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 15 times
Top

Re: Omitting records question

Postby e66n06 » Tue May 20, 2008 11:56 pm

Thank you, that was really helpful, just what I needed :D
e66n06
 
Posts: 7
Joined: Wed May 14, 2008 3:53 pm
Has thanked: 0 time
Been thanked: 0 time
Top
Post a reply

Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post
Forum Rules|FAQ|Subscriptions|Bookmarks