# INCLUDE COND with WHEN=GROUP

Author Message
sudhakarak

New User

Joined: 30 Apr 2020
Posts: 2
Location: INDIA

Posted: Mon Jun 21, 2021 6:55 pm

I have a requirement where the records are present in the following format

Let's consider 1-5 bytes is person id. He might have multiple records. 7th byte is the determination indicator. If Person has 1 in the 7th position. I need to populate only record that has 1 in the 7th position. If a person doesn't have 1 record then I need to populate the M row. Please find the below input and expected output

INPUT:
 Code: 11111|A|25 11111|0|35 11111|M|45 11111|1|46 22222|A|10 22222|M|05 22222|0|03

Expected OUTPUT:

 Code: 11111|1|46 22222|M|05

I have tried using SORT WHEN=GROUP but I couldn't able to achieve it. Please share your thoughts.
sergeyken

Senior Member

Joined: 29 Apr 2008
Posts: 2103
Location: USA

Posted: Mon Jun 21, 2021 7:08 pm

 sudhakarak wrote: I have tried using SORT WHEN=GROUP but I couldn't able to achieve it. Please share your thoughts.

Please, present here the results of your attempts, including the used code, the test input, the received output, and the error messages (if any).

This forum is used to give an advice, but not to do a job for other people, free of charge.
sudhakarak

New User

Joined: 30 Apr 2020
Posts: 2
Location: INDIA

Posted: Mon Jun 21, 2021 7:54 pm

Sorry for not adding my solutions. Out of many possibilities I got the expected result with two steps by doing the below but I'm guessing there might be a better solution than this.

 Code: //STEP01 EXEC PGM=SORT //SYSOUT DD SYSOUT=* //SORTIN   DD * 11111|A|25 11111|0|35 11111|M|45 11111|1|46 22222|A|10 22222|M|05 22222|0|03 //SORTOUT  DD SYSOUT=* //SYSIN DD *  SORT FIELDS=(1,5,CH,A,7,1,CH,D)  INCLUDE COND=(7,1,SS,EQ,C'1,M') /* //STEP02 EXEC PGM=SORT //SYSOUT DD SYSOUT=* //SORTIN   DD * 11111|1|46 11111|M|45 22222|M|05 //SORTOUT  DD SYSOUT=* //SYSIN DD *  SORT FIELDS=(1,5,CH,A)  SUM FIELDS=NONE /*

Output:

 Code: 11111|1|46 22222|M|05
Joerg.Findeisen

Senior Member

Joined: 15 Aug 2015
Posts: 1297
Location: Bamberg, Germany

 Posted: Mon Jun 21, 2021 8:11 pm Use the possibilities OUTFIL has to offer, namely SECTIONS with NODETAIL. You are pretty close to a good solution.
sergeyken

Senior Member

Joined: 29 Apr 2008
Posts: 2103
Location: USA

Posted: Mon Jun 21, 2021 8:22 pm

Your criteria described a little doubtful.
As far as I understand them, it should work like this:
 Code: INCLUDE COND=(7,1,SS,EQ,C'1M')   ignore all garbage immediately  SORT FIELDS=(1,5,CH,A,           group by userid               7,1,CH,A)           and order by priority  SUM FIELD=NONE                   get rid of duplicates  OUTFIL NODETAIL,REMOVECC,         SECTIONS=(1,5,HEADER3=(1,80))   leave only the best record  END

Keep in mind: SORT executes the statements not in the order you have placed them, but as described in the SORT manual!
Joerg.Findeisen

Senior Member

Joined: 15 Aug 2015
Posts: 1297
Location: Bamberg, Germany

 Posted: Mon Jun 21, 2021 8:28 pm Leave off the SUM, it is already sorted for deduplication in SECTIONS.
sergeyken

Senior Member

Joined: 29 Apr 2008
Posts: 2103
Location: USA

Posted: Mon Jun 21, 2021 9:23 pm

 Joerg.Findeisen wrote: Leave off the SUM, it is already sorted for deduplication in SECTIONS.

The sooner one gets rid of any unneeded stuff, the better (for any further processing).
Joerg.Findeisen

Senior Member

Joined: 15 Aug 2015
Posts: 1297
Location: Bamberg, Germany

Posted: Mon Jun 21, 2021 9:34 pm

 sergeyken wrote: The sooner one gets rid of any unneeded stuff, the better (for any further processing).

Tested and agreed.
Rohit Umarjikar

Global Moderator

Joined: 21 Sep 2010
Posts: 3072
Location: NYC,USA

Posted: Tue Jun 22, 2021 1:01 am

 Code: //S1      EXEC PGM=SORT              //SORTIN   DD  *                      11111|A|25                            11111|0|35                            11111|M|45                            11111|1|46                            22222|A|10                            22222|M|05                            22222|0|03                            33333|A|10                            33333|0|03                            44444|0|10                            44444|M|05                            44444|1|03                            55555|1|10                            55555|1|05                            55555|M|03                            //SORTOUT   DD SYSOUT=*              //SYSOUT   DD  SYSOUT=*              //SYSIN     DD *                        SORT FIELDS=(1,5,A,7,1,D,81,1,A),FORMAT=CH                INCLUDE COND=(7,1,CH,EQ,C'1',OR,7,1,CH,EQ,C'M')            INREC IFTHEN=(WHEN=(7,1,CH,EQ,C'1'),OVERLAY=(81:C'1')),         IFTHEN=(WHEN=(7,1,CH,EQ,C'M'),OVERLAY=(81:C'2'))    OUTREC OVERLAY=(81:SEQNUM,3,ZD,RESTART=(1,5))              OUTFIL BUILD=(1,80),INCLUDE=(81,3,ZD,EQ,1)

