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

Merge 2 files ( same layout ) into one..


IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Squeak6

New User


Joined: 04 Aug 2005
Posts: 36

PostPosted: Tue Feb 07, 2012 7:59 pm
Reply with quote

** I had to create a new topic ~ fresh start **


I apologize if I was not clear earlier.. I have 2 files with the same layout ( file A & file B) which I’d like to combine into 1 ( file C ).. I need to know how to add the fields below ( not all together )

EX. Add overtime hours from both files creating a new combined overtime hours from both files.. do this for each one of the packed files below.. all while retaining the same layout.

Both files have a length of 120.. I included the position and length of each filed.


Code:
FIELD NAME                                                 START  END  LEN
05  MO-OVERTIME-HRS                   PIC S9(7)V9(2) COMP-3. 051  055  005
05  MO-OVERTIME-AMT                   PIC S9(9)V9(2) COMP-3. 056  061  006
05  MO-OVERTIME-RETRO-AMT             PIC S9(9)V9(2) COMP-3. 062  067  006
05  MO-YTD-OVT-HRS                    PIC S9(9)V9(2) COMP-3. 068  073  006
05  MO-YTD-OVT-AMT                    PIC S9(11)V9(2) COMP-3 074  080  007
05  MO-YTD-OVT-RETRO-AMT              PIC S9(11)V9(2) COMP-3 081  087  007
Back to top
View user's profile Send private message
elango_K

New User


Joined: 18 Aug 2011
Posts: 44
Location: India

PostPosted: Tue Feb 07, 2012 8:14 pm
Reply with quote

What is the key on these files?
How do we know which record has to be added to ehich record???
Back to top
View user's profile Send private message
Squeak6

New User


Joined: 04 Aug 2005
Posts: 36

PostPosted: Tue Feb 07, 2012 8:28 pm
Reply with quote

the key is SSN

Code:
FIELD NAME                                                 START  END  LEN
     05  MO-SSN                        PIC 9(9).             016  024  009


** I've done something like this before using:

INREC OVERLAY=
SUM FIELDS=
OUTREC OVERLAY=

but I've never had more than one filed and never had packed data **
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Tue Feb 07, 2012 11:41 pm
Reply with quote

Squeak6,

Did you even try to answer the questions I asked previously? What is the point of opening another topic with no explanation? How hard is to answer a simple q?

What do you do with unmatched records from file 1 and file 2?

If you need help, then answer ALL the questions and post a sample data of both input and output.
Back to top
View user's profile Send private message
Squeak6

New User


Joined: 04 Aug 2005
Posts: 36

PostPosted: Wed Feb 08, 2012 12:16 am
Reply with quote

the reason I did not show an example of the file is because the data is packed.. packed data would not have shown.. I felt by showing the file layout would be the best approach..

was I wrong? ( please advise.. )
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Wed Feb 08, 2012 12:19 am
Reply with quote

can You browse it under ISPF ...
use hex on and paste using the code tags
like ...
www.ibmmainframes.com/viewtopic.php?t=57443&highlight=

the HEX display is for VIEW but the same approach holds for BROWSE
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA

PostPosted: Wed Feb 08, 2012 12:49 am
Reply with quote

Squeak6,

You can show packed data in the same way you show character data - just indicate it's packed. For example:

I have 2-byte PD fields in positions 1-3 and 6-8 with values like this:

Code:

+123  -989
-002  +005


It's still not clear what you want to do. If you want to get the totals for all of your PD values, you can use TRAILER1 with TOT for PD. If you want to get the totals for each key value, you can use SUM with PD, or SECTIONS with TRAILER with TOT for PD. If you want more specific help, you need to clarify what you want to do:

Please show an example of the records in each input file (relevant fields only) and what you expect for output. Explain the "rules" for getting from input to output. Give the starting position, length and format of each relevant field. Give the RECFM and LRECL of the input files. If file1 can have duplicates within it, show that in your example. If file2 can have duplicates within it, show that in your example.
Back to top
View user's profile Send private message
Squeak6

