MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Comparing Dates Between Files Using DFSORT

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Job Control Language(JCL)
View previous topic :: View next topic  
Author Message
vini
Intermediate


Joined: 12 Jan 2004
Posts: 240
Topics: 48
Location: Maryland
PostPosted: Mon Mar 30, 2020 12:54 pm    Post subject: Comparing Dates Between Files Using DFSORT Reply with quote
Hi,
There are 2 Files in FB format with varying LRECLS and layout as follows:
File A
Code:

CONTRACT-ID               PIC X(08)
FILLER                    PIC X(10)
BGN-INCURRED-DT           MM/DD/YYYY
END-INCURRED-DT           MM/DD/YYYY

File B
Code:

CONTRACT-ID                PIC X(08)
FILLER                     PIC X(20)
INCURRED-DT                MM/DD/YYYY
FILLER                     PIC X(10)

Need to create File C with layout identical to File B where INCURRED-DT falls between BGN-INCURRED-DT and END-INCURRED-DT where the CONTRACT-IDs match between files.

===== File C - Desired Output ==========
Code:

CONTRACT-ID                PIC X(08)
FILLER                     PIC X(20)
INCURRED-DT                MM/DD/YYYY
FILLER                     PIC X(10)

Please let me know how to create File C using DFSORT /ICETOOL etc

Thank You!
Vini
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12367
Topics: 75
Location: San Jose
PostPosted: Mon Mar 30, 2020 3:19 pm    Post subject: Reply with quote
vini,

How many records does FILEA and FILEB contain? I am guessing that FILEA has just 1 record. is that right?

Can you provide some sample data ?
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
vini
Intermediate


Joined: 12 Jan 2004
Posts: 240
Topics: 48
Location: Maryland
PostPosted: Mon Mar 30, 2020 3:37 pm    Post subject: Reply with quote
Hi Kolusu,

FileA has approx 1000 records and FileB about 1Million records. File B is like the transaction data so each Contract ID Patend Cd combo can have multiple records in File B so the date range is the filter.
The Key is composite of Contract ID and Patient Code. I had overlooked patient code in original post.


File A
Code:
Code:

CONTRACT-ID               PIC X(08)
FILLER                    PIC X(5)
PATIENT-CD            PIC X(01)
FILLER                    PIC X(10)
BGN-INCURRED-DT           MM/DD/YYYY
END-INCURRED-DT           MM/DD/YYYY

File B
Code:
Code:

CONTRACT-ID                PIC X(08)
FILLER                    PIC X(5)
PATIENT-CD            PIC X(01)
FILLER                     PIC X(20)
INCURRED-DT                MM/DD/YYYY
FILLER                     PIC X(10)

Need to create File C with layout identical to File B where INCURRED-DT falls between BGN-INCURRED-DT and END-INCURRED-DT where the CONTRACT-IDs match between files.

===== File C - Desired Output ==========
Code:


CONTRACT-ID                PIC X(08)
FILLER                    PIC X(5)
PATIENT-CD            PIC X(01)
FILLER                     PIC X(20)
INCURRED-DT                MM/DD/YYYY
FILLER                     PIC X(10)
Back to top
View user's profile Send private message
vini
Intermediate


Joined: 12 Jan 2004
Posts: 240
Topics: 48
Location: Maryland
PostPosted: Mon Mar 30, 2020 3:50 pm    Post subject: Reply with quote
Sample Data .... the layout in original post was made up for ease the actual spacing and lengths may vary....

File A
Code:

R00113078       A     06/27/2017      04/09/2019
R00436436       C     01/23/2015      04/08/2016


File B
Code:
                                                 
R00113078       A     02/22/2015                 
R00113078       A     01/09/2016                 
R00113078       A     06/28/2017                 
R00113078       A     07/22/2018                 
R00436436       C     01/12/2014                 
R00436436       C     11/12/2015                 
R00436436       C     12/12/2015                 
R00436436       C     03/27/2016                 


File C
Code:
                                                 
