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
 

 

Extract matching and non matching recs using SORT
Goto page 1, 2  Next
 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> SYNCSORT
View previous topic :: :: View next topic  
Author Message
maki_psg

New User


Joined: 28 Jan 2010
Posts: 47
Location: India

PostPosted: Mon Jun 10, 2013 9:00 am    Post subject: Extract matching and non matching recs using SORT
Reply with quote

Hi,

I have a requirement to extract the data from 2 files with matching keys from one file (File 2) and non matching keys from both the files (File 1 and File 2).

Data from file 1:

Code:

A 101
C <spaces>
B 102
E 105
D 104


Data from File 2:

Code:

P 301
A 200
Q 302
B 101
D 109
Z 901


I would like to extract the rec from File 2 if there is a match on Col1 and also extract non matching recs from both the files.

Sample output file:
Code:

A 200
B 101
C <spaces>
D 109
E 105
P 301
Q 302
Z 901


Please help to extract the above output using SORT.
Thanks.
Back to top
View user's profile Send private message

Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2158
Location: @my desk

PostPosted: Mon Jun 10, 2013 10:16 am    Post subject:
Reply with quote

maki_psg,

I would suggest "Search"ing the DFSORT forum , I am sure you will find a lot of working examples for your requirement.

Try something out and get back if you face any issues with that. Someone will be around to help you.
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Mon Jun 10, 2013 11:05 am    Post subject:
Reply with quote

Are you using SYNCSORT OR DFSORT?
Back to top
View user's profile Send private message
mistah kurtz

Active User


Joined: 28 Jan 2012
Posts: 268
Location: Room: TREE(3). Hilbert's Hotel

PostPosted: Mon Jun 10, 2013 11:20 am    Post subject:
Reply with quote

Assuming both your files are FB and LRECL=80..and matching key is the first column in both the files F1 and F2..
you can try this DFSORT job.
Code:

//SRT01    EXEC PGM=SORT                                             
//SYSOUT   DD SYSOUT=*                                               
//SORTJNF1 DD *                                                     
A 101                                                               
C <SPACES>                                                           
B 102                                                               
E 105                                                               
D 104                                                               
//SORTJNF2 DD *                                                     
P 301                                                               
A 200                                                               
Q 302                                                               
B 101                                                               
D 109                                                               
Z 901                                                               
//*                                                                 
//SORTOUT  DD SYSOUT=*                                               
//*                                                                 
//SYSIN    DD *                                           
  JOINKEYS FILE=F1,FIELDS=(1,1,A)                         
  JOINKEYS FILE=F2,FIELDS=(1,1,A)                         
  JOIN     UNPAIRED,F1,F2                                 
  OPTION   COPY                                           
  REFORMAT FIELDS=(F1:1,80,F2:1,80,?)                     
  INREC    IFTHEN=(WHEN=(161,1,CH,EQ,C'1'),BUILD=(1,80)), 
           IFTHEN=(WHEN=(161,1,SS,EQ,C'B,2'),BUILD=(81,80))


Output:
Code:

A 200     
B 101     
C <SPACES>
D 109     
E 105     
P 301     
Q 302     
Z 901     
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Mon Jun 10, 2013 11:34 am    Post subject: Reply to: Extract matching and non matching recs using SORT
Reply with quote

mistah kurtz,

