Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Grouping as well as summing like records

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
raam_kumar

New User


Joined: 25 Apr 2007
Posts: 44
Location: chennai, India

PostPosted: Wed Apr 16, 2008 10:25 pm    Post subject: Grouping as well as summing like records
Reply with quote

Hi

I have the following requirement.

Sample Input:-
<Pos 1 to 5><pos 6 to 8> <Pos 9 to 11>
AAAAA 111 250
AAAAA 121 150
AAAAA 111 100
BBBBB 111 000
BBBBB 111 125
DDDDD 111 100
DDDDD 121 100

Expected Output:-

AAAAA 350 150 500
BBBBB 125 000 125
DDDDD 100 100 200

Where 350 is sum of all "111" belonging to "AAAAA"
150 is sum of all "121" belonging to "AAAAA"
500 is the Addition of the values 350 and 150

Can this be achieved using sort.
Back to top
View user's profile Send private message

Frank Yaeger

DFSORT Moderator


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

PostPosted: Wed Apr 16, 2008 11:22 pm    Post subject:
Reply with quote

Do you only have '111' and '121' for the second field? If not, what do you want to do about other second field values (e.g. '333')?
Back to top
View user's profile Send private message
raam_kumar

New User


Joined: 25 Apr 2007
Posts: 44
Location: chennai, India

PostPosted: Thu Apr 17, 2008 12:12 pm    Post subject: Reply to: Grouping as well as summing like records
Reply with quote

Hi Frank

Actually there are 6 possible values that can come in second field. All are of 3 character numeric.

111
211
311 belongs to one group

121
131
141 belongs to second group
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Thu Apr 17, 2008 10:25 pm    Post subject:
Reply with quote

raam_kumar,

The following DFSORT JCL will give you the desired results

Code:

//STEP0100 EXEC PGM=ICEMAN                           
//SYSOUT   DD SYSOUT=*                               
//SORTIN   DD *                                       
----+----1----+----2----+----3----+----4----+----5----
AAAAA 111 250                                         
AAAAA 121 150                                         
AAAAA 111 100                                         
AAAAA 311 100                                         
BBBBB 111 000                                         
BBBBB 111 125                                         
CCCCC 121 125                                         
CCCCC 131 125                                         
DDDDD 111 100                                         
DDDDD 121 100                                         
//SORTOUT  DD SYSOUT=*                               
//SYSIN    DD *                                       
  SORT FIELDS=(01,09,CH,A)                           
  OUTREC IFTHEN=(WHEN=INIT,OVERLAY=(20:18C'0')),     
  IFTHEN=(WHEN=(7,3,CH,EQ,C'111'),OVERLAY=(20:11,3)),
  IFTHEN=(WHEN=(7,3,CH,EQ,C'211'),OVERLAY=(23:11,3)),
  IFTHEN=(WHEN=(7,3,CH,EQ,C'311'),OVERLAY=(26:11,3)),
  IFTHEN=(WHEN=(7,3,CH,EQ,C'121'),OVERLAY=(29:11,3)),
  IFTHEN=(WHEN=(7,3,CH,EQ,C'131'),OVERLAY=(32:11,3)),
  IFTHEN=(WHEN=(7,3,CH,EQ,C'141'),OVERLAY=(35:11,3)) 
  OUTFIL REMOVECC,NODETAIL,                           
  BUILD=(80X),                                       
  SECTIONS=(1,5,                                     
  TRAILER3=(1,5,X,TOT=(20,3,ZD,M10,LENGTH=8),X,       
                  TOT=(23,3,ZD,M10,LENGTH=8),X,       
                  TOT=(26,3,ZD,M10,LENGTH=8),X,       
                  TOT=(29,3,ZD,M10,LENGTH=8),X,       
                  TOT=(32,3,ZD,M10,LENGTH=8),X,       
                  TOT=(35,3,ZD,M10,LENGTH=8)))       
/*


The output from the above job is
Code:

AAAAA      350        0      100      150        0        0
BBBBB      125        0        0        0        0        0
CCCCC        0        0        0      125      125        0
DDDDD      100        0        0      100        0        0


Hope this helps...
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Summing on Mixed Case Literals Roy Ware SYNCSORT 3 Tue Jan 03, 2017 10:18 pm
No new posts Removing Duplicates based on certain ... chandracdac DFSORT/ICETOOL 8 Fri Dec 09, 2016 4:40 am
No new posts Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
No new posts How to split the records using the am... vnktrrd DFSORT/ICETOOL 24 Fri Oct 28, 2016 7:33 pm
No new posts Sort records based on numeric field. Alks SYNCSORT 2 Wed Oct 19, 2016 10:14 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us