Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

When first 3 records are matching then remove the duplicates

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
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    Post subject: When first 3 records are matching then remove the duplicates
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 Moderator


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

PostPosted: Tue Aug 05, 2008 10:32 pm    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Check if any Detail records and extra... V S Amarendra Reddy SYNCSORT 19 Mon May 08, 2017 8:54 pm
No new posts Format record to remove Leading zeroes Learncoholic DFSORT/ICETOOL 14 Wed Apr 05, 2017 2:43 pm
No new posts Join giving more records than expected Danielle.Filteau SYNCSORT 2 Thu Mar 09, 2017 3:28 am
No new posts TSO or command line utility to genera... kishpra JCL & VSAM 3 Thu Mar 09, 2017 1:11 am
No new posts Extract set of records matching on ac... bhaskar_kanteti DFSORT/ICETOOL 3 Mon Mar 06, 2017 7:19 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us