IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

DFSORT - Check for date and compare two files
IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic Quick References
View previous topic :: View next topic  
Author Message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
PostPosted: Mon Jun 18, 2012 11:47 am
 Reply with quote
Hi All,

I have two files of the following format

File 1 : Currency (3 bytes ), Key1(10 bytes) and date range (180 bytes) date is in format YYMMDD

File 2 : Key(10 bytes) , Date Range(180 bytes) date in format YYMMDD

the date range is basically a set of 30 dates each of 6 bytes (total 180)

My requirement is to check for a particular currency/key whether in those 180 bytes, current date and next 7 days are present in file 1, if any of these 7 days are present then check in file two if the same date is present in the 180 bytes of file2. If yes then report it as a match else report it as non matching.

For eg for currency GBP

file 1 : GBP0000000001120618120619...........
File 2 : 0000000001120618120617........

first need to check whether current date is present in file 1 in those 180 bytes, if yes then check in file 2 for the same date in 180 bytes.
If present in both files report it as present else no match.

I tried using Sub string facility in dfsort to find the current date but how do i give the date as a YYMMDD format and that too in SS ?

Is there any other way to accomplish this ?

DATE1 basically give date in YYYYMMDD format, what is the date function for YYMMDD , also how to join the two files for the above check ?

Thanks for the help in advance,
ashimer
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
PostPosted: Mon Jun 18, 2012 6:55 pm
 Reply with quote
Hello,

Suggest you post more sample input (using the "Code" tag) and the output you want when the sample input is processed (also using the Coge tag) explaining the rules that caused the output to be what you show.
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
PostPosted: Mon Jun 18, 2012 9:22 pm
 Reply with quote
Sure Dick.

My file 1 contains data in the format


Code:
GBP0000000001120618120619120620....


where GBP (3 bytes) is currency, followed by 0000000001 which is currency code (10 bytes) and then 30 dates in YYMMDD (180 bytes total).

File contains

Code:
0000000001120503120618120619120621......


where 0000000001 is currency code which is same as in file1 and then 30dates in YYMMDD (total 180 bytes). The 180 bytes of two files need not be the same.

Now i need to check whether current date 120618 is present in file1, if yes then check in file2 for the occurence of the same date for the same currency.

If present in both report as a match else no match.

In file1 120618 is from 11-17 while in file2 its in a different position. The position is not fixed in none of the records.

I tried sorting the records for all currencies for which either the current date or the date in the next 7 days are present
using

Code:
(14,6,Y2T,GE,Y'DATE1',AND,14,6,Y2T,LE,Y'DATE1'+7)

similary for all 30 dates for total 180 bytes, so basically i have an OR on the condition for

positions starting 14,20,26,32 etc. I am not sure whether there is a simple option to check for the presence of current date or current date+ 7 days in the

180 bytes, similar to SS function of string. Its not accepting Y'DATE1' as the search string - Invalid comparison.

I want the output to be GBP120618120619 as 18th and 19th is present in both files - first o/p

2 o/p should be GBP120620 as this is present in file1 but not in file2.
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose
PostPosted: Mon Jun 18, 2012 10:21 pm
 Reply with quote
ashimer,

Does the Date in any of the files re-occur in any of the 30 occurrences ? i.e 061812 is found in pos 14(slot1) , can it occur in pos 164(slot 26) ? or is the date is unique for each record?
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
PostPosted: Tue Jun 19, 2012 12:48 pm
 Reply with quote
Kolusu,
No the dates wont repeat again, dates will be unique.
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose
PostPosted: Tue Jun 19, 2012 10:55 pm
 Reply with quote
ashimer,

The following DFSORT JCL will give you the desired results. Since you are interested in just 1 week worth of data, we will replace only those dates and perform the join.

The output from this job will be

GBP+key+date1 thru date1+7 | file-2 date1 thru date1+7

Now you can split the files accordingly. You can work on that. icon_smile.gif

A brief explanation of the job.

Step0100 : This step will generate 2 FINDREP files that we will use to mask the dates and put them back again.

Step0200 : will perform the join after masking the dates.Once the desired dates are masked we remove the other dates using another findrep.

Code:

