sorting a file - sum fields and removing duplicates



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

sorting a file - sum fields and removing duplicates

Postby test » Tue Dec 18, 2012 2:45 am

Hi

I'm very new to this and would love to hear if this is possible to achive and how to solve it.

My file is:
userid    acct.    note    cost-cmp3
333-444   222333   TEXT2   -10
333-444   222222   TEXT1   -20
111-222   111111   TEXT1   -10
111-222   111111   TEXT1   +10
111-222   111111   TEXT2   -20
555-666   111111   TEXT1   -10


Now, is it possible in my JCL to add a sort that sorts and reduce my file?

The output should be something like this:
userid    acct.    note    cost-cmp3
111-222   111111   TEXT1   +00
111-222   111111   TEXT2   -20
333-444   222222   TEXT1   -10
333-444   222333   TEXT2   -20
555-666   111111   TEXT1   -10


The file is sorted by userid, acct and note.
And duplicates (where userid, acct, and notes are same) we remove the the line and sumerize all costs together (see -10 and +10 we get 0).

Is this possible?

How do I solve this? Do you have any good sort manual?

Please advice

Thank you.
test
 
Posts: 3
Joined: Tue Dec 18, 2012 2:14 am
Has thanked: 3 times
Been thanked: 0 time

Re: sorting a file - sum fields and removing duplicates

 

Re: sorting a file - sum fields and removing duplicates

Postby Akatsukami » Tue Dec 18, 2012 3:34 am

As you have posted in the DFSORT forum, I shall presume until it is shown otherwise that you indeed have DFSORT, and not Syncsort, CA-SORT, Sort of a Different Colo(u)r, or some other product.

The DFSORT programming manual is here. Read it. This is an extremely elementary task, even by the standards of software engineers; if you cannot figure it out on your own, your prospects for a career in the IT industry are dim indeed.
"You have sat too long for any good you have been doing lately ... Depart, I say; and let us have done with you. In the name of God, go!" -- what I say to a junior programmer at least once a day

These users thanked the author Akatsukami for the post:
test (Wed Jan 30, 2013 6:13 pm)
User avatar
Akatsukami
Global moderator
 
Posts: 1053
Joined: Sat Oct 16, 2010 2:31 am
Location: Bloomington, IL
Has thanked: 6 times
Been thanked: 51 times

Re: sorting a file - sum fields and removing duplicates

Postby BillyBoyo » Tue Dec 18, 2012 3:52 am

The DFSORT Getting Started is here. You will find examples. You need SORT and SUM.

The manuals in the links are also available as PDFs.

These users thanked the author BillyBoyo for the post:
test (Wed Jan 30, 2013 6:13 pm)
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times

Re: sorting a file - sum fields and removing duplicates

Postby skolusu » Tue Dec 18, 2012 4:14 am

use the following DFSORT JCL which will give you the desired results

//STEP0100 EXEC PGM=SORT                       
//SYSOUT   DD SYSOUT=*                         
//SORTIN   DD DSN=Your Input FB file,DISP=SHR               
//SORTOUT  DD SYSOUT=*                         
//SYSIN    DD *                               
  SORT FIELDS=(01,10,CH,A,           $ USER-ID
               11,06,CH,A,           $ ACCT   
               20,04,CH,A)           $ NOTE   
                                               
  SUM FIELDS=(25,6,PD)               $ SUM COST
//*
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

These users thanked the author skolusu for the post:
test (Wed Jan 30, 2013 6:13 pm)
skolusu
 
Posts: 586
Joined: Wed Apr 02, 2008 10:38 pm
Has thanked: 0 time
Been thanked: 39 times

Re: sorting a file - sum fields and removing duplicates

Postby test » Wed Jan 30, 2013 6:14 pm

Thank you all for the urls !
and for the solution skolusu!
test
 
Posts: 3
Joined: Tue Dec 18, 2012 2:14 am
Has thanked: 3 times
Been thanked: 0 time


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post