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

Subtracting values from 2 files with identical keys
IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic Quick References
View previous topic :: View next topic  
Author Message
lvbntapasvi

New User


Joined: 20 Jul 2006
Posts: 30
PostPosted: Thu Apr 12, 2012 12:53 pm
 Reply with quote
Hello everyone,

I have a requirement wherein there are 2 files with first 1 to 10 bytes as key to the files.

Both the files will have 26 values (each in SFF form) for a single record, and if the key matches in both the files, then I need to subtract the corresponding record values, i.e. file 2-file 1, and store the subtracted values in the output file.

File 1:

Code:
1001695001 2 20120316            +7443657.8           +8143193.2         +7512275.9


File 2:

Code:
1001695001 2 20120323            +7452223.6           +8155566.0         +7403980.7


Now my output file should have the subtracted values for each column

Code:
1001695001 2 20120323               +8565.8             +20938.6         -108295.2


RECFM=FB for both the files
LRECL=589 for both the files

I have searched this forum and could find the below sort card. I have tried using this for execution of my test case, but this seems to be working only for 1 column of values.

Code:

OPTION EQUALS                                               
SORT FIELDS=(1,10,CH,A)                                     
OUTREC IFOUTLEN=589,                                         
IFTHEN=(WHEN=INIT,OVERLAY=(590:SEQNUM,1,ZD,RESTART=(1,10))),
IFTHEN=(WHEN=(590,1,ZD,EQ,2),                               
 OVERLAY=(46:46,20,ZD,MUL,-1,TO=ZD,LENGTH=20))               
 OUTFIL REMOVECC,NODETAIL,                                   
     SECTIONS=(1,21,                                         
       TRAILER3=(1,21,46:TOT=(46,20,SFF,EDIT=(SIIIIIIIIIT.T))))     



Is there any other way round to achieve this?

Thanks for your support.
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
PostPosted: Thu Apr 12, 2012 1:15 pm
 Reply with quote
If you want to match two record, then you should start with JOINKEYS.
Back to top
View user's profile Send private message
lvbntapasvi

New User


Joined: 20 Jul 2006
Posts: 30
PostPosted: Thu Apr 12, 2012 5:05 pm
 Reply with quote
Bill,

Yes, I have been using JOINKEYS, but I have never come across any subtraction function that can be used along with JOINKEYS.

Could you please suggest any reference material for the same.

I have referred to DFSORT Getting Started and Application Programming guides, but couldn't find any related points.

Thanks.
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
PostPosted: Thu Apr 12, 2012 5:24 pm
 Reply with quote
Have a look at SUB.
Back to top
View user's profile Send private message
saiprasadh

Active User


Joined: 20 Sep 2006
Posts: 154
Location: US
PostPosted: Thu Apr 12, 2012 7:36 pm
 Reply with quote
Try below mentioned sort card

Code:
//STEP030  EXEC PGM=SORT                                               
//TOOLMSG  DD  SYSOUT=*                                               
//DFSMSG   DD  SYSOUT=*                                               
//INPUT1   DD  *                                                       
1001695001 2 20120316            +7443657.8           +8143193.2       
//INPUT2   DD  *                                                       
1001695001 2 20120323            +7452223.6           +8155566.0       
//SORTOUT  DD  SYSOUT=*                                               
//SYSOUT   DD SYSOUT=*                                                 
//SYSIN    DD *                                                       
   JOINKEYS F1=INPUT1,FIELDS=(1,10,A)                                 
   JOINKEYS F2=INPUT2,FIELDS=(1,10,A)                                 
   REFORMAT FIELDS=(F1:1,80,F2:1,80)                                   
   SORT FIELDS=COPY                                                   
   OUTREC FIELDS=(1:1,33,                                             
                 34:114,10,SFF,SUB,34,10,SFF,EDIT=(SIIIIIT.T),         
                 SIGNS=(+,-),                                         
                 44:11X,                                             
                 55:135,10,SFF,SUB,55,10,SFF,EDIT=(SIIIIIT.T),       
                 SIGNS=(+,-))                                       
/*                                           


Output:

Code:
1001695001 2 20120316              +8565.8             +12372.8                 
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 577
Location: USA
PostPosted: Thu Apr 12, 2012 7:42 pm
 Reply with quote
lvbntapasvi,
Please answer below.

1) Could you possibly have duplicates for key values in any of the 2 files?
2) Are you always going to find a match?
3) Could you have orphan records from any of the 2 input files?
4) If you do find orphan records, what should be done for them? drop them or write as is?

Thanks,
Back to top
View user's profile Send private message
lvbntapasvi

New User


Joined: 20 Jul 2006
Posts: 30
PostPosted: Fri Apr 13, 2012 1:02 pm
 Reply with quote
Hi saiprasadh,

Thanks a lot. The sort card provided by you is perfectly working fine.

Thanks again.
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 Compare 2 files(F1 & F2) and writ... JCL & VSAM 4
No new posts INCLUDE OMIT COND for Multiple values... DFSORT/ICETOOL 5
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