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.
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.
Joined: 07 Dec 2007 Posts: 2205 Location: San Jose
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.
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 :-)
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.
Joined: 17 Oct 2006 Posts: 2454 Location: @my desk
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.
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.
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?
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 .
Yes, "?" works in the control cards which were posted at the top.
Joined: 23 Nov 2006 Posts: 19270 Location: Inside the Matrix
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.