How to produce a report comparing the earliest date in one file to a date in another file with matching keys?
Article ID: 54092
Updated On:
Products
Issue/Introduction
I have two files defined to Ramis with matching keys. Each file has a date field. The first file has multiple records for each key. The second file has one record for each key. In the real world, these files might be a transaction detail file with more than 1 transaction record for each account, and a master file with just one record per account. How to compare the earliest date field value for each key from the first file to the date value from the second file where the keys match?
Environment
Release: 7.4
Resolution
Ramis RELATE can be used to match the two files. The summary operator "MIN field WITHIN domain" can be used to retrieve the "earliest" or lowest value (MIN) of the field for each key in the first file. Using RELATE, keep the INTERSECTION to obtain the data from both files matched on the key field. Then, in a DEFINE, compare the date fields and produce the report with a TABLE request.
Here is a set of Ramis statements to test the technique. This test creates the file descriptions for two test QSAM files, shows the RELATE and the DEFINE and TABLE syntax. In this example, since the date field format is YYYYMMDD, the compare is easy and there is no need to use any DATE functions to convert the values for comparison.
First, to run this test, create two QSAM files with record length 80 with some test data.
Here is IEBGENER JCL to create the two test files and test data:
//* Create test QSAM file FILE1. //* The key is positions 1-4 and the date is 5-12. //FILE1 EXEC PGM=IEBGENER //SYSIN DD DUMMY //SYSPRINT DD SYSOUT=* //SYSUT2 DD DSN=file1, // DCB=(LRECL=80,RECFM=FB,BLKSIZE=0), // DISP=NEW,CATLG,DELETE), // SPACE=(TRK,(1,1)),UNIT=SYSDA //SYSUT1 DD * 000120080101 000220081201 000320080401 000420080601 000520081101 000620081201 /* //* Create test QSAM file FILE2. //* The key is positions 1-4 and the date is 5-12. //FILE2 EXEC PGM=IEBGENER //SYSIN DD DUMMY //SYSPRINT DD SYSOUT=* //SYSUT2 DD DSN=file2, // DCB=(LRECL=80,RECFM=FB,BLKSIZE=0), // DISP=NEW,CATLG,DELETE), // SPACE=(TRK,(1,1)),UNIT=SYSDA //SYSUT1 DD * 000120080101 000220080201 000320081201 000420081001 /*
And, here is the Ramis sample JCL and syntax to create the file descriptions and print the detail data:
//RAMIS EXEC PGM=RAMIS,REGION=4M
//STEPLIB DD DISP=SHR,DSN=your.Ramis.CAILIB
//RAMTSKLB DD DISP=SHR,DSN=your.Ramis.CAILIB
//RAMLIBE DD DISP=SHR,LABEL=(,,,IN),DSN=your.Ramis.RAMLIBE
//DATABASE DD DISP=SHR,DSN=your.Ramis.DATABASE
//FILE1 DD DISP=SHR,DSN=file1
//FILE2 DD DISP=SHR,DSN=file2
//RAMPRINT DD SYSOUT=*
//RAMSYSIN DD *
SET MODE=BRF
*
PRINT *
PRINT * First, erase the QSAM file descriptions if they exist.
PRINT *
*
ERASE*
FILE QSAMFILE1
FILE QSAMFILE2
END
*
PRINT *
PRINT * Load the two QSAM file descriptions to RAMEXTMASTER dictionary.
PRINT *
*
REVISE
FILE RAMEXTMASTER
FILETYPE=QSAM,
FILENAME=FILE1,
LEVEL=1,
NAME=KEY1,ALIAS=K1,USAGE=A4,ACTUAL=A4,$
LEVEL=2,
NAME=DATE1,ALIAS=D1,USAGE=I8,ACTUAL=A8,$
NAME=FILLER,ALIAS=F1,USAGE=A1,ACTUAL=A68,$
FILETYPE=QSAM,
FILENAME=FILE2,
LEVEL=1,
NAME=KEY2,ALIAS=K2,USAGE=A4,ACTUAL=A4,$
NAME=DATE2,ALIAS=D2,USAGE=I8,ACTUAL=A8,$
NAME=FILLER,ALIAS=F2,USAGE=A1,ACTUAL=A68,$
END
*
PRINT *
PRINT * Show the two QSAM file descriptions.
PRINT *
*
RAMEXTINDEX FILE1
RAMEXTINDEX FILE2
*
PRINT *
PRINT * Run reports to see the full content of the two files.
PRINT *
*
TABLE FILE FILE1 PRINT KEY1 AND DATE1 END
TABLE FILE FILE2 PRINT KEY2 AND DATE2 END
/*
Finally, here is the Ramis sample JCL and syntax with the technique using RELATE and reporter (DEFINE and TABLE) to compare the dates:
//RAMIS EXEC PGM=RAMIS,REGION=4M
//STEPLIB DD DISP=SHR,DSN=your.Ramis.CAILIB
//RAMTSKLB DD DISP=SHR,DSN=your.Ramis.CAILIB
//RAMLIBE DD DISP=SHR,LABEL=(,,,IN),DSN=your.Ramis.RAMLIBE
//DATABASE DD DISP=SHR,DSN=your.Ramis.DATABASE
//FILE1 DD DISP=SHR,DSN=file1
//FILE2 DD DISP=SHR,DSN=file2
//RAMPRINT DD SYSOUT=*
//RAMSYSIN DD *
SET MODE=BRF
*
PRINT *
PRINT * Now, run the RELATE to get the earliest DATE1 value for
PRINT * each key (MIN DATE1 WITHIN KEY1) from FILE1 and match
PRINT * against KEY2 in FILE2, retrieving the DATE2 value from
PRINT * FILE2 and producing the INTERSECTION.
PRINT *
*
RELATE
PROJECT FILE1 USING MIN DATE1 WITHIN KEY1 BY KEY1
PROJECT FILE2 USING DATE2 BY KEY2
KEEP INTERSECTION NAMED MATCHED
END
*
PRINT *
PRINT * Show the description of the relation named MATCHED.
PRINT *
*
? RELATE MATCHED
*
PRINT *
PRINT * Now, compare the two dates in a DEFINE.
PRINT *
*
DEFINE
FILE MATCHED
COMPARE/A6 = IF DATE1 GE DATE2 THEN '1 GE 2' ELSE '2 GT 1';
END
*
PRINT *
PRINT * And, finally produce the report.
PRINT *
PRINT *
*
TABLE FILE MATCHED PRINT KEY1 AND DATE1 AND DATE2 AND COMPARE END
/*
Now, here is the result of the above Ramis execution to create the test files:
****************************************
* RAMIS SYSTEM ACTIVITY *
* SET *
****************************************
MODE=BRF
*
* First, erase the QSAM file descriptions if they exist.
*
FILE QSAMFILE1
DM0363: Description erased for QSAMFILE1
FILE QSAMFILE2
DM0363: Description erased for QSAMFILE2
*
* Load the two QSAM file descriptions to RAMEXTMASTER dictionary.
*
FILE RAMEXTMASTER
RM0132: NUMBER OF RECORDS UPDATED= 0 INCLUDED= 6
*
* Show the two QSAM file descriptions.
*
DESCRIPTION FOR EXTERNAL FILE QSAMFILE1 PAGE 1
LEVEL OCCURS LIST FIELDNAME SYNONYM FORM FORM
----- ------ ---- --------- ------- ----- ------
1 0 1 KEY1 K1 A4 A4
2 0 2 DATE1 D1 I8 A8
3 FILLER F1 A1 A68
DM0316: MSGLEVEL option set at 0
DESCRIPTION FOR EXTERNAL FILE QSAMFILE2 PAGE 1
USAGE ACTUAL
LEVEL OCCURS LIST FIELDNAME SYNONYM FORM FORM
----- ------ ---- --------- ------- ----- ------
1 0 1 KEY2 K2 A4 A4
2 DATE2 D2 I8 A8
3 FILLER F2 A1 A68
DM0316: MSGLEVEL option set at 0
*
* Run reports to see the full content of the two files.
*
RP0808: Number of records in table= 6, lines= 6
PAGE 1
KEY1 DATE1
---- -----
0001 20080101
0002 20081201
0003 20080401
0004 20080601
0005 20081101
0006 20081201
RP0808: Number of records in table= 4, lines= 4
PAGE 1
KEY2 DATE2
---- -----
0001 20080101
0002 20080201
0003 20081201
0004 20081001
And, finally, here is the result of the Ramis execution to process the RELATE, DEFINE, and TABLE requests:
****************************************
* RAMIS SYSTEM ACTIVITY *
* SET *
****************************************
MODE=BRF
*
* Now, run the RELATE to get the earliest DATE1 value for
* each key (MIN DATE1 WITHIN KEY1) from FILE1 and match
* against KEY2 in FILE2, retrieving the DATE2 value from
* FILE2 and producing the INTERSECTION.
*
RL0808: 6 Record(s), 6 Row(s) from file QSAMFILE1
RL0808: 4 Record(s), 4 Row(s) from file QSAMFILE2
RL0807: 4 row(s) kept for RELATION named MATCHED
*
* Show the description of the relation named MATCHED.
*
DESCRIPTION OF RELATION MATCHED
ACT NUM NUMBER PERMANENT
NUM PROJECTIONS FLDS RECORDS OPTIONS STATUS FILENAME
--- ------------ ---- ------- ------------ ------ ------------
1 QSAMFILE1 3 4 WITHOUT ACTIVE
*INTER* COMBINATIONS
QSAMFILE2 DATA FROM
QSAMFILE1
QSAMFILE2
FIELDNAME ALIAS FORMAT
------------ ----- ------
KEY1 P1 A4
DATE1 P2 I8
DATE2 P3 I8
*
* Now, compare the two dates in a DEFINE.
*
FILE MATCHED
*
* And, finally produce the report.
*
RP0808: Number of records in table= 4, lines= 4
PAGE 1
KEY1 DATE1 DATE2 COMPARE
---- ----- ----- -------
0001 20080101 20080101 1 GE 2
0002 20081201 20080201 1 GE 2
0003 20080401 20081201 2 GT 1
0004 20080601 20081001 2 GT 1 Feedback
