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
 

 

Subtracting values from 2 files with identical keys

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
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    Post subject: Subtracting values from 2 files with identical keys
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

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7315

PostPosted: Thu Apr 12, 2012 1:15 pm    Post subject: Reply to: Subtracting values from 2 files with identical key
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    Post subject: Reply to: Subtracting values from 2 files with identical key
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

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7315

PostPosted: Thu Apr 12, 2012 5:24 pm    Post subject: Reply to: Subtracting values from 2 files with identical key
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    Post subject:
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: 578
Location: USA

PostPosted: Thu Apr 12, 2012 7:42 pm    Post subject:
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    Post subject: Reply to: Subtracting values from 2 files with identical key
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    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 BI,TO=ZD Changing Values Tyler Dunbar DFSORT/ICETOOL 1 Tue Jul 25, 2017 12:12 am
No new posts getting three output files out of JOI... migusd SYNCSORT 7 Mon Jun 05, 2017 11:30 pm
No new posts Sorting of hex values Saurabh_mi DFSORT/ICETOOL 11 Thu May 25, 2017 3:49 pm
No new posts Replace values in the input data Vikas Maharnawar DFSORT/ICETOOL 10 Thu May 11, 2017 2:18 pm
No new posts Unable to create multiple files using... mbattu COBOL Programming 3 Fri May 05, 2017 5:35 pm


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