New User


Joined: 04 Aug 2005
Posts: 36

PostPosted: Wed Feb 08, 2012 1:00 am
Reply with quote

File A ( file B is exactly the same.. the amount fields will have different amounts..)

position 1 - 72
Code:
360aaaaaaaabbbb123456789jones, JORGE L      000001   Í    i *          °
FFF888888888888FFFFFFFFF9998A64DDDCC4D444444FFFFFF0007000084500000000019
36011111111222212345678916552B016975030000000000010025C00190C00000C00060


Positions 73-120
Code:
     Äq      Ã 00101016AC00022618               
000016900000262FFFFFFFFCCFFFFFFFF000000000000000
C000138C000006C001010161300022618000000000000000



below are the like fields I would like to add together.
Code:
MO-OVERTIME-HRS                   PIC S9(7)V9(2) COMP-3. 051  055  005
MO-OVERTIME-AMT                   PIC S9(9)V9(2) COMP-3. 056  061  006
MO-OVERTIME-RETRO-AMT             PIC S9(9)V9(2) COMP-3. 062  067  006

MO-YTD-OVT-HRS                    PIC S9(9)V9(2) COMP-3. 068  073  006
MO-YTD-OVT-AMT                    PIC S9(11)V9(2) COMP-3 074  080  007
MO-YTD-OVT-RETRO-AMT              PIC S9(11)V9(2) COMP-3 081  087  007


I want to add the like fields in above layout from A & B . All other fieds will just carry over from file A.


layout of both A&B
Code:
    FIELD NAME                                                 START  END  LEN
                                                                             
01  MO-INDEX-OVERTIME-REC.                                       001  120  120
    05  MO-INDEX-KEY.                                            001  024  024
        10  MO-DEPARTMENT                 PIC 9(3).              001  003  003
        10  MO-INDEX-CODE                 PIC X(12).             004  015  012
        10  MO-SSN                        PIC 9(9).              016  024  009
        10  MO-SSN-R                                             016  024  009
            REDEFINES MO-SSN.                                                 
            15  MO-SSN-1-3                PIC 9(3).              016  018  003
            15  MO-SSN-4-5                PIC 9(2).              019  020  002
            15  MO-SSN-6-9                PIC 9(4).              021  024  004
    05  MO-NAME                           PIC X(20).             025  044  020
    05  MO-OCCP-CODE                      PIC 9(6).              045  050  006
    05  MO-OVERTIME-HRS                   PIC S9(7)V9(2) COMP-3. 051  055  005
    05  MO-OVERTIME-AMT                   PIC S9(9)V9(2) COMP-3. 056  061  006
    05  MO-OVERTIME-RETRO-AMT             PIC S9(9)V9(2) COMP-3. 062  067  006   
05  MO-YTD-OVT-HRS                    PIC S9(9)V9(2) COMP-3. 068  073  006
   05  MO-YTD-OVT-AMT                    PIC S9(11)V9(2) COMP-3 074  080  007
   05  MO-YTD-OVT-RETRO-AMT              PIC S9(11)V9(2) COMP-3 081  087  007
   05  MO-DDL                            PIC 9(8).              088  095  008
   05  MO-DDL-R                                                 088  095  008
       REDEFINES MO-DDL.                                                     
       10  MO-DDL-1-3                    PIC 9(3).              088  090  003
       10  MO-DDL-4-5                    PIC 9(2).              091  092  002
       10  MO-DDL-6-8                    PIC 9(3).              093  095  003
   05  MO-EMPL-STATUS                    PIC X(2).              096  097  002
   05  MO-EMPID                          PIC 9(08).             098  105  008
   05  FILLER                            PIC X(15).             106  120  015


