I have two file F1, F2. I have the match the files based on a key. If there is a match, i have to output complete F2 record + another fields from F1 into F3 file.
Both F1 and F2 files can have many duplicate keys. I have to perform an one to many matching here.
Note: F3 is the output file, F3 always should have complete F2 record + another field overlaid from F1 file.
Example:
Case1:Say F1 has a key 'A' and F2 has two occurances of 'A', Then F3 should have two records from F2 (F2 record + the field moved/overlaid from F1. Overlaid value in F3 is the same here since it is moved from the same F1 record.).
Case2:Say F1 has two occurances of the key 'A' and F2 has two occurances of key 'A'. Then F3 should have four records in the output (One to many). The overlaid value from F1 file will be same for two out of four output records and remaining two records will have the same overlaid value. Since F! file has two records with this particular key.
Case3:Say F1 has two ocurances of key 'A' and F2 has only one occurance of key 'A'. F3 will have two records but the value moved/overlaid from F1 will be different for both the output record.
Quote:
Case1:
Note: '123' is the fields to be overlaid to F3
F1.............F2.........F3
A 123........A...........A 123
................A...........A 123
Case2:
F1............F2..........F3
A 123........A...........A 123
A 456........A...........A 123
............................A 456
............................A 456
Case 3:
F1.............F2.........F3
A 123........A...........A 123
A 456.....................A 456
I tried the below SAS, it just gives me an 1 to 1 mapping not 1 to many.
Code:
DATA WORK9.ERR3;
MERGE WORK1.REC1(IN=A) WORK3.REC3(IN=B) ; BY KEY3 (points to 'A');
IF KEY7 NE ' ';
IF A AND B THEN
DO;
FILE OUTDSN3;
PUT @1 Record $CHAR541.
@542 KEY3 $CHAR.
@543 F1overlaid $CHAR3.
.
.
.
.
END;
Plese help.
If there are other solutions to this issue like syncsort, Joinkeys, overlay.. please let me know.
Joined: 26 Apr 2004 Posts: 4652 Location: Raleigh, NC, USA
I believe that the Syncsort JOINKEYS statement will give you what you want. From the manual:
Quote:
Use the JOINKEYS statement to enable join feature processing and to identify the fields used to select records for join processing. The join feature joins records from two input files that are specified on the SORTJNF1 and SORTJNF2 DD statements. By default, when the JOINKEYS fields from m records in SORTJNF1 match the JOINKEYS fields from n records in SORTJNF2, all combinations of the records are joined using the REFORMAT statement, producing m*n records as input to subsequent MFX processing. (This is called an “inner join.”)
Joined: 28 Jan 2012 Posts: 316 Location: Room: TREE(3). Hilbert's Hotel
Assuming that you have data in sorted order,
you can try something like:
Code:
//STEP01 EXEC PGM=SORT
//SORTJNF1 DD *
A 123
B 123
B 456
C 123
C 456
//SORTJNF2 DD *
A
B
B
C
//F3 DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SYSIN DD *
JOINKEYS FILE=F1,FIELDS=(1,1,A),SORTED,NOSEQCK
JOINKEYS FILE=F2,FIELDS=(1,1,A),SORTED,NOSEQCK
REFORMAT FIELDS=(F2:1,1,F1:2,4)
OPTION COPY
OUTFIL FNAMES=F3
I'm trying to write unmatched records (Present in F1 but not present in F2) to another file say F4. Is it possible to incorporate the functionality it in this step or do i have to write another step using XSUM?