R00113078       A     06/28/2017                 
R00113078       A     07/22/2018                 
R00436436       C     11/12/2015                 
R00436436       C     12/12/2015                 
R00436436       C     03/27/2016
Back to top
View user's profile Send private message
vini
Intermediate


Joined: 12 Jan 2004
Posts: 240
Topics: 48
Location: Maryland
PostPosted: Mon Mar 30, 2020 3:55 pm    Post subject: Reply with quote
Please ignore the numbers in column 1 to 6 of sample data its of no significance.
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12367
Topics: 75
Location: San Jose
PostPosted: Mon Mar 30, 2020 4:38 pm    Post subject: Reply with quote
You need to do a better job of explaining the requirements. I assumed that your matching key in both files is the Contract-id and patient-cd.

The following JCL will give you the desired results.

1. I used the large input file as INA for optimal performance and it has the following layout
Code:

LRECL=80

CONTRACT-ID                PIC X(09)
FILLER                     PIC X(5)
PATIENT-CD                 PIC X(01)
FILLER                     PIC X(20)
INCURRED-DT                MM/DD/YYYY
FILLER                     PIC X(10)
FILLER                     PIC X(25)



2. I used the small input file as INB and it has the following layout
Code:

CONTRACT-ID               PIC X(09)
FILLER                    PIC X(5)
PATIENT-CD                PIC X(01)
FILLER                    PIC X(10)
BGN-INCURRED-DT           MM/DD/YYYY
END-INCURRED-DT           MM/DD/YYYY


For INB file, we build a record using JNF2CNTL with only fields that are required which are
Code:

Contract-id  PIC X(09)
Patient-cd   PIC X(01)
Begin-date   PIC 9(08) as CCYYMMDD
Filler       PIC X.
End-date     PIC 9(08) as CCYYMMDD


One we have the matched records now, we perform 2 calculations.

1. Incur-date minus Begin-date. This should be a positive value if the incur-date >= begin-date
2. End-date minus Incur-date. This should be positive if the incur-date <= End date

Now using OUTFIL we check for date difference >=0 and write out the first 80 bytes that we need

Code:

//STEP0100 EXEC PGM=SORT                                     
//SYSOUT   DD SYSOUT=*                                       
//INA      DD *                                               
----+----1----+----2----+----3----+----4----+----5----+----6--
R00113078     A                    02/22/2015                 
R00113078     A                    01/09/2016                 
R00113078     A                    06/28/2017                 
R00113078     A                    07/22/2018                 
R00436436     C                    01/12/2014                 
R00436436     C                    11/12/2015                 
R00436436     C                    12/12/2015                 
R00436436     C                    03/27/2016                 
//INB      DD *                                               
R00113078     A          06/27/201704/09/2019                 
R00436436     C          01/23/201504/08/2016                 
//SORTOUT  DD SYSOUT=*                                       
//SYSIN    DD *                                               
  OPTION COPY                                                 
  JOINKEYS F1=INA,FIELDS=(01,09,A,   # Contract-id           
                          15,01,A)   # Patient-cd             
                                                             
  JOINKEYS F2=INB,FIELDS=(01,09,A,   # Contract-id           
                          10,01,A)   # Patient-cd             
                                                             
  REFORMAT FIELDS=(F1:01,80,                                 
                   F2:11,17)                                 
                                                             
  INREC OVERLAY=(099:42,04,          # Incur-Year             
                     36,02,          # Incur-Month           
                     39,02,          # Incur-Day             
                                                             
                 110:99,8,Y4T,       # Incr-Date             
                     DATEDIFF,       # Minus                 
                     81,8,Y4T,       # Begin-Date             
                                                             
                 120:90,8,Y4T,       # End-Date               
                     DATEDIFF,       # Minus                 
                     99,8,Y4T)       # Incur-Date             
                                                             
  OUTFIL BUILD=(1,80),                                       
       INCLUDE=(110,8,SFF,GE,0,AND,  # Begin Diff >=0 and     
                120,8,SFF,GE,0)      # end diff >=0           
