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
 

 

Need to remove duplicate column wise

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM
View previous topic :: :: View next topic  
Author Message
jemonthomas

New User


Joined: 24 Jul 2006
Posts: 35
Location: india

PostPosted: Tue Jan 03, 2012 4:04 pm    Post subject: Need to remove duplicate column wise
Reply with quote

Hi,

I need to remove duplicate column wise in a jcl.

For Example

Input File

ABC Thomas
ABC Richard
ABC Mathew
ABC Tom
BCD Neena
BCD Namitha
BCD Nisha
CDE Arun
CDE Nirmal

I need ouput as

ABC Thomas
Richard
Mathew
Tom
BCD Neena
Namitha
Nisha
CDE Arun
Nirmal


Could anyone please help me on this?
Back to top
View user's profile Send private message

Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Tue Jan 03, 2012 4:05 pm    Post subject: Reply to: Need to remove duplicate column wise
Reply with quote

What would you like to remove it with? A program you write? A Sort step? Something else?
Back to top
View user's profile Send private message
jemonthomas

New User


Joined: 24 Jul 2006
Posts: 35
Location: india

PostPosted: Tue Jan 03, 2012 4:09 pm    Post subject:
Reply with quote

I want to remove it in sort.
Back to top
View user's profile Send private message
vasanthz

Global Moderator


Joined: 28 Aug 2007
Posts: 1445
Location: Azeroth

PostPosted: Tue Jan 03, 2012 4:28 pm    Post subject:
Reply with quote

What SORT product you have and the release level?
LRECL, DSORG of files?
Back to top
View user's profile Send private message
jemonthomas

New User


Joined: 24 Jul 2006
Posts: 35
Location: india

PostPosted: Tue Jan 03, 2012 5:25 pm    Post subject: Reply to: Need to remove duplicate column wise
Reply with quote

I am using syncsort.Lrecl is 80.DSORG is sequential
Back to top
View user's profile Send private message
bodatrinadh

Active User


Joined: 05 Jan 2007
Posts: 101
Location: chennai (India)

PostPosted: Tue Jan 03, 2012 5:30 pm    Post subject: Reply to: Need to remove duplicate column wise
Reply with quote

Hai Thomas,

Here is your code. Assuming LRECL=80 and RECFM=FB...

Code:
//S1A  EXEC PGM=SORT                                                       
//SORTIN    DD *                                               
ABC THOMAS                                                     
ABC RICHARD                                                   
ABC MATHEW                                                     
ABC TOM                                                       
BCD NEENA                                                     
BCD NAMITHA                                                   
BCD NISHA                                                     
CDE ARUN                                                       
CDE NIRMAL                                                     
//SORTOUT   DD SYSOUT=*                                       
//SYSPRINT  DD SYSOUT=*                                       
//SYSIN     DD *                                               
  INREC BUILD=(1,3,X,5,10,X,16:SEQNUM,8,ZD,RESTART=(1,3))     
  SORT FIELDS=(1,3,CH,A)                                       
  OUTREC IFTHEN=(WHEN=(16,8,ZD,EQ,1),BUILD=(1,15)),     
               IFTHEN=(WHEN=(16,8,ZD,NE,1),OVERLAY=(5,10,70X))

Output:-
Code:
ABC THOMAS           
RICHARD               
MATHEW               
TOM                   
BCD NEENA             
NAMITHA               
NISHA                 
CDE ARUN             
NIRMAL       

Thanks
-3nadh


Edited to add the code tags...Anuj
Back to top
View user's profile Send private message
jemonthomas

New User


Joined: 24 Jul 2006
Posts: 35
Location: india

PostPosted: Tue Jan 03, 2012 5:50 pm    Post subject:
Reply with quote

Thanks a lot bodatrinadh
Back to top
View user's profile Send private message
socker_dad

Active User


Joined: 05 Dec 2006
Posts: 147
Location: Columbia, MO

PostPosted: Fri Feb 24, 2012 1:35 am    Post subject: continuing in this vein.....
Reply with quote

Mine is a little more complicated. here's the code:

Code:

INREC BUILD=(001:001,001,                   HEX 00 BYTE   
         002:002,007,                       CUSTOMER ID   
         009:009,001,                       HEX 00 BYTE   
         010:010,008,                       CUSTOMER DOB   
         018:018,001,                       HEX 00 BYTE   
         019:019,036,                       CUSTOMER NAME 
         055:055,001,                       HEX 00 BYTE   
         056:056,008,                       DATE           
         064:064,001,                       HEX 00 BYTE   
         065:065,004,                       SUSP REAS CODE?
         069:069,001,                       HEX 00 BYTE   
         070:070,008,                       DATE           
         078:078,001,                       HEX 00 BYTE   
         079:079,019,                       TEXT FIELD     
         098:098,001,                       HEX 00 BYTE   
         099:099,005,                       NUMBER FIELD   
         104:104,001,                       HEX 00 BYTE   
         105:105,001,                       DASH             
         106:106,001,                       HEX 00 BYTE     
         107:107,005,                       ACCOUNT NUMBER   
         113:113,001,                       HEX 00 BYTE     
         114:114,004,                       NUMBER FIELD     
         118:118,001,                       HEX 00 BYTE     
         119:119,005)                       NUMBER FIELD     

