View previous topic :: View next topic
Author
Message
shyam_shagam New User Joined: 07 Sep 2006Posts: 15 Location: india
here are two flat files. 1st file contains policy-number and some start date. 2nd file contains policy-number and end-date. The policy numbers in both files are same and no of records in both files are also same. Both the files are sorted on policy numbers in ascending order. Now I want a single file which will contain policy-number, start-date and end-date.
For ex.
1st file data 2nd file data
Pol-num Start-date Pol-num End-date
100000 2050101 100000 2061201
200000 2040203 200000 2071231
10:40:30 AM: Ravi: Expected results
Output file data
Pol-num start-date end-date
100000 2050101 2061201
200000 2040203 2071231
Back to top
Skolusu Senior Member Joined: 07 Dec 2007Posts: 2205 Location: San Jose
shyam_shagam,
Concatenate both file1 and file2 to Sortin and use the following DFSORT JCL to give you the desired results. I assumed that your Policy # starts in pos 1 for 6 bytes and the dates start in pos 8 for 7 bytes.
Code:
//STEP0100 EXEC PGM=ICEMAN
//SYSOUT DD SYSOUT=*
//SORTIN DD DSN=Your input file1,
// DISP=SHR
// DD DSN=Your input file2,
// DISP=SHR
//SORTOUT DD SYSOUT=*
//SYSIN DD *
SORT FIELDS=(01,06,CH,A)
OUTREC IFTHEN=(WHEN=INIT,
OVERLAY=(81:SEQNUM,1,ZD,RESTART=(1,6))),
IFTHEN=(WHEN=(81,1,ZD,EQ,1),
OVERLAY=(16:7C'0')),
IFTHEN=(WHEN=(81,1,ZD,EQ,2),
OVERLAY=(16:8,8,8:7C'0'))
OUTFIL REMOVECC,NODETAIL,
OUTREC=(1,23),
SECTIONS=(1,6,
TRAILER3=(1,7,
TOT=(08,7,ZD,EDIT=(TTTTTTT)),X,
TOT=(16,7,ZD,EDIT=(TTTTTTT))))
/*
Hope this helps...
Back to top
shyam_shagam New User Joined: 07 Sep 2006Posts: 15 Location: india
SKolusu,
Super thank you, Its working fine.
If possible, could you please explain line by how its working.
- Thanks
Shyam.
Back to top
Deepa.m New User Joined: 28 Apr 2005Posts: 99
also can be done using SPLICE operator
Code:
//JSTY0000 EXEC PGM=ICETOOL
//IN1 DD *
100000 2050101
200000 2040203
/*
//IN2 DD *
100000 2061201
200000 2071231
/*
//TEMP1 DD DSN=&&TEMP,UNIT=SYSDA,SPACE=(TRK,(5,5)),DISP=(MOD,PASS)
//OUT DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//TOOLMSG DD SYSOUT=*
//TOOLIN DD *
COPY FROM(IN1) TO(TEMP1)
COPY FROM(IN2) TO(TEMP1) USING(CTL1)
SPLICE FROM(TEMP1) TO(OUT) ON(1,6,CH) WITH(16,7)
/*
//CTL1CNTL DD *
OUTREC FIELDS=(1,6,9X,16:8,7,80:X)
/*
Back to top
Skolusu Senior Member Joined: 07 Dec 2007Posts: 2205 Location: San Jose
shyam_shagam wrote:
SKolusu,
Super thank you, Its working fine.
If possible, could you please explain line by how its working.
- Thanks
Shyam.
file 1 :
Code:
100000 2050101
200000 2040203
file 2 :
Code:
100000 2061201
200000 2071231
when you concatenate and sort them on the they will be as follows
Code:
100000 2050101
100000 2061201
200000 2040203
200000 2071231
Now we add a seqnum at pos 81 to number the dups
Code:
100000 2050101 1
100000 2061201 2
200000 2040203 1
200000 2071231 2
The IFTHEN statements will change the data as follows
Code:
100000 2050101 0000000
100000 0000000 2061201
200000 2071231 0000000
200000 0000000 2040203
Using DFSORT's reporting features we write out the records summing up the values in column 18 and 16 which gives the desired result.
Hope this helps...
Back to top
Please enable JavaScript!