Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

DFSORT - Check for date and compare two files

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
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    Post subject: DFSORT - Check for date and compare two files
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

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Mon Jun 18, 2012 6:55 pm    Post subject:
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    Post subject:
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    Post subject:
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    Post subject: Reply to: DFSORT - Check for date and compare two files
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts High CPU consumption Job using IAM fi... aswinir JCL & VSAM 8 Thu Dec 01, 2016 8:28 pm
No new posts What is the command to check MODE of ... rohanthengal CLIST & REXX 6 Fri Nov 18, 2016 1:48 pm
No new posts TOT & DATE parameter in ICEMAN hemanthj642 DFSORT/ICETOOL 4 Mon Nov 14, 2016 5:19 am
No new posts Check System time(Minute) Using TIME1... balaji81_k DFSORT/ICETOOL 5 Fri Nov 11, 2016 10:53 am
No new posts Match or compare two files in VB Format anatol DFSORT/ICETOOL 14 Thu Nov 03, 2016 7:41 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us