|
View previous topic :: View next topic
|
| Author |
Message |
Paresh123
New User
Joined: 02 Jan 2014
Posts: 4
Location: USA
|
|
|
|
Using Syncsort 1.4.0.1R, I would like to limit the number of records in the output that have a particular combination of values for FIELD1 and FIELD2 e.g. in the data below, the particular combinations are
1) "MONKEY" and "YES"
2) "MONKEY" and "NO"
3) "SAM" and "YES"
4) "SAM" and "NO"
| Code: |
FIELD1 FIELD2
MONKEY YES
MONKEY YES
MONKEY YES
MONKEY YES
MONKEY YES
MONKEY NO
SAM YES
SAM YES
SAM NO
SAM NO
SAM NO
SAM NO
SAM NO
|
If I wanted to limit of 2records/combination, the output should look like:
| Code: |
FIELD1 FIELD2
MONKEY YES
MONKEY YES
MONKEY NO
SAM YES
SAM YES
SAM NO
SAM NO
|
One approach to this problem, would be to:
1) Sort by FIELD1, then FIELD2
2) Add a Sequence Number, that restarts whenever FIELD2 Changes
3) Include only records that have a Sequence number less than 3.
This approach falls flat if a particular combination is missing - if the "MONKEY NO" record is missing in the input, then then we go from one combination ("MONKEY YES") to another (SAM YES) without the sequence number restarting. Any suggestions? If I need to be more clear about what I am asking, please let me know. |
|
| Back to top |
|
 |
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
If that is what your data looks like, add a sequence number, using RESTART= and specify both fields as a single field on the RESTART.
The OUTFIL INCLUDE= on the sequence number, and BUILD to cut the record back down to size.
Details depend on V vs F and the length of your fields. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hello and welcome to the forum,
What should happen if field1 or field2 does not contain a "control" value (i.e. MONKEY MAYBE or SAM blank)? |
|
| Back to top |
|
|
Paresh123
New User
Joined: 02 Jan 2014
Posts: 4
Location: USA
|
|
|
|
Hello Dick and thanks for the warm welcome.
The first control field can have 101 different values; the second control field will always be one of two values, but never blank.
Bill,
The actual data differs from what was posted in that the control fields are non-contiguous. The records are fixed length and the Control fields are always in the same position.
I suppose I can reformat the record to make the two control fields contiguous and then use SEQNUM with the RESTART parameter on that combined control field. I think this is the solution. Thank you both for your help! I'll post some sample data and code for future reference in a future post. |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
| Yes, I think that will do. Your sample data shows that the key fields are contiguous, even if not in sorted order. If they are non-contiguous, you will have to SORT, otherwise I think you have the correct approach. |
|
| Back to top |
|
|
Paresh123
New User
Joined: 02 Jan 2014
Posts: 4
Location: USA
|
|
|
|
Task: Limit unique combinations of non-contiguous control fields (fields 1 and 3) to two records each using Syncsort.
Input Text
| Code: |
Field1 Field2 Field3
MONKEY NONSENSE YES
MONKEY NONSENSE YES
MONKEY NONSENSE YES
MONKEY NONSENSE YES
MONKEY NONSENSE YES
MONKEY MEANINGLESS YES
MONKEY MEANINGLESS YES
MONKEY MEANINGLESS MAYBE
MONKEY MEANINGLESS NO
MONKEY MEANINGLESS NO
MONKEY MEANINGLESS NO
SAM MEANINGLESS YES
SAM MEANINGLESS MAYBE
SAM MEANINGLESS MAYBE
SAM MEANINGLESS MAYBE
SAM MEANINGLESS MAYBE
SAM MEANINGLESS NO
SAM MEANINGLESS NO
SAM MEANINGLESS NO
SAM MEANINGLESS NO
SAM MEANINGLESS NO
|
Output:
| Code: |
MONKEY NONSENSE YES
MONKEY NONSENSE YES
MONKEY MEANINGLESS MAYBE
MONKEY MEANINGLESS NO
SAM MEANINGLESS YES
SAM MEANINGLESS MAYBE
SAM MEANINGLESS MAYBE
SAM MEANINGLESS NO
SAM MEANINGLESS NO
|
One Solution:
| Code: |
//JS01 EXEC PGM=SYNCSORT
//SORTIN DD DISP=SHR,DSN=TEST.INPUT
//SORTOUT DD DSN=TEST.SEQNUM.ADDED
// UNIT=TESTCI,SPACE=(TRK,(10)),
// DCB=RECFM=FB,BLKSIZE=0,LRECL=26),
// DISP=NEW,CATLG,DELETE)
//SORTWK01 DD UNIT=TEST,SPACE=(TRK,(10))
//SYSOUT DD SYSOUT=*
//SYSIN DD *
INREC FIELDS=(01:08,12, Field2
13:01,07, Field1
20:20,05) Field3
SORT FIELDS=(13,12,CH,A)
OUTREC FIELDS=(1,24,25:SEQNUM,2,ZD,RESTART=(13,12))
//JS02 EXEC PGM=SYNCSORT
//SORTIN DD DISP=SHR,DSN=TEST.SEQNUM.ADDED
//SORTOUT DD DSN=TEST.OUTPUT,
// UNIT=TESTCI,SPACE=(TRK,(10)),
// DCB=RECFM=FB,BLKSIZE=0,LRECL=24),
// DISP=NEW,CATLG,DELETE)
//SYSOUT DD SYSOUT=*
//SYSIN DD *
INCLUDE COND=(25,2,ZD,LE,3)
SORT FIELDS=COPY
OUTREC FIELDS=(01:13,07, Field 1
08:01,12, Field 2
20:20,05) Field 3 |
|
|
| Back to top |
|
|
|
|
  |
All times are GMT + 6 Hours |
|
