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
 

 

One to many in sas/syncsort

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM
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    Post subject: One to many in sas/syncsort
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

Moderator Team Head


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

PostPosted: Tue Sep 17, 2013 9:17 pm    Post subject: Reply to: One to many in sas/syncsort
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    Post subject:
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: 268
Location: Room: TREE(3). Hilbert's Hotel

PostPosted: Wed Sep 18, 2013 12:02 pm    Post subject:
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    Post subject:
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

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7232

PostPosted: Wed Sep 18, 2013 6:13 pm    Post subject: Reply to: One to many in sas/syncsort
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: 268
Location: Room: TREE(3). Hilbert's Hotel

PostPosted: Wed Sep 18, 2013 6:46 pm    Post subject:
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    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 syncsort: copy lines after the keyword shreya19 SYNCSORT 7 Fri Dec 02, 2016 9:47 am
No new posts Syncsort - Hard coded Value in output PORYES SYNCSORT 1 Thu Nov 03, 2016 9:23 am
No new posts Syncsort - NULL in Integer field chec... nartcr SYNCSORT 4 Thu Oct 06, 2016 6:47 am
No new posts ICETOOL/SYNCSORT - SYSPRINT output nartcr DFSORT/ICETOOL 17 Wed Oct 05, 2016 10:46 pm
No new posts Syncsort version supporting FINDREP c... juares castro SYNCSORT 9 Tue Aug 09, 2016 6:54 pm


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