In CA Ramis, how can I produce a report, comparing the earliest date in one file to a date in another file with matching keys?

Document ID : KB000054092
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

I have two files defined to CA 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 can I 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?

Solution:

CA 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 CA 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 CA 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 CA 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 CA Ramis execution to create the test files:

CA-Ramis              Release 7.4  Service Pack 3, Genlevel 0105 
COPYRIGHT (C) 1971, 2001 Computer Associates International, Inc. 
    
          **************************************** 
          *        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 CA Ramis execution to process the RELATE, DEFINE, and TABLE requests:

CA-Ramis              Release 7.4  Service Pack 3, Genlevel 0105 
COPYRIGHT (C) 1971, 2001 Computer Associates International, Inc.
                  **************************************** 
                  *        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