SORT FIELDS=(002,007,A,                                     
             076,002,A,                                     
             070,005,A,                                     
             114,10,A,                                       
             099,005,A),                                     
             FORMAT=CH,                                     
             SIZE=E6000                                     

OUTREC IFTHEN=(WHEN=(001,054,ZD,EQ,1),BUILD(1,123)),         
       IFTHEN=(WHEN=(001,054,ZD,NE,1),OVERLAY=(001,054,54X))


The INREC is the record layout; the Sort is the sort sequence (obviously). Where I am screwing up is the OUTREC. The HEX 00 BYTE fields must appear in each record, but where the CUSTOMER NUMBER/CUSTOMER DOB and CUSTOMER NAME are the same as the previous record, these fields should be spaces.

Help!!!
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Fri Feb 24, 2012 2:01 am    Post subject: Reply to: Need to remove duplicate column wise
Reply with quote

EDIT: Messed up, so deleted.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Fri Feb 24, 2012 3:50 am    Post subject: Reply to: Need to remove duplicate column wise
Reply with quote

I've put together an example I think you can apply.

I'm assuming that if the account number is the same, there is no need to check name/dob.

There is code to insert your "low-value", I'm not sure if it is in your input, if so, ditch the obvious. Also, I've set the value of the low-value to an asterisk, to show up on the test.

I'm assuming the input file is sorted. If not, whack your SORT statement back in place of the OPTION COPY.


Code:
//BLANKDUP EXEC PGM=SORT
//SYMNAMES DD *
LOW-VALUE-LIT,X'5C'
//SYSOUT   DD SYSOUT=*
//SYMNOUT  DD SYSOUT=*
//SORTOUT  DD SYSOUT=*
//SYSIN    DD *
  OPTION COPY
  OUTREC IFTHEN=(WHEN=INIT,
           OVERLAY(81:SEQNUM,5,ZD,RESTART=(1,5))),
         IFTHEN=(WHEN=INIT,
           OVERLAY(06:LOW-VALUE-LIT,
                   12:LOW-VALUE-LIT,
                   18:LOW-VALUE-LIT)),
         IFTHEN=(WHEN=(81,5,ZD,NE,1),
            OVERLAY=(01:5X,
                     07:5X,
                     13:5X))
//*
//SORTIN   DD *
11111 11111 11111 SOME OTHER DATA TO REMAIN INTACT
22222 22222 22222 SOME OTHER DATA TO REMAIN INTACT
22222 22222 22222 SOME OTHER DATA TO REMAIN INTACT
33333 33333 33333 SOME OTHER DATA TO REMAIN INTACT
44444 44444 44444 SOME OTHER DATA TO REMAIN INTACT
55555 55555 55555 SOME OTHER DATA TO REMAIN INTACT
55555 55555 55555 SOME OTHER DATA TO REMAIN INTACT
55555 55555 55555 SOME OTHER DATA TO REMAIN INTACT


Output is:

Code:
11111*11111*11111*SOME OTHER DATA TO REMAIN INTACT
22222*22222*22222*SOME OTHER DATA TO REMAIN INTACT
     *     *     *SOME OTHER DATA TO REMAIN INTACT
33333*33333*33333*SOME OTHER DATA TO REMAIN INTACT
44444*44444*44444*SOME OTHER DATA TO REMAIN INTACT
55555*55555*55555*SOME OTHER DATA TO REMAIN INTACT
     *     *     *SOME OTHER DATA TO REMAIN INTACT
     *     *     *SOME OTHER DATA TO REMAIN INTACT
Back to top
View user's profile Send private message
socker_dad

Active User


Joined: 05 Dec 2006
Posts: 147
Location: Columbia, MO

PostPosted: Fri Feb 24, 2012 4:33 am    Post subject:
Reply with quote

Almost got it! Of course, when we get close, they change requirements! When the key is the same as the previous record, make the first 55 bytes spaces. Not a problem, but the key is always set to 000001, even though the lowest key is 0000007 (can never be null or low values).

Could that be because there are two WHEN=INIT, statements?

The key is actually in columns 2 thru 9, so I modified the SYSIN a bit:

Code:

