IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

One to many in sas/syncsort


IBM Mainframe Forums -> JCL & VSAM
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
shankarm

Active User


Joined: 17 May 2010
Posts: 175
Location: India

PostPosted: Tue Sep 17, 2013 9:01 pm
Reply with quote

Hello,

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.
Back to top
View user's profile Send private message
superk

Global Moderator


Joined: 26 Apr 2004
Posts: 4652
Location: Raleigh, NC, USA

PostPosted: Tue Sep 17, 2013 9:17 pm
Reply with quote

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.”)
Back to top
View user's profile Send private message
shankarm

Active User


Joined: 17 May 2010
Posts: 175
Location: India

PostPosted: Tue Sep 17, 2013 9:34 pm
Reply with quote

Thanks Kevin. Currently, am trying the below using icetool. I will update the forum if i find the solution.

Code:

//DFSMSG DD SYSOUT=*                   
//TOOLIN DD *                         
  SORT JKFROM TO(OUT) USING(CTL1)     
/*                                     
//CTL1CNTL DD *                       
  JOINKEYS F1=JNA,FIELDS=(1,1,A)       
  JOINKEYS F2=JNB,FIELDS=(1,1,A),SORTED
  REFORMAT FIELDS=(F2:1,1,F1:2,1)     
  OPTION EQUALS                       
  SORT FIELDS=COPY                     
/*                                     
Back to top
View user's profile Send private message
mistah kurtz

Active User


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

PostPosted: Wed Sep 18, 2013 12:02 pm
Reply with quote

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                             


Output: F3
Code:
A 123
B 123
B 123
B 456
B 456
C 123
C 456
Back to top
View user's profile Send private message
shankarm

Active User


Joined: 17 May 2010
Posts: 175
Location: India

PostPosted: Wed Sep 18, 2013 4:58 pm
Reply with quote

Great. This works fine. Thanks for your help.

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?
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Wed Sep 18, 2013 6:13 pm
Reply with quote

Possible in on step.

You need JOIN UNMATCHED,F1

You need another OUTFIL

You'll need a REFORMAT statement with your entire F1 and one particular byte from F2

You need, since it is SyncSort, to be able to test something on the REFORMAT record to know that you have the mismatch.

Easiest is if your key cannot contain space, for instance, as it is the defaul FILL character.

Otherwise find a byte which can never contain a particular value and set the FILL on the REFORMAT to that value.

On your first OUTFIL, OMIT= when your selected byte is equal to the value of the FILL character.

On your second OUTFIL, specify SAVE.

All your matched records will go to the first OUTFIL and everything else (just unmatched F1s as specified in the JOIN) will go to your second OUTFIL.
Back to top
View user's profile Send private message
mistah kurtz

Active User


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

PostPosted: Wed Sep 18, 2013 6:46 pm
Reply with quote

Try the below job:

Here I'm using '?' as a default FILL character to idetnify which record is coming from which file.

Code:
//STEP01   EXEC PGM=SORT                                       
//SORTJNF1 DD *                                               
A 123                                                         
B 123                                                         
B 456                                                         
C 123                                                         
C 456                                                         
D 123                                                         
//SORTJNF2 DD *                                               
A                                                             
B                                                             
B                                                             
C                                                             
E                                                             
//F1ONLY   DD SYSOUT=*                                         
//F2ONLY   DD SYSOUT=*                                         
//COMMON   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             
    JOIN     UNPAIRED                                         
    REFORMAT FIELDS=(F1:1,5,F2:1,1),FILL=C'?'                 
    OPTION   COPY                                             
    OUTFIL   FNAMES=F1ONLY,INCLUDE=(6,1,CH,EQ,C'?'),BUILD=(1,5)
    OUTFIL   FNAMES=F2ONLY,INCLUDE=(1,1,CH,EQ,C'?'),BUILD=(6,1)
    OUTFIL   FNAMES=COMMON,SAVE,BUILD=(1,5)                   


Output:
Code:
F1ONLY:
D 123

F2ONLY:
E

COMMON:
A 123
B 123
B 123
B 456
B 456
C 123
C 456
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> JCL & VSAM

 


Similar Topics
Topic Forum Replies
No new posts Compare only first records of the fil... SYNCSORT 7
No new posts Count Records with a crietaria in a f... DFSORT/ICETOOL 5
No new posts DFSORT/SYNCSORT/ICETOOL JCL & VSAM 8
No new posts Syncsort "Y2C" Function SYNCSORT 1
No new posts Arithmetic division using Syncsort SYNCSORT 6
Search our Forums:

Back to Top