Help with merging 2 files into 1



IBM's flagship sort product DFSORT for sorting, merging, copying, data manipulation and reporting. Includes ICETOOL and ICEGENER

Help with merging 2 files into 1

Postby D9988 » Sat Aug 15, 2009 4:25 am

Hello,

I would like to merge 2 work files together. If there is an account on work file 2 that matches the account from work file 1, I want to grab the Rate data. The resulting work file 3 would have Policy, Account, Status, and Rate. If there was no matching account on work file 2, work file 3 would be written with a blank Rate.

Work file 1 layout (input file)
Policy (n8)
Account (n8)
Status(a1)

Work file 2 layout (input file)
Account(n8)
Rate(a6)

Work file 3 layout (output file)
Policy(n8)
Account(n8)
Status(a1)
Rate(a6)

Can someone help guide me with the syntax I will need to use? I've tried different combinations of SORT and ICETOOL utilizing a mixture of SORT FIELDS, SUM FIELDS, MERGE FIELDS, INREC/OUTREC FIELDS with no luck so far.
David
D9988
 
Posts: 34
Joined: Fri Nov 09, 2007 10:55 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Help with merging 2 files into 1

Postby Frank Yaeger » Sat Aug 15, 2009 10:02 pm

Please show an example of the records in each input file (relevant fields only) and what you expect for output. Explain the "rules" for getting from input to output. Give the starting position, length and format of each relevant field. Give the RECFM and LRECL of the input files. If file1 can have duplicates within it, show that in your example. If file2 can have duplicates within it, show that in your example.
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
User avatar
Frank Yaeger
Global moderator
 
Posts: 1079
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 15 times

Re: Help with merging 2 files into 1

Postby D9988 » Fri Aug 21, 2009 2:20 am

Thanks for the response. Both workfiles will be FB. The first one will have a LRECL of 23, the 2nd one has a LRECL of 17. But these can be changed if needed to make things easier.

=COLS> ----+----1----+----2---
****** ***********************
000001 0031121590000100 1.0765
000004 0031121890000400 0.7889
000005 0031121990000500 0.8951
000006 0031122190000700 0.8628

=COLS> ----+----1----+--
****** ****************
000001 0031121590000100A
000002 0031121690000200F
000003 0031121890000400A
000004 0031121990000500A
000005 0031122190000700A


Columns 1 thru 16 (1,16) of both workfiles compose the account number.
Columns 18-23 (18,6) of work file 1 include the interest rate.
Column 17 (17,1) of work file 2 is the account status.

There will be no duplicates within either workfile. Work file 2 is the master list of all accounts. Work file 1 is the list of accounts that have had an interest rate change.

I want to write a 3rd work file that merges both files together. Work File 3 will have every record from work file 2. If there is an account number that matches on both work files 1 and 2 (the first record for example), the data would be combined into one line. The output would look like:
0031121590000100A1.0765
If there is an account on work file 2 that does not have a match in work file 1 (no new interest rate for that account), the output would look like:
0031121690000200F
(the rate would be blank).

I hope I am explaining this clearly enough. If not please let me know. Thanks for your assistance!
David
D9988
 
Posts: 34
Joined: Fri Nov 09, 2007 10:55 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Help with merging 2 files into 1

Postby skolusu » Fri Aug 21, 2009 3:57 am

D9988,

create the 2nd file also as 23 bytes but put 6 spaces in pos 18.We then concatenate both files together and use WHEN=GROUP function to get the matching records

//STEP0100 EXEC PGM=SORT
//SYSOUT   DD SYSOUT=*
//SORTIN   DD DSN=Your 23 byte file with interest rate,DISP=SHR
//         DD DSN=your 23(17+6 spaces) byte file,DISP=SHR
//SORTOUT  DD SYSOUT=*                                             
//SYSIN    DD *                                                     
  INREC IFTHEN=(WHEN=INIT,OVERLAY=(24:C'1')),                       
  IFTHEN=(WHEN=(18,6,CH,EQ,C' '),OVERLAY=(24:C'2'))
                 
  SORT FIELDS=(1,16,CH,A),EQUALS                                   

  OUTREC IFTHEN=(WHEN=INIT,OVERLAY=(26:SEQNUM,2,ZD,RESTART=(1,16))),
  IFTHEN=(WHEN=GROUP,BEGIN=(26,2,ZD,EQ,1),PUSH=(18:18,6,25:24,1))   
  OUTFIL INCLUDE=(24,2,ZD,GE,21),BUILD=(1,23)
//*


The output from this job is

0031121590000100A1.0765
0031121690000200F     
0031121890000400A0.7889
0031121990000500A0.8951
0031122190000700A0.8628
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
skolusu
 
Posts: 586
Joined: Wed Apr 02, 2008 10:38 pm
Has thanked: 0 time
Been thanked: 39 times

Re: Help with merging 2 files into 1

Postby D9988 » Tue Aug 25, 2009 10:27 pm

This is working perfectly. As always, thanks for the help! I really appreciate it :D
David
D9988
 
Posts: 34
Joined: Fri Nov 09, 2007 10:55 pm
Has thanked: 0 time
Been thanked: 0 time


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post