//SYMNAMES  DD *                                     
LOW-VALUE-LIT,X'5C'                                   
//SYSIN     DD *                                     
  OPTION COPY                                         
    OUTREC IFTHEN=(WHEN=INIT,                         
             OVERLAY(02:SEQNUM,7,ZD,RESTART=(2,7))), 
           IFTHEN=(WHEN=INIT,                         
             OVERLAY(01:LOW-VALUE-LIT,               
                     09:LOW-VALUE-LIT,               
                     18:LOW-VALUE-LIT,               
                     55:LOW-VALUE-LIT)),             
           IFTHEN=(WHEN=(02,7,ZD,NE,1),               
              OVERLAY=(01:55X))                       


This gets me this output (don't worry, the names & numbers are all fake):

Code:

*0000001*09/06/50*TAYLOR, WESLEY RAYMOND              *03/11/96
*0000001*10/25/46*BOOHER, NOAH JAMES                  *02/12/02
*0000001*11/04/45*FRISON, EUGENE                      *09/22/98
*0000001*05/21/43*GILPIN, DOLORES JANE                *01/23/98
*0000001*04/25/46*KLINGER, GLENN WILLIAM              *08/04/98
*0000001*03/01/46*SPROED, EDWARD DOUGLAS              *03/20/98
*0000001*08/01/43*CLEMO, FREDERICK LINN               *03/02/98
*0000001*08/25/39*KRETTINGER, RONALD GLEN             *05/05/98
*0000001*06/20/37*HEINRICH, CLARENCE THEODORE         *03/23/94
                                                       04/25/94
*0000001*08/09/41*TUTTLE, LYLE EDWARD                 *04/10/98
*0000001*08/09/21*BAILEY, JAMES GUY                   *02/07/94
                                                       02/02/94
*0000001*08/02/17*MILLETT, KATHLEEN NORTON            *03/06/98
*0000001*08/31/43*BENNETT, ROBERT MELVIN              *01/25/96
                                                       01/12/96
                                                       08/29/96
*0000001*05/08/44*ASLIN, STANISLAUS ELTON             *11/19/93
                                                       11/26/93
                                                       05/05/94

How do I get the key field (columns 2 - 9) to show the right values?
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Fri Feb 24, 2012 5:16 am    Post subject: Reply to: Need to remove duplicate column wise
Reply with quote

You are overwriting your key with the sequence number that is being added.

Code:
    OUTREC IFTHEN=(WHEN=INIT,                         
             OVERLAY(81:SEQNUM,7,ZD,RESTART=(2,7))),


The Column 81:, should change to the first byte after the end of your record. The technique is to temporarily "extend" the record with a sequence number, the sequence number will re-set to one on change of the specified key (yours is 2,7).

Then, later, chop off the extension as it is no longer needed. No working-storage is Sort, so use the record :-)

In my example I forgot to do the chop-off.

One way is with "BUILD", so BUILD=(1,length-of-your-original) would do it, another way is IFOUTLEN=length-of-your-original, which just hangs around the IFTHENS and tells the sort what the output record length is.

Code:
  OPTION COPY                                         
    OUTREC IFOUTLEN=length-of-your-original,
           IFTHEN=(WHEN=INIT,                         
             OVERLAY(legnth-of-your-original-plus-1:SEQNUM,7,ZD,RESTART=(2,7))),
           IFTHEN=(WHEN=INIT,                         
             OVERLAY(01:LOW-VALUE-LIT,               
                     09:LOW-VALUE-LIT,               
                     18:LOW-VALUE-LIT,               
                     55:LOW-VALUE-LIT)),             
           IFTHEN=(WHEN=(legnth-of-your-original-plus-1,7,ZD,NE,1),               
              OVERLAY=(01:55X))               


EDIT: You can merge the two IFTHEN=(WHEN=INIT together and do it in one OVERLAY, it doesn't even affect the readability much, once I'd thought longer.
Back to top
View user's profile Send private message
socker_dad

Active User


Joined: 05 Dec 2006
Posts: 147
Location: Columbia, MO

PostPosted: Fri Feb 24, 2012 5:26 am    Post subject:
Reply with quote

Sweet!

Perfect results!

Thanks for all the help.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Fri Feb 24, 2012 6:00 am    Post subject: Reply to: Need to remove duplicate column wise
Reply with quote

No problem. Don't forget to change the X'5C' :-)
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 -> JCL & VSAM All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
No new posts SQZ pipe delimited file with 1 column... Rick Silvers DFSORT/ICETOOL 2 Sun Nov 06, 2016 8:11 pm
This topic is locked: you cannot edit posts or make replies. Checking wether a string starts from ... sundaram.naveen CLIST & REXX 9 Thu Oct 06, 2016 11:56 am
No new posts Add system date inside of a PS file a... pramitdas DFSORT/ICETOOL 5 Sun Aug 28, 2016 12:06 am
No new posts Converting NULL column into NOT NULL ... Raghu navaikulam DB2 5 Sat Aug 06, 2016 3:45 pm


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