IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

When first 3 records are matching then remove the duplicates


IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
SCARCEBOYZ

New User


Joined: 16 May 2005
Posts: 32
Location: Millenium Business Park, Mumbai

PostPosted: Tue Aug 05, 2008 6:04 pm
Reply with quote

Hello All,

I have a dataset -

Code:
DUNS       DSTBR     CID                        CTRYCDE        AMT
----------------------------------------------------
A          B         C                            CAN          4.0
A          B         C                            USA          0.89
A          B         C                            USA          4.00
000000000111111111122222222223333333333444444444455555555555
123456789012345678901234567890123456789012345678901234567890


My requirement is -

1-) When first three records are matching , then remove the duplicate entry.Keep USA entry only and remove CAN.

2-) In the above example, A, B, C are matching so I need to remove CAN entry here.

3-) To implement this I am writing 2 sort steps.In first I will sort the dataset in descending order so that USA entry comes first and then I will remove the duplicate in next step.

a-) First sort will change the order so that CTRY Code field is in descending order.

Code:
//******************************************************
//***  ELIMINATE DUPLICATES OF TOTAL SALES                       
//***********************************************************
//STEP105   EXEC SORT,REGION=5M,                                 
//             COND=(4,LT)                                       
//S.SORTIN  DD DSN=I@X4113.FSDST01.FSDT114B.TOTAL.SALES,         
//             DISP=SHR                                         
//S.SORTOUT DD DSN=I@X4113.FSDST01.FSDT114B.TOTAL.SALES.SORT,   
//             DISP=(,CATLG,DELETE),                             
//             UNIT=SYSTEMP,                                     
//             SPACE=(CYL,(1000,120),RLSE),                     
//             DCB=(LRECL=125,RECFM=FB,BLKSIZE=0)               
//S.CNTL    DD   *                                               
 FIELDS=(1,11,A,12,10,A,22,10,A,51,3,D),FORMAT=CH,FILSZ=E500000 
 SORT FIELDS=COPY                                               
 END
//*




b-) To remove the duplicate entries

Code:
//******************************************************
//***  ELIMINATE DUPLICATES ON TOTAL.SALES.SORT                 
//***************************************************************
//STEP106   EXEC SORT,REGION=5M,                                 
//             COND=(4,LT)                                       
//S.SORTIN  DD DSN=I@X4113.FSDST01.FSDT114B.TOTAL.SALES.SORT,   
//             DISP=SHR                                         
//S.SORTOUT DD DSN=I@X4113.FSDST01.FSDT114B.TOTAL.SALES.SORT1,   
//             DISP=(,CATLG,DELETE),                             
//             UNIT=SYSTEMP,                                     
//             SPACE=(CYL,(1000,120),RLSE),                     
//             DCB=(LRECL=125,RECFM=FB,BLKSIZE=0)               
//S.CNTL    DD   *                                               
 FIELDS=(1,11,A,12,10,A,22,10,A),FORMAT=CH,FILSZ=E500000         
 SUM FIELDS=NONE                                                 
 END                                                             
//*


I just wanted to know if there is a way to perform this in one sort step instead of 2.Please help.

Regards,
Vivek
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA

PostPosted: Tue Aug 05, 2008 10:32 pm
Reply with quote

Here's a DFSORT/ICETOOL job that will do what you want in one pass:

Code:

//S1   EXEC  PGM=ICETOOL
//TOOLMSG   DD  SYSOUT=*
//DFSMSG    DD  SYSOUT=*
//IN DD DSN=...  input file
//OUT DD DSN=... output file
//TOOLIN DD *
SELECT FROM(IN) TO(OUT) ON(1,31,CH) FIRST USING(CTL1)
/*
//CTL1CNTL DD *
  SORT FIELDS=(1,31,CH,A,51,3,CH,D)
/*
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DFSORT/ICETOOL

 


Similar Topics
Topic Forum Replies
No new posts Sortjoin and Search for a String and ... DFSORT/ICETOOL 1
No new posts Compare only first records of the fil... SYNCSORT 7
No new posts Pulling a fixed number of records fro... DB2 2
No new posts Remove leading zeroes SYNCSORT 4
No new posts Join multiple records using splice DFSORT/ICETOOL 5
Search our Forums:

Back to Top