IBM Mainframe Forum

by **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.

by **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.

by **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.

Select all

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

Select all

=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:

Select all

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:

Select all

0031121690000200F

(the rate would be blank).

I hope I am explaining this clearly enough. If not please let me know. Thanks for your assistance!

by **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

Select all

//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

Select all

0031121590000100A1.0765
0031121690000200F
0031121890000400A0.7889
0031121990000500A0.8951
0031122190000700A0.8628

by **D9988** » Tue Aug 25, 2009 10:27 pm

This is working perfectly. As always, thanks for the help! I really appreciate it

IBM Mainframe Forum

Help with merging 2 files into 1

Help with merging 2 files into 1

Re: Help with merging 2 files into 1

Re: Help with merging 2 files into 1

Re: Help with merging 2 files into 1

Re: Help with merging 2 files into 1