//STEP0100 EXEC PGM=SORT                                             
//SYSOUT   DD SYSOUT=*                                               
//SORTIN   DD *                                                     
DUMMY RECORD                                                         
//FREP1    DD DSN=&&FR1,DISP=(,PASS),SPACE=(TRK,(1,0),RLSE)         
//FREP2    DD DSN=&&FR2,DISP=(,PASS),SPACE=(TRK,(1,0),RLSE)         
//SYSIN    DD *                                                     
  SORT FIELDS=COPY                                                   
  INREC BUILD=(01:DATE1,11:DATE1+1,21:DATE1+2,31:DATE1+3,41:DATE1+4,
               51:DATE1+5,61:DATE1+6,71:DATE1+7)                     
                                                                     
  OUTFIL FNAMES=FREP1,                                               
  BUILD=(10:C'INOUT=(',                                             
         17:C'C''',03,6,C'''',C',C''',C'A',C'''',C',',/,             
         17:C'C''',13,6,C'''',C',C''',C'B',C'''',C',',/,             
         17:C'C''',23,6,C'''',C',C''',C'C',C'''',C',',/,             
         17:C'C''',33,6,C'''',C',C''',C'D',C'''',C',',/,             
         17:C'C''',43,6,C'''',C',C''',C'E',C'''',C',',/,             
         17:C'C''',53,6,C'''',C',C''',C'F',C'''',C',',/,             
         17:C'C''',63,6,C'''',C',C''',C'G',C'''',C',',/,             
         17:C'C''',73,6,C'''',C',C''',C'H',C'''',C'))),',80:X)       
                                                                     
  OUTFIL FNAMES=FREP2,                                               
  BUILD=(10:C'INOUT=(',                                             
         17:C'C''',C'A',C'''',C',C''',03,6,C'''',C',',/,             
         17:C'C''',C'B',C'''',C',C''',13,6,C'''',C',',/,             
         17:C'C''',C'C',C'''',C',C''',23,6,C'''',C',',/,             
         17:C'C''',C'D',C'''',C',C''',33,6,C'''',C',',/,             
         17:C'C''',C'E',C'''',C',C''',43,6,C'''',C',',/,             
         17:C'C''',C'F',C'''',C',C''',53,6,C'''',C',',/,             
         17:C'C''',C'G',C'''',C',C''',63,6,C'''',C',',/,             
         17:C'C''',C'H',C'''',C',C''',73,6,C'''',C')))',80:X)       
//*


The output from this step is

FREP1 File :
Code:

 INOUT=(C'120619',C'A',   
        C'120620',C'B',   
        C'120621',C'C',   
        C'120622',C'D',   
        C'120623',C'E',   
        C'120624',C'F',   
        C'120625',C'G',   
        C'120626',C'H'))),


FREP2 File :
Code:

INOUT=(C'A',C'120619',   
       C'B',C'120620',   
       C'C',C'120621',   
       C'D',C'120622',   
       C'E',C'120623',   
       C'F',C'120624',   
       C'G',C'120625',   
       C'H',C'120626'))) 


Code:
                                                               
//STEP0200 EXEC PGM=SORT                                             
//SYSOUT   DD SYSOUT=*                                               
//INA      DD *                                                     
GBP0000000001120619120620120621120731120622                         
//INB      DD *                                                     
0000000001120619120620120621120731120622                             
//SORTOUT  DD SYSOUT=*                                               
//SYSIN    DD *                                                     
  OPTION COPY                                                       
  JOINKEYS F1=INA,FIELDS=(4,10,A)                                   
  JOINKEYS F2=INB,FIELDS=(1,10,A)                                   
  REFORMAT FIELDS=(F1:1,21,F2:11,8)                                 
  INREC IFTHEN=(WHEN=INIT,BUILD=(1,21,40X,22,8,40X)),               
  IFTHEN=(WHEN=INIT,FINDREP=(STARTPOS=14,                           
//         DD DSN=&&FR2,DISP=SHR,VOL=REF=*.STEP0100.FREP2           
//*                                                                 
//JNF1CNTL DD *                                                     
  INREC IFTHEN=(WHEN=INIT,FINDREP=(STARTPOS=14,                     
//         DD DSN=&&FR1,DISP=SHR,VOL=REF=*.STEP0100.FREP1           
//         DD *                                                     
  IFTHEN=(WHEN=INIT,FINDREP=(STARTPOS=14,                           
            IN=(C'0',C'1',C'2',C'3',C'4',C'5',C'6',C'7',C'8',C'9'), 
           OUT=(C'')))                                               
//*                                                                 
//JNF2CNTL DD *                                                     
  INREC IFTHEN=(WHEN=INIT,FINDREP=(STARTPOS=11,                     
//         DD DSN=&&FR1,DISP=SHR,VOL=REF=*.STEP0100.FREP1           
//         DD *                                                     
  IFTHEN=(WHEN=INIT,FINDREP=(STARTPOS=11,                           
            IN=(C'0',C'1',C'2',C'3',C'4',C'5',C'6',C'7',C'8',C'9'), 
           OUT=(C'')))                                               
//*
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
PostPosted: Wed Jun 20, 2012 6:05 pm
 Reply with quote
Thanks Kolusu, I didnt get the o/p exactly as required but if you could explain be the step200 i can change it as per my needs. I frankly did not get the whole logic behind step200
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose
PostPosted: Wed Jun 20, 2012 9:34 pm
 Reply with quote
ashimer wrote:
Thanks Kolusu, I didnt get the o/p exactly as required but if you could explain be the step200 i can change it as per my needs. I frankly did not get the whole logic behind step200


Well the logic is quite simple. All you are interested is in 8 dates (current date thru current date + 7) i.e

for today Jun 20 2012 you want validate these dates in your files
Code:

120620
120621
120622
120623
120624
120625
120626
120627


We are generating 2 FINDREP control cards.

FR1 will replace all DESIRED dates (shown above ) to A,B,C,D,E,F,G,H respectively in both files i,e
Code:

120620 will become A
120621 will become B
120622 will become C
120623 will become D
120624 will become E
120625 will become F
120626 will become G
120627 will become H



And other 22 dates which we don't care will be suppressed. So now you are just left with

File 1 will have Currency (3 bytes ), Key1(10 bytes) and 8 alphabets(A-H) depending on the availability)

File 2 will have Key1(10 bytes) and 8 alphabets(A-H) depending on the availability)

Now we match on the key and create 1 single record for every matching record as follows

Code:

Match record = Currency (3 bytes ), Key1(10 bytes) and 8 alphabets from file1 (A-H) depending on the availability)

AND

8 alphabets from file2 (A-H) depending on the availability)


Now all you need to do is check if file1 dates are matching with file2 dates. (This part I left out hoping that you can manage) oh well looks like I have to layout everything.

Use the following DFSORT JCL for step0200
Code:

//STEP0200 EXEC PGM=SORT                       
//SYSOUT   DD SYSOUT=*                         
//INA      DD *                                 
GBP0000000001120619120620120621120731120622     
//INB      DD *                                 
0000000001120619120620120621120731120622120623 
//OUT1     DD SYSOUT=*                         
//OUT2     DD SYSOUT=*                         
//SYSIN    DD *                                                   
  OPTION COPY                                                     
  JOINKEYS F1=INA,FIELDS=(4,10,A)                                 
  JOINKEYS F2=INB,FIELDS=(1,10,A)                                 
  REFORMAT FIELDS=(F1:1,21,F2:11,8) 

  INREC IFOUTLEN=30,IFTHEN=(WHEN=INIT,BUILD=(1,13,41:14,16)),     
  IFTHEN=(WHEN=(41,8,SS,EQ,C'A'),OVERLAY=(14:C'A'),HIT=NEXT),     
  IFTHEN=(WHEN=(41,8,SS,EQ,C'B'),OVERLAY=(15:C'B'),HIT=NEXT),     
  IFTHEN=(WHEN=(41,8,SS,EQ,C'C'),OVERLAY=(16:C'C'),HIT=NEXT),     
  IFTHEN=(WHEN=(41,8,SS,EQ,C'D'),OVERLAY=(17:C'D'),HIT=NEXT),     
  IFTHEN=(WHEN=(41,8,SS,EQ,C'E'),OVERLAY=(18:C'E'),HIT=NEXT),     
  IFTHEN=(WHEN=(41,8,SS,EQ,C'F'),OVERLAY=(19:C'F'),HIT=NEXT),     
  IFTHEN=(WHEN=(41,8,SS,EQ,C'G'),OVERLAY=(20:C'G'),HIT=NEXT),     
  IFTHEN=(WHEN=(41,8,SS,EQ,C'H'),OVERLAY=(21:C'H'),HIT=NEXT),     
                                                                   
  IFTHEN=(WHEN=(49,8,SS,EQ,C'A',AND,14,1,CH,NE,C'A'),             
  OVERLAY=(23:C'A'),HIT=NEXT),                                     
  IFTHEN=(WHEN=(49,8,SS,EQ,C'B',AND,15,1,CH,NE,C'B'),             
  OVERLAY=(24:C'B'),HIT=NEXT),                                     
  IFTHEN=(WHEN=(49,8,SS,EQ,C'C',AND,16,1,CH,NE,C'C'),             
  OVERLAY=(25:C'C'),HIT=NEXT),                                     
  IFTHEN=(WHEN=(49,8,SS,EQ,C'D',AND,17,1,CH,NE,C'D'),             
  OVERLAY=(26:C'D'),HIT=NEXT),                                     
  IFTHEN=(WHEN=(49,8,SS,EQ,C'E',AND,18,1,CH,NE,C'E'),             
  OVERLAY=(27:C'E'),HIT=NEXT),                                     
  IFTHEN=(WHEN=(49,8,SS,EQ,C'F',AND,19,1,CH,NE,C'F'),             
  OVERLAY=(28:C'F'),HIT=NEXT),                                     
  IFTHEN=(WHEN=(49,8,SS,EQ,C'G',AND,20,1,CH,NE,C'G'),             
  OVERLAY=(29:C'G'),HIT=NEXT),                                     
  IFTHEN=(WHEN=(49,8,SS,EQ,C'H',AND,21,1,CH,NE,C'H'),             
  OVERLAY=(30:C'H'),HIT=NEXT)                                     
                             
  OUTFIL FNAMES=OUT1,INCLUDE=(14,8,CH,GT,C' '),                     
  IFOUTLEN=61,IFTHEN=(WHEN=INIT,BUILD=(1,13,14,8,JFY=(SHIFT=LEFT))),                       
  IFTHEN=(WHEN=INIT,FINDREP=(STARTPOS=14,                           
//         DD DSN=&&FR2,DISP=SHR,VOL=REF=*.STEP0100.FREP2           
//         DD *                                                     
  OUTFIL FNAMES=OUT2,INCLUDE=(23,8,CH,GT,C' '),                     
  IFOUTLEN=61,IFTHEN=(WHEN=INIT,BUILD=(1,13,23,8,JFY=(SHIFT=LEFT))),                 
  IFTHEN=(WHEN=INIT,FINDREP=(STARTPOS=14,                           
//         DD DSN=&&FR2,DISP=SHR,VOL=REF=*.STEP0100.FREP2           
//*                                                                 
//JNF1CNTL DD *                                                     
  INREC IFTHEN=(WHEN=INIT,FINDREP=(STARTPOS=14,                     
//         DD DSN=&&FR1,DISP=SHR,VOL=REF=*.STEP0100.FREP1           
//         DD *                                                     
  IFTHEN=(WHEN=INIT,FINDREP=(STARTPOS=14,                           
            IN=(C'0',C'1',C'2',C'3',C'4',C'5',C'6',C'7',C'8',C'9'), 
           OUT=(C'')))                                               
//*                                                                 
//JNF2CNTL DD *                                                     
  INREC IFTHEN=(WHEN=INIT,FINDREP=(STARTPOS=11,                     
//         DD DSN=&&FR1,DISP=SHR,VOL=REF=*.STEP0100.FREP1           
//         DD *                                                     
  IFTHEN=(WHEN=INIT,FINDREP=(STARTPOS=11,                           
            IN=(C'0',C'1',C'2',C'3',C'4',C'5',C'6',C'7',C'8',C'9'), 
           OUT=(C'')))                                               
//*


The output from this step is

OUT1 contents : ( has 3 matching dates i.e 20,21 and 22)

Code:

GBP0000000001120620120621120622



OUT2 contents : ( has 1 non-matching date i.e 23)

Code:

GBP0000000001120623
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
PostPosted: Thu Jun 21, 2012 5:05 pm
 Reply with quote
Thanks Kolusu for the detailed explanation.
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose
PostPosted: Thu Jun 21, 2012 9:50 pm
 Reply with quote
ashimer wrote:
Thanks Kolusu for the detailed explanation.


Did you get the desired results?
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
PostPosted: Fri Jun 22, 2012 1:44 pm
 Reply with quote
Yes Kolusu, in fact i went ahead with your step100 and then joined two files to produce the dates present for the currency in a single line itself, that itself proved sufficient for my requirement.

Thanks a lot !
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DFSORT/ICETOOL

 

Similar Topics
Topic Forum Replies
No new posts Replacing 'YYMMDD' with date, varying... SYNCSORT 3
No new posts Modifying Date Format Using DFSORT DFSORT/ICETOOL 9
No new posts Write line by line from two files DFSORT/ICETOOL 7
No new posts Compare only first records of the fil... SYNCSORT 7
No new posts Replace Multiple Field values to Othe... DFSORT/ICETOOL 12
Search our Forums:

Back to Top
IBMMainframes.com is not an official and/or affiliated with IBM® in anyway
Board Rules | FAQ | Downloads | Wiki | SiteMap | Contact Us