The matching marker can only have one of three values. IFTHEN=(WHEN=NONE will give you an "ELSE", which will make it clearer, rather than have the next person along wondering what values are not being catered for.

maki_psg,

Are your input files in sequence on the first byte? Are duplicates possible on either file?

Your volumes must be small, so you'd probably not notice, but you might want to look at using MERGE.

Specify your second file as the first input to the MERGE and use OPTION EQUAL and SUM FIELDS=NONE.

EDIT: I can see from your sample data that your files are not in sequence. Forget MERGE.
Back to top
View user's profile Send private message
mistah kurtz

Active User


Joined: 28 Jan 2012
Posts: 268
Location: Room: TREE(3). Hilbert's Hotel

PostPosted: Mon Jun 10, 2013 1:06 pm    Post subject:
Reply with quote

Thanks Bill.. I have modified it as below:

Code:
//SYSIN    DD *                                         
  JOINKEYS FILE=F1,FIELDS=(1,1,A)                       
  JOINKEYS FILE=F2,FIELDS=(1,1,A)                       
  JOIN     UNPAIRED,F1,F2                               
  OPTION   COPY                                         
  REFORMAT FIELDS=(F1:1,80,F2:1,80,?)                   
  INREC    IFTHEN=(WHEN=(161,1,CH,EQ,C'1'),BUILD=(1,80)),
           IFTHEN=(WHEN=NONE,BUILD=(81,80))             
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Mon Jun 10, 2013 10:13 pm    Post subject:
Reply with quote

mistah kurtz,

Good Job. I would like to tidy up a little bit more.

You don't have to specify F1 and F2 on the JOIN statement. A simple JOIN UNPAIRED will give you matched as well unmatched records from both F1 and F2.

You also don't need to build the record once again when the indicator is '1' as the data is already present in positions 1 thru 80. You can use IFOUTLEN=80, to make sure we got the right LRECL for output.

so use the following control cards
Code:

//SYSIN    DD *                                                     
  JOINKEYS FILE=F1,FIELDS=(1,1,A)                                   
  JOINKEYS FILE=F2,FIELDS=(1,1,A)                                   
  JOIN UNPAIRED                                                     
  OPTION COPY                                                       
  REFORMAT FIELDS=(F1:1,80,F2:1,80,?)                               
  INREC IFOUTLEN=80,IFTHEN=(WHEN=(161,1,SS,EQ,C'B,2'),BUILD=(81,80))
//*
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Tue Jun 11, 2013 2:21 am    Post subject: Reply to: Extract matching and non matching recs using SORT
Reply with quote

If the volumes were large, there might be advantages to rearranging things.

If most of the records were "matches" with only a few "mismatches" then F1 and F2 could be "swapped", so that most of the input data required for output are in the first part of the REFORMAT record, and processed simply by the IFOUTLEN. This goes for whatever combination gives the preponderance of records. If should be the first part of the REFORMAT record.

I know there is a thing about "negation", but with only three possibilities, NE,C'1' might do :-)

Particularly with symbols...
Back to top
View user's profile Send private message
mistah kurtz

Active User


Joined: 28 Jan 2012
Posts: 268
Location: Room: TREE(3). Hilbert's Hotel

PostPosted: Tue Jun 11, 2013 9:58 am    Post subject:
Reply with quote

Thanks Skolusu and Bill..
I guess OP has forgotten that he/she posted something here :-)
Back to top
View user's profile Send private message
maki_psg

New User


Joined: 28 Jan 2010
Posts: 47
Location: India

PostPosted: Tue Jun 11, 2013 10:40 am    Post subject:
Reply with quote

Skolusu wrote:
mistah kurtz,

Good Job. I would like to tidy up a little bit more.

You don't have to specify F1 and F2 on the JOIN statement. A simple JOIN UNPAIRED will give you matched as well unmatched records from both F1 and F2.

You also don't need to build the record once again when the indicator is '1' as the data is already present in positions 1 thru 80. You can use IFOUTLEN=80, to make sure we got the right LRECL for output.

so use the following control cards
Code:

//SYSIN    DD *                                                     
  JOINKEYS FILE=F1,FIELDS=(1,1,A)                                   
  JOINKEYS FILE=F2,FIELDS=(1,1,A)                                   
  JOIN UNPAIRED                                                     
  OPTION COPY                                                       
  REFORMAT FIELDS=(F1:1,80,F2:1,80,?)                               
  INREC IFOUTLEN=80,IFTHEN=(WHEN=(161,1,SS,EQ,C'B,2'),BUILD=(81,80))
//*


@ Pandora, We are using SYNCSORT.

@ Skolusu, the input file volume is huge. I had posted a sample data, which was similar to the prod scenario.

Thank you mistah kurtz, Skolusu and Bill.

I have used the below sort card and it works perfectly.
Code:

//SYSIN    DD *                                                     
  JOINKEYS FILE=F1,FIELDS=(1,1,A)                                   
  JOINKEYS FILE=F2,FIELDS=(1,1,A)                                   
  JOIN UNPAIRED                                                     
  OPTION COPY                                                       
  REFORMAT FIELDS=(F1:1,80,F2:1,80,?)                               
  INREC IFOUTLEN=80,IFTHEN=(WHEN=(161,1,SS,EQ,C'B,2'),BUILD=(81,80))
//*                                                                 
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2158
Location: @my desk

PostPosted: Tue Jun 11, 2013 10:53 am    Post subject:
Reply with quote

