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
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.
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!
********************************* TOP OF DATA **********************************
11111|1|46
22222|M|05
44444|1|03
55555|1|10
******************************** BOTTOM OF DATA ********************************
Joined: 15 Aug 2015 Posts: 1334 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.
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
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.
//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 ******