|
View previous topic :: View next topic
|
| Author |
Message |
sudhakarak
New User
Joined: 30 Apr 2020
Posts: 2
Location: INDIA
|
|
|
|
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. |
|
| Back to top |
|
 |
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2228
Location: USA
|
|
|
|
| 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. |
|
| Back to top |
|
|
sudhakarak
New User
Joined: 30 Apr 2020
Posts: 2
Location: INDIA
|
|
|
|
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
|
|
|
| Back to top |
|
|
Joerg.Findeisen
Senior Member
Joined: 15 Aug 2015
Posts: 1394
Location: Bamberg, Germany
|
|
|
|
| Use the possibilities OUTFIL has to offer, namely SECTIONS with NODETAIL. You are pretty close to a good solution. |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2228
Location: USA
|
|
|
|
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! |
|
| Back to top |
|
|
Joerg.Findeisen
Senior Member
Joined: 15 Aug 2015
Posts: 1394
Location: Bamberg, Germany
|
|
|
|
| Leave off the SUM, it is already sorted for deduplication in SECTIONS. |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2228
Location: USA
|
|
|
|
| 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). |
|
| Back to top |
|
|
Joerg.Findeisen
Senior Member
Joined: 15 Aug 2015
Posts: 1394
Location: Bamberg, Germany
|
|
|
|
| sergeyken wrote: |
| The sooner one gets rid of any unneeded stuff, the better (for any further processing). |
Tested and agreed. |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3099
Location: NYC,USA
|
|
|
|
| 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 ******************************** |
|
|
| Back to top |
|
|
Joerg.Findeisen
Senior Member
Joined: 15 Aug 2015
Posts: 1394
Location: Bamberg, Germany
|
|
|
|
| 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. |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2228
Location: USA
|
|
|
|
| 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 |
|
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3099
Location: NYC,USA
|
|
|
|
| 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. |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2228
Location: USA
|
|
|
|
| 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 ****** |
|
|
| Back to top |
|
|
|
|
|