Output -
 Code: ********************************* TOP OF DATA ********************************** 11111|1|46                                                                      22222|M|05                                                                      44444|1|03                                                                      55555|1|10                                                                      ******************************** BOTTOM OF DATA ********************************
Joerg.Findeisen

Senior Member

Joined: 15 Aug 2015
Posts: 1297
Location: Bamberg, Germany

Posted: Tue Jun 22, 2021 7:23 am

sergeyken wrote:
 Code: INCLUDE COND=(7,1,SS,EQ,C'1M')   ignore all garbage immediately  SORT FIELDS=(1,5,CH,A,           group by userid               7,1,CH,A)           and order by priority  SUM FIELD=NONE                   get rid of duplicates  OUTFIL NODETAIL,REMOVECC,         SECTIONS=(1,5,HEADER3=(1,80))   leave only the best record  END

Change HEADER3 to TRAILER3 to get the desired results.
sergeyken

Senior Member

Joined: 29 Apr 2008
Posts: 2103
Location: USA

Posted: Tue Jun 22, 2021 5:27 pm

Joerg.Findeisen wrote:
sergeyken wrote:
 Code: INCLUDE COND=(7,1,SS,EQ,C'1M')   ignore all garbage immediately  SORT FIELDS=(1,5,CH,A,           group by userid               7,1,CH,A)           and order by priority  SUM FIELD=NONE                   get rid of duplicates  OUTFIL NODETAIL,REMOVECC,         SECTIONS=(1,5,HEADER3=(1,80))   leave only the best record  END

Change HEADER3 to TRAILER3 to get the desired results.

Oops, I messed up the priority between '1' and 'M' records...

Initially I coded SORT FIELDS=(…,7,1,CH,D) to make it in the proper order, but then something hit on my head, and I changed the sort order to 'A'...

This was my initial code, before my unneeded fix
 Code: INCLUDE COND=(7,1,SS,EQ,C'1M')   ignore all garbage immediately  SORT FIELDS=(1,5,CH,A,           group by userid               7,1,CH,D)           and order by priority  SUM FIELD=NONE                   get rid of duplicates  OUTFIL NODETAIL,REMOVECC,         SECTIONS=(1,5,HEADER3=(1,80))   leave only the best record  END
Rohit Umarjikar

Global Moderator

Joined: 21 Sep 2010
Posts: 3072
Location: NYC,USA

Posted: Wed Jun 23, 2021 8:10 pm

sergeyken wrote:
Joerg.Findeisen wrote:
sergeyken wrote:
 Code: INCLUDE COND=(7,1,SS,EQ,C'1M')   ignore all garbage immediately  SORT FIELDS=(1,5,CH,A,           group by userid               7,1,CH,A)           and order by priority  SUM FIELD=NONE                   get rid of duplicates  OUTFIL NODETAIL,REMOVECC,         SECTIONS=(1,5,HEADER3=(1,80))   leave only the best record  END

Change HEADER3 to TRAILER3 to get the desired results.

Oops, I messed up the priority between '1' and 'M' records...

Initially I coded SORT FIELDS=(…,7,1,CH,D) to make it in the proper order, but then something hit on my head, and I changed the sort order to 'A'...

This was my initial code, before my unneeded fix
 Code: INCLUDE COND=(7,1,SS,EQ,C'1M')   ignore all garbage immediately  SORT FIELDS=(1,5,CH,A,           group by userid               7,1,CH,D)           and order by priority  SUM FIELD=NONE                   get rid of duplicates  OUTFIL NODETAIL,REMOVECC,         SECTIONS=(1,5,HEADER3=(1,80))   leave only the best record  END

Please test this , This is not producing the desired output as you still need TRAILER3.
sergeyken

Senior Member

Joined: 29 Apr 2008
Posts: 2103
Location: USA

Posted: Wed Jun 23, 2021 8:19 pm

 Code: //STEPGRP  EXEC PGM=SORT //SYNOUT   DD  SYSOUT=* //SORTIN   DD  * 11111|A|25 11111|0|35 11111|M|45 11111|1|46 22222|A|10 22222|M|05 22222|0|03 //* //SORTOUT  DD  SYSOUT=* //* //SYSIN    DD *  INCLUDE COND=(7,1,SS,EQ,C'1M')   IGNORE ALL GARBAGE IMMEDIATELY  SORT FIELDS=(1,5,CH,A,           GROUP BY USERID               7,1,CH,D)           AND ORDER BY PRIORITY  SUM FIELDS=NONE                   GET RID OF DUPLICATES  OUTFIL NODETAIL,REMOVECC,         SECTIONS=(1,5,HEADER3=(1,80))   LEAVE ONLY THE BEST RECORD  END //*

 Code: ********************************* TOP OF DATA ******** 11111|1|46 22222|M|05 ******************************** BOTTOM OF DATA ******
 View Bookmarks All times are GMT + 6 Hours

 Topic Forum Replies Similar Topics INCLUDE OMIT COND for Multiple values... DFSORT/ICETOOL 5 Compare latest 2 rows of a table usin... DB2 1 Reference for COND parameter. JCL & VSAM 1 Problem with IFTHEN=(WHEN=GROUP,BEGIN... DFSORT/ICETOOL 5 Splitting group records based on deta... DFSORT/ICETOOL 8
Search our Forums:

 IBMMainframes.com is not an official and/or affiliated with IBM® in anyway Board Rules | FAQ | Downloads | Wiki | SiteMap | Contact Us