/*                                                           
//JNF2CNTL DD *                                               
  INREC BUILD=(01,09,                # Contract-id           
               15,01,                # Patient-cd             
               32,04,                # Begin-incr-year       
               26,02,                # Begin-incr-Month       
               29,02,                # Begin-incr-Day         
               X,                    # Space                 
               42,04,                # End-Incr-Year         
               36,02,                # End-Incr-Month         
               39,02)                # End-Incr-Day           
/*


The output from this job is

Code:

R00113078     A                    06/28/2017   
R00113078     A                    07/22/2018   
R00436436     C                    11/12/2015   
R00436436     C                    12/12/2015   
R00436436     C                    03/27/2016   

_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
vini
Intermediate


Joined: 12 Jan 2004
Posts: 240
Topics: 48
Location: Maryland
PostPosted: Mon Mar 30, 2020 10:12 pm    Post subject: Reply with quote
Hi Kolusu,

My sincere apologies for the less than ideal stating of requirements...

Ran the job with positions changed per what I understood/guessed but it did not pair any records.
That must be owing to me not quite following from the INREC OVERLAY part onwards ... what are positions 099, 110 and 120 ?
Am now posting the actual layouts of both FILEA and FILEB because am unable to follow my apologies for same and getting no paired records and hence nothing in output.

INA , LRECL = 81
Code:
FILLER                  PIC X(04)
CONTRACT-ID       PIC X(09)
FILLER                  PIC X(01)
PATIENT-CD          PIC X(01)
FILLER                  PIC X(45)
INCURRED-DT       PIC X(10)
FILLER                  PIC X(11)

-----------------------------------------------
INB, LRECL = 150
Code:
CONTRACT-ID        PIC X(09)
FILLER                  PIC X(07)
PATIENT-CD          PIC X(01)
FILLER                  PIC X(05)
BGN-INC-DT          PIC X(10)
FILLER                  PIC X(06)
END-INC-DT          PC  X(10)
FILLER                  PIC X(102)

Really appreciate the help!!!
Vini
Back to top
View user's profile Send private message
vini
Intermediate


Joined: 12 Jan 2004
Posts: 240
Topics: 48
Location: Maryland
PostPosted: Mon Mar 30, 2020 10:39 pm    Post subject: Reply with quote
Kolusu - Here is the step I added to an existing job and ran but got no paired records....
Code:
//DH99ST11 EXEC PGM=SORT
//SYSOUT   DD SYSOUT=*
//INA      DD DSN=DH20062.PAID.EXCEL.PT5,DISP=SHR
//INB      DD DSN=DH20062.INPUT,DISP=SHR
//SORTOUT  DD  DSN=DH20062.PAID.EXCEL.PT6,
//             DISP=(NEW,CATLG,DELETE),
//             UNIT=DISK,SPACE=(TRK,(9999,999),RLSE)
//SYSIN    DD *
  OPTION COPY
  JOINKEYS F1=INA,FIELDS=(05,09,A,   # CONTRACT-ID
                          15,01,A)   # PATIENT-CD
  JOINKEYS F2=INB,FIELDS=(01,09,A,   # CONTRACT-ID
                          17,01,A)   # PATIENT-CD
  REFORMAT FIELDS=(F1:01,81,
                   F2:11,17)
  INREC OVERLAY=(100:42,04,          # INCUR-YEAR
                       36,02,          # INCUR-MONTH
                       39,02,          # INCUR-DAY
                   111:100,8,Y4T,       # INCR-DATE
                       DATEDIFF,       # MINUS
                       82,8,Y4T,       # BEGIN-DATE
                   121:91,8,Y4T,       # END-DATE
                       DATEDIFF,       # MINUS
                      100,8,Y4T)       # INCUR-DATE
  OUTFIL BUILD=(1,81),
       INCLUDE=(111,8,SFF,GE,0,AND,  # BEGIN DIFF >=0 AND
                121,8,SFF,GE,0)      # END DIFF >=0
/*
//JNF2CNTL DD *
  INREC BUILD=(01,09,                # CONTRACT-ID
               17,01,                # PATIENT-CD
               29,04,                # BEGIN-INCR-YEAR
               23,02,                # BEGIN-INCR-MONTH
               26,02,                # BEGIN-INCR-DAY
               X,                    # SPACE
               45,04,                # END-INCR-YEAR
               39,02,                # END-INCR-MONTH
               42,02)                # END-INCR-DAY
/*
//
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12367
Topics: 75
Location: San Jose
PostPosted: Mon Mar 30, 2020 11:39 pm    Post subject: Reply with quote
Vini,

You have been on this board for more than a decade and you should by now know the rules or how to post a question. You are just wasting my time as well as your time.

Your rebuilt INB using INREC in JNF2CNTL so your patient id is now at position 10 and not at position 17. Secondly on the main task INREC you did not change the positions of INCUR date. Luckily the JOIN did not find any matching records and you never got to the INREC part of it.

Here are the updated control cards. I also added a check for invalid dates. You really need to understand the job. One way to understand the job is removing the OUTFIL statement and check the results. it will show you how the dates are added and how the date difference is calculated.


Code:

//SYSIN    DD *                                               
  OPTION COPY                                                 
  JOINKEYS F1=INA,FIELDS=(05,09,A,    # Contract-id           
                          15,01,A)    # Patient-cd             
                                                               
  JOINKEYS F2=INB,FIELDS=(01,09,A,    # Contract-id           
                          10,01,A)    # Patient-cd             
                                                               
  REFORMAT FIELDS=(F1:01,81,          # 81 byte from INA   
                   F2:11,17)          # Begin and End dates                                 
                                                               
  INREC OVERLAY=(100:67,04,           # Incur-Year             
                     61,02,           # Incur-Month           
                     64,02,           # Incur-Day             
                                                               
                 110:100,8,Y4T,       # Incr-Date             
                     DATEDIFF,        # Minus                 
                     82,8,Y4T,        # Begin-Date             
                                                               
                 120:91,8,Y4T,        # End-Date               
                     DATEDIFF,        # Minus                 
                     100,8,Y4T,       # Incur-Date             
                                                               
                 110:110,1,                                   
                     CHANGE=(1,C'*',  # Invalid date diff     
                               C'-'), # set a negative diff   
                    NOMATCH=(110,1),  # if good then as is     
                                                               
                 120:120,1,                                   
                     CHANGE=(1,C'*',  # Invalid date diff     
                               C'-'), # set a negative diff   
                    NOMATCH=(120,1))  # if good then as is     
                                                               
  OUTFIL BUILD=(1,81),                                         
       INCLUDE=(110,8,SFF,GE,0,AND,  # Begin Diff >=0 and     
                120,8,SFF,GE,0)      # end diff >=0           
/*                                                             
//JNF2CNTL DD *                                                 
  INREC BUILD=(01,09,                # Contract-id               
               17,01,                # Patient-cd               
               29,04,                # Begin-incr-year           
               23,02,                # Begin-incr-Month         
               26,02,                # Begin-incr-Day           
               X,                    # Space                     
               45,04,                # End-Incr-Year             
               39,02,                # End-Incr-Month           
               42,02)                # End-Incr-Day             
/*


In order to check how the invalid date check works, create an invalid date record in your file INA & INB. February 30 is an invalid date

Code:

abcdefghi     C                    02/30/2016                                                 


INB
Code:

abcdefghi     C          01/23/201504/08/2016     

_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
vini
Intermediate


Joined: 12 Jan 2004
Posts: 240
Topics: 48
Location: Maryland
PostPosted: Tue Mar 31, 2020 11:20 am    Post subject: Reply with quote
Apologies one more time for not following all the rules of this board around posting questions.

Appreciate all the help and kindness shown especially in these stressful times with the ongoing COVID pandemic ...

Stay inside stay safe everyone!
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Job Control Language(JCL) All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group