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

Author Message
raam_kumar

New User

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

 Posted: Wed Apr 16, 2008 10:25 pm    Post subject: Grouping as well as summing like records Hi I have the following requirement. Sample Input:- 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.

Frank Yaeger

DFSORT Moderator

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

 Posted: Wed Apr 16, 2008 11:22 pm    Post subject: 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')?
raam_kumar

New User

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

 Posted: Thu Apr 17, 2008 12:12 pm    Post subject: Reply to: Grouping as well as summing like records 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
Skolusu

Senior Member

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

Posted: Thu Apr 17, 2008 10:25 pm    Post subject:

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...
 All times are GMT + 6 Hours
 Page 1 of 1

Search our Forum:

 Topic Author Forum Replies Posted Similar Topics MXG - Processing Compressed DB2 SMF r... vasanthz All Other Mainframe Topics 2 Thu May 10, 2018 12:47 am Extract the records with a PD field's... sudhakar84 DFSORT/ICETOOL 11 Mon Apr 02, 2018 7:26 pm Merge 2 records sancraig16 SYNCSORT 19 Tue Mar 27, 2018 8:17 pm Copy set of records based on condition krish.deepu SYNCSORT 2 Thu Mar 08, 2018 11:39 am add sequence number for duplicate rec... SRUTHI DRAVIDAMANI COBOL Programming 11 Thu Feb 22, 2018 5:37 pm

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