( somethimes its hard to type what you're trying to do.. )
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA

PostPosted: Wed Feb 08, 2012 1:17 am
Reply with quote

This is like pulling teeth. I wanted to see input values in multiple records and the expected output values like this:

Code:

Input 1:

AAAA 10 20
AAAA 20 05
BBBB 05 18
CCCC 08 06

Input 2:

AAAA 15 05
BBBB 09 22


Expected output?

I still don't know what you want to do.

For the fields to be added:

Do you want to get ONE total for ALL of the records, or do you want to get ONE total for EACH key value? If the latter, what is the position, length and format of the key and are the keys in each input file already in sorted order (as implied by your use of the term MERGE)?
Back to top
View user's profile Send private message
Squeak6

New User


Joined: 04 Aug 2005
Posts: 36

PostPosted: Wed Feb 08, 2012 1:39 am
Reply with quote

I hope this helps.. (Frank I wish u had a help line # icon_idea.gif )

Code:
FILE A                             
123456789 ABCDEFGHI100 -000000010 00
987654321 ABCDEFGHI101 -000000020 00
123456123 ABCDEFGHI102 -000000030 00
                                   
                                   
FILE B                             
123456789 XYZFDJFNI201 -000000010 00
987654321 XYZFWFNKI301 -000000020 00
                                   
FILE C                             
123456789 ABCDEFGHI100 -000000020 00
987654321 ABCDEFGHI101 -000000040 00
123456123 ABCDEFGHI102 -000000060 00
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK

PostPosted: Wed Feb 08, 2012 2:12 am
Reply with quote

I do not see how you get -60 for the last line - surely it is -30 as you only have one record for 123456123
Back to top
View user's profile Send private message
Squeak6

New User


Joined: 04 Aug 2005
Posts: 36

PostPosted: Wed Feb 08, 2012 2:27 am
Reply with quote

Nic Clouston wrote:
I do not see how you get -60 for the last line - surely it is -30 as you only have one record for 123456123


correct..!!!
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA

PostPosted: Wed Feb 08, 2012 2:41 am
Reply with quote

Wow, you really are bad at describing what you want. icon_cry.gif

Assuming you want to add the PD fields for each key and that the keys are not in sorted order:

Just concatenate the two files as SORTIN DD, SORT on the key, and SUM on the PD fields you want totalled, e.g.

Code:

...
//SORTIN DD DSN=...  input file1
//             DD DSN=...  input file2
//SORTOUT DD DSN=...  output file
//SYSIN DD *
   OPTION EQUALS
   SORT FIELDS=(p,m,f,A)
   SUM FIELDS=(p1,m1,PD,p2,m2,PD,...)
/*


This will give you one record for each key with the totals for that key. The output records will be in sorted order by the key.

EQUALS will ensure that the other fields from the first record with each key are kept.

If you really want the records in some other order (as implied by your example), you'll need to explain what order you want them in.
Back to top
View user's profile Send private message
Squeak6

New User


Joined: 04 Aug 2005
Posts: 36

PostPosted: Wed Feb 08, 2012 3:01 am
Reply with quote

I got it to work..( checking totals just to make sure.. )

I was missing the options equals.. ( when I tried on my own.. )

Code:
*******************************************************************
*   THIS CODE WILL COMBINED DUPLICATE AMOUNTS FROM 2 FILES..     *
*******************************************************************
  OPTION EQUALS                                                   
  SORT FIELDS=(16,9,CH,A)                                         
  SUM FIELDS=(51,5,PD,56,6,PD,62,6,PD,68,6,PD,74,7,PD,81,7,PD)     


I had 4 records.. 2 on each test file.. ended up with 2.. and it took the informaiton I needed from the 1st file..
Back to top
View user's profile Send private message
Squeak6

New User


Joined: 04 Aug 2005
Posts: 36

PostPosted: Wed Feb 08, 2012 3:04 am
Reply with quote

(( Thank you.. ))
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA

PostPosted: Wed Feb 08, 2012 3:12 am
Reply with quote

Well, I'm glad we finally got that straightened out.
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 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 Merge two VSAM KSDS files into third ... JCL & VSAM 6
No new posts Joinkeys - 5 output files DFSORT/ICETOOL 7
No new posts How to append a PS file into multiple... JCL & VSAM 3
Search our Forums:

Back to Top