IBM Mainframe Forum

[mainframegeek]

How to Split the record in two part and remove the duplicate using syncsort?
File 1
01 FILE-REC
05 AREA-NMBR PIC 9(5)
05 BEG-RANGE PIC 9(10)
05 END-RANGE PIC 9(10)

DATA IN FILE 1

0001952000000000100000008
0001952000000001000000020
0001952000000003000000040
0012121000000011100000121
0012121000000015100000191

OUTPUT FILE
0001952000000000100000040
0012121000000011100000191

REQUIREMENT
For a particular area number we have more than one record, out of these records we have to arrange minimum value for BEG-RANGE field and maximum value for END-RANGE in a record.

as per example values of BEG-RANGE for 0001952 AREA-NMBR
0000000001
0000000010
0000000030

out of these we need to get minimum value 0000000001

as per example values of END-RANGE for 0001952 AREA-NMBR
00000008
00000020
00000040
out of these we need to get maximum value 0000000040

and at last we need to put these two values with AREA-NMBR
like 000195200000000010000000040

Likewise we have to do for other records. Area-NMBR is getting repeated but other two fields are unique.
Can anyone help me doing this with the help of synsort 1.3.2 version.

[BillyBoyo]

Puneet,

Do you have to take the beginning range from the first record for that key and the ending range from the last record for the key?

Or, are you OK with the MINimum beginning value for the key and the MAXimum ending value for the key.

As you already know, the first is difficult and the second is easy.

[mainframegeek]

Values could be arranged in any order.
For Example, this is given above
0001952000000000100000008
0001952000000001000000020
0001952000000003000000040

It could be in any order.Like given below
0001952000000003000000040
0001952000000001000000020
0001952000000000100000008
Output should be
0001952000000001000000040

The purpose is to arrange the records in such a way that minimum value of BEG-RANGE and maximum range of END-RANGE will come for that particular area number in a single record. After doing that single record has to be there, which should have minimum value BEG-RANGE and maximum value of END-RANGE.

[BillyBoyo]

And did you get to try the OUTFIL with SECTIONS and TRAILER3 solution?

[mainframegeek]

I tried with the solution given below and got the error:
JOINKEYS FILES=F1,FIELDS=(26,8,A),SORTED,NOSEQCK
*
JOINKEYS FILES=F2,FIELDS=(26,8,A),SORTED,NOSEQCK
*
JOIN UNPAIRED,F1
REFORMAT FIELDS=(F1:1,25,F2:1,5)
INREC IFOUTLEN=26,
IFTHEN=(WHEN=GROUP,BEGIN=(1,5),PUSH=(32:6,10))
*
IFTHEN=(WHEN=(1,5,CH,NE,26,5,CH,OR,31,1,ZD,EQ,1),
*
OVERLAY=(6:32,10,26:C'X'))
*
OUTFIL INCLUDE=(26,1,CH,EQ,C'X'),BUILD=(1,25)
WER268A JOINKEYS STATEMENT: SYNTAX ERROR
WER268A JOINKEYS STATEMENT: SYNTAX ERROR
WER251A INCLUDE/OMIT INVALID FORMAT
WER275A NO KEYWORDS FOUND ON CONTROL STATEMENT
WER275A NO KEYWORDS FOUND ON CONTROL STATEMENT
WER449I SYNCSORT GLOBAL DSM SUBSYSTEM ACTIVE

[BillyBoyo]

And did you get to try the OUTFIL with SECTIONS and TRAILER3 solution?

You've been told that the JOINKEYS won't work. Let alone anything else, the JNFnCNTL files are not supported.

[bodatrinadh]

Hi,

You can try this snippet..

Select all

//STEP2    EXEC PGM=SORT     
//SYSOUT   DD SYSOUT=*       
//SYSPRINT DD SYSOUT=*       
//SORTIN   DD *               
0001952000000000100000008     
0001952000000001000000020     
0001952000000003000000040     
0012121000000011100000121     
0012121000000015100000191     
//SORTOUT DD SYSOUT=*         
//SYSIN    DD *               
  SORT FIELDS=(1,7,CH,A)     
  DUPKEYS MAX=(18,08,ZD)     

Output :-

Select all

0001952000000000100000040   
0012121000000011100000191   

[BillyBoyo]

-3nadh,

That's nice.

Puneet messed up the test data, so 1,5 and 6,10, 16,10 are the fields.

Does it work with MERGE as the file is already in key order?

Select all

OPTION EQUALS
MERGE FIELDS=(1,5,CH,A)
DUPKEYS MAX=(16,10,ZD)

Also, any other options on DUPKEYS? Presumably MIN. What about FIRST and LAST?

Is this for 1.4 only? Puneet has 1.3.2.

[BillyBoyo]

Hi Billy,

If the test data has 1,5 and 6,10, 16,10 key fields then i need to try some alternate code...

Does it work with MERGE as the file is already in key order?

I tried your code Billy..

Select all

OPTION EQUALS
MERGE FIELDS=(1,5,CH,A)
DUPKEYS MAX=(16,10,ZD)

Output i got is

Select all

0001952000000003000000040
0012121000000015100000191

Also, any other options on DUPKEYS? Presumably MIN. What about FIRST and LAST?

Is this for 1.4 only? Puneet has 1.3.2.

DUPKEYS will work from 1.3.2 version.

[BillyBoyo]

Excellent -3nadh,

Interesting with the MERGE - keeps the entire MAX record maybe?

I was wondering because with DFSort you can use MERGE with SORTIN01 only and then SUM FIELDS=NONE works with an already sorted file (can't do the SUM FIELDS=NONE with SORT FIELDS=NONE or OPTION,COPY).

Can you just add about other options for the DUPKEYS, please?