maki_psg wrote:
@ Pandora, We are using SYNCSORT.
maki_psg

Please post queries related to Syncsort in the JCL part of this forum in future. All the DFSORT keywords may not work well in Syncsort and vice versa, though for the most part, both will work the same way.

btw, Could you tell your Syncsort version, my manual does not say anything about the '?' support. icon_confused.gif
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Tue Jun 11, 2013 11:23 am    Post subject:
Reply with quote

Code:
//S1 EXEC PGM=ICEMAN
//SYSOUT   DD SYSOUT=*
//SORTIN DD *
RECORD
/*
//SORTOUT DD DUMMY
//SYSIN   DD   *
  OPTION COPY
/*



Please paste the complete sysout of the above step
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Tue Jun 11, 2013 11:53 am    Post subject: Reply to: Extract matching and non matching recs using SORT
Reply with quote

1.4.1.0 has undocumented support for JNFnCNTL files. Perhaps it also has for "?"? Either that or, unusually, TS/OP thinks they have SyncSort but don't....

maki_psg,

If your data is huge, pay attention to the points already mentioned. Mock-up your huge amounts, see if you get a reasonable difference in resources used, and choose the option which best fits your data.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Tue Jun 11, 2013 12:21 pm    Post subject: Reply to: Extract matching and non matching recs using SORT
Reply with quote

maki_psg,

You have a previous topic, from early April, which you also posted in DFSORT.

Please do not do that again. SyncSort questions go in the JCL part of the forum.

From that topic you say "I am currently using SYNCSORT FOR Z/OS 1.4.1.0R." Can you confirm that the "?" works in the Control Cards that you are using, please?
Back to top
View user's profile Send private message
maki_psg

New User


Joined: 28 Jan 2010
Posts: 47
Location: India

PostPosted: Tue Jun 11, 2013 1:01 pm    Post subject: Re: Reply to: Extract matching and non matching recs using S
Reply with quote

Bill Woodger wrote:
maki_psg,

You have a previous topic, from early April, which you also posted in DFSORT.

Please do not do that again. SyncSort questions go in the JCL part of the forum.

From that topic you say "I am currently using SYNCSORT FOR Z/OS 1.4.1.0R." Can you confirm that the "?" works in the Control Cards that you are using, please?


Bill, apologies for posting the topic incorrectly icon_sad.gif.

Yes, "?" works in the control cards which were posted at the top.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Tue Jun 11, 2013 1:05 pm    Post subject: Reply to: Extract matching and non matching recs using SORT
Reply with quote

OK. Thanks.

So, with at least 1.4.1.0R, SyncSorters can use the ? in the REFORMAT statement :-)
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2158
Location: @my desk

PostPosted: Thu Jun 13, 2013 11:09 am    Post subject: Re: Reply to: Extract matching and non matching recs using S
Reply with quote

Bill Woodger wrote:
OK. Thanks.

So, with at least 1.4.1.0R, SyncSorters can use the ? in the REFORMAT statement :-)
Thats good news, Bill icon_smile.gif , Not sure why the 1.4 documentation does not mention about this enhancement. icon_eek.gif
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Thu Jun 13, 2013 12:08 pm    Post subject:
Reply with quote

Is there no SYNCSORT Developer in forum thats sad :'(

A person to share us new logic and commands of the latest version will come in handy for all users
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Thu Jun 13, 2013 12:42 pm    Post subject: Reply to: Extract matching and non matching recs using SORT
Reply with quote

SyncSort drop in from time-to-time.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Jun 13, 2013 7:05 pm    Post subject:
Reply with quote

Hello,

If your organization is registered with Syncsort, they send informational e-mails with some regularity . . . Make sure that whoever recieves them sends them to those who use Syncsort.
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 -> SYNCSORT All times are GMT + 6 Hours
Goto page 1, 2  Next
Page 1 of 2

 

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 How to convert the VBM file to VB or... Sulabh Agrawal JCL & VSAM 4 Fri Nov 18, 2016 1:04 pm
No new posts For each given key value, how to keep... Div Grad DFSORT/ICETOOL 7 Fri Nov 11, 2016 11:38 pm
No new posts Matching with Key at different postions. rajatbagga DFSORT/ICETOOL 12 Wed Nov 09, 2016 10:58 am
No new posts Sort records based on numeric field. Alks SYNCSORT 2 Wed Oct 19, 2016 10:14 pm


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