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
 

 

How to capture data from 2 files "column wise"
Goto page 1, 2  Next
 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
n.kirankumar
Warnings : 1

New User


Joined: 14 Feb 2008
Posts: 16
Location: Hyderabad

PostPosted: Fri Feb 15, 2008 12:46 pm    Post subject: How to capture data from 2 files "column wise"
Reply with quote

Hi All,

Please help me with the below issue:

I have 2 files with FB:

Data in file1 :
Code:
Name    SSN     Total           
Jim      0001    876           
Kim      0003    987           
Joe      0004    787
Hen      0002    098
Loe      0005    876       

Date in file2:
Code:
Name    SSN    Total   M1   M2   Mtl    D1   D2
Jim      0001   876    34   76   110    43   45   
Joe      0004   787    65   35   100    32   21
Kim      0003   987    55   45   100    41   43

Output file needs to be like below:
Code:
Name    SSN    Total    M1  Mtl   D1   
Jim      0001    876    34   76   43 
Hen      0002    098
Kim      0003    987    55   45   41   
Joe      0004    787    65   35   32 
Loe      0005    876 


The above 2 input files are not in sorted order.

Please let me know the solution for the above issue at the earliest.
Back to top
View user's profile Send private message

enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Fri Feb 15, 2008 2:22 pm    Post subject: Reply to: How to capture data from 2 files "column wise
Reply with quote

The issue has been debated many many times
a forum search on "two files match" will certaily provide You with
lots of useful pointers

if You are not able to find anything useful by Yourself
here is a pointer to a thread that resembles what You are looking for

http://www.ibmmainframes.com/viewtopic.php?t=27023&highlight=file+match
Back to top
View user's profile Send private message
ParagChouguley

Active User


Joined: 03 Feb 2007
Posts: 175
Location: PUNE(INDIA)

PostPosted: Fri Feb 15, 2008 2:57 pm    Post subject:
Reply with quote

Kiran,
Your requirement is not so clear. Still if I could have understood it correctly, here is a JCL. Also you haven't mentioned properly which fields you want in output, I'm displaying all of them. You can change last OUTREC statement as per your requirement.

Code:

//S1      EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG  DD SYSOUT=*
//*
//IN1     DD *
NAME SSN TOTAL
JIM 0001 876
KIM 0003 987
JOE 0004 787
HEN 0002 098
LOE 0005 876
/*
//IN2     DD *
NAME SSN TOTAL M1 M2 MTL D1 D2
JIM 0001 876   34 76 110 43 45
JOE 0004 787   65 35 100 32 21
KIM 0003 987   55 45 100 41 43
/*
//TEMP1    DD DSN=&&TEMP1,DISP=(MOD,PASS,DELETE),
//  UNIT=SYSDA,SPACE=(CYL,(5,5))
//TEMP2    DD DSN=&&TEMP2,DISP=(,PASS,DELETE)
//   UNIT=SYSDA,SPACE=(CYL,(5,5))
//OUT1    DD DSN=...  output file
//TOOLIN  DD *
COPY FROM(IN1) TO(TEMP1) USING(SRT1)
COPY FROM(IN2) TO(TEMP1) USING(SRT2)
SPLICE FROM(TEMP1) TO(TEMP2) ON(1,14,CH) WITH(15,17) -
    KEEPNODUPS USING(SRT3)
SORT FROM(TEMP2) TO(OUT1) USING(SRT4)
/*
//SRT1CNTL DD *
  OUTREC BUILD=(1,14,16X)
/*
//SRT2CNTL DD *
  OUTREC BUILD=(1,30)
/*
//SRT3CNTL DD *
  OUTFIL FNAMES=TEMP2,
    IFTHEN=(WHEN=(1,4,CH,EQ,C'NAME'),
      BUILD=(C'A',1,30)),
    IFTHEN=(WHEN=NONE,
      BUILD=(C'B',1,30))
/*
//SRT4CNTL DD *
  SORT FIELDS=(1,1,CH,A,6,4,ZD,A)
  OUTREC BUILD=(2,30)
/*


Output:
Code:

NAME SSN TOTAL M1 M2 MTL D1 D2 
JIM 0001 876   34 76 110 43 45 
HEN 0002 098                   
KIM 0003 987   55 45 100 41 43 
JOE 0004 787   65 35 100 32 21 
LOE 0005 876                   



--Parag
Back to top
View user's profile Send private message
n.kirankumar
Warnings : 1

New User


Joined: 14 Feb 2008
Posts: 16
Location: Hyderabad

PostPosted: Fri Feb 15, 2008 3:22 pm    Post subject:
Reply with quote

Hi Parag,

Sorry If I haven't explained you my requirement properly.
Your assumption was right.

The output should not have all the fields"NAME SSN TOTAL M1 M2 MTL D1 D2 "

But the Output file should have only "Name" "SSN" " Total" "M1" "Mtl" and "D1"

Please let me know if we can get the output of the above required fields only.
Back to top
View user's profile Send private message
ParagChouguley

Active User


Joined: 03 Feb 2007
Posts: 175
Location: PUNE(INDIA)

PostPosted: Fri Feb 15, 2008 4:25 pm    Post subject:
Reply with quote

Then change last sort card SRT4CNTL
to
Code:

//SRT4CNTL DD *                   
    SORT FIELDS=(1,1,CH,A,6,4,ZD,A)
    OUTREC FIELDS=(2,18,23,6)     
/*                                 


which will be able to give you following output
Code:

NAME SSN TOTAL M1 MTL D1
JIM 0001 876   34 110 43
HEN 0002 098           
KIM 0003 987   55 100 41
JOE 0004 787   65 100 32
LOE 0005 876           


--Parag
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Fri Feb 15, 2008 4:29 pm    Post subject: Reply to: How to capture data from 2 files "column wise
Reply with quote

Quote:
Please let me know if we can get the output of the above required fields only.


Yes it can be done,
but it would be very rewarding for the responders if You' d carry on a little bit of research on Your own

AS I wrote in my previous post there are many samples in this forum on how to build an output record from non contiguous fields
Back to top
View user's profile Send private message
acevedo

Active User


Joined: 11 May 2005
Posts: 344
Location: Spain

PostPosted: Fri Feb 15, 2008 4:49 pm    Post subject:
Reply with quote

enrico... this reminds me about
give a man a fish and he will eat for a day....teach a man to fish and he will eat for a lifetime.
Back to top
View user's profile Send private message
n.kirankumar
Warnings : 1

New User


Joined: 14 Feb 2008
Posts: 16
Location: Hyderabad

PostPosted: Fri Feb 15, 2008 6:03 pm    Post subject: Thanks Parag
Reply with quote

Thanks for response...

Can you please brief me how the ICETOOL and DFSORT control statements work.
Back to top
View user's profile Send private message
n.kirankumar
Warnings : 1

New User


Joined: 14 Feb 2008
Posts: 16
Location: Hyderabad

PostPosted: Fri Feb 15, 2008 9:05 pm    Post subject:
Reply with quote

Hi Priyesh..

Thanks for overwhelming response for my query..
I am being an amateur to this Platform and forum, I am trying to learn as much as I can.
Most importantly,the reason I put in this query was I had a High Priority issue on this, which needs to be solved within a short span of time.
We respect encouragment from people like you.
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


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

PostPosted: Fri Feb 15, 2008 11:12 pm    Post subject:
Reply with quote

Kiran,

If you're not familiar with DFSORT and DFSORT's ICETOOL, I'd suggest reading through "z/OS DFSORT: Getting Started". It's an excellent tutorial, with lots of examples, that will show you how to use DFSORT, DFSORT's ICETOOL and DFSORT Symbols. You can access it online, along with all of the other DFSORT books, from:

www.ibm.com/servers/storage/support/software/sort/mvs/srtmpub.html
Back to top
View user's profile Send private message
n.kirankumar
Warnings : 1

New User


Joined: 14 Feb 2008
Posts: 16
Location: Hyderabad

PostPosted: Sat Feb 16, 2008 6:29 pm    Post subject:
Reply with quote

Hi Parag,

Thanks for your response.

There is change in my requirement now.

The input file 1 has a length of 120 chars,whose record format is FB.
The input file 2 has a length of 150 chars,whose record format is also FB.

Data in file1

Indicator SSN NAME Dept
2 0076 Nick 09
5 0089 Leo 23
6 0021 Bill 87
7 7898 Neo 08
1 9879 Phil 89

Data in file2

Year Amt1 SSN Amt2 M1 M2
2008 679 9879 980 12 13
2007 765 0021 987 76 32
2008 987 0076 980 87 16

The above input file1 and file2 must be compared on KEY(i.e.,SSN) and the when both the SSNs match then corresponding values M1 and M2 needs to moved to output file.If file1 SSN is not present in file2 ,then only file1 record needs to be written to be output file.

The Output file should look something like below.

Data in Output file .

Indicator SSN NAME Dept M1 M2
6 0021 Bill 87 76 32
2 0076 Nick 09 87 16
5 0089 Leo 23
7 7898 Neo 08
1 9879 Phil 89 12 13

I will pleased to get a quick response on this.
Awaiting response.
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Sat Feb 16, 2008 6:47 pm    Post subject: Reply to: How to capture data from 2 files "column wise
Reply with quote

Quote:
Most importantly,the reason I put in this query was I had a High Priority issue on this, which needs to be solved within a short span of time.

Quote:
I will pleased to get a quick response on this.
Awaiting response.


Remember the urgency is a problem only on Your side
You should give us better reasons than just Your urgency,
for us to take time to please You with a quick reply icon_lol.gif

do You realize that the problem is exactly the same as before, only with the column offsets changed?
it would be nice if at least You would have tried

But maybe that' s just homework icon_confused.gif

a very smart politician once said
Quote:
To think evil is a sin, but most of the time You are right
Back to top
View user's profile Send private message
n.kirankumar
Warnings : 1

New User


Joined: 14 Feb 2008
Posts: 16
Location: Hyderabad

PostPosted: Sat Feb 16, 2008 7:32 pm    Post subject: Re: Reply to: How to capture data from 2 files "column
Reply with quote

Hi Enrico,

Yes,I agree that urgency is from my end but still I have been trying to figure out this problem for last 3 days.
One of my colleagues suggested post a query on this forum,where I can get a glimpse of the solution.
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Sat Feb 16, 2008 7:56 pm    Post subject: Reply to: How to capture data from 2 files "column wise
Reply with quote

Why don' t You try to help yourself a little bit,
rather than waiting for a diapering solution form the forum icon_lol.gif
it' s saturday and for most people is not a working day

hint...
look at the record layout from Your first question,
look at the record layout of the last question
meditate about the analogy of your last question with the first solution given to you
build the new ICETOOL parameters with the new fields positions/offsets
Back to top
View user's profile Send private message
ParagChouguley

Active User


Joined: 03 Feb 2007
Posts: 175
Location: PUNE(INDIA)

PostPosted: Sat Feb 16, 2008 9:09 pm    Post subject:
Reply with quote

Hi Kiran,
Could you please use Code tag to show your Input and output, which will show exact position and length of the fields. And also you can give a try to modify the jcl given by me to suit your requirements. The forum members are always there to help you in that !
icon_biggrin.gif

--Parag
Back to top
View user's profile Send private message
n.kirankumar
Warnings : 1

New User


Joined: 14 Feb 2008
Posts: 16
Location: Hyderabad

PostPosted: Sat Feb 16, 2008 9:43 pm    Post subject:
Reply with quote

Hi Parag,

Thanks for your response.I have been trying the Jcl which you posted last night.Its working fine but I m facing problem in last SORT card SRT4CNTL.
I tried putting all efoort to get the output.
But I m not able to find the problem.
Can you please help me with the below requirement.

Input file1

Code:
Indicator SSN    NAME   Dept
    2     0076   Nick   09
    5     0089   Leo    23
    6     0021   Bill   87
    7     7898   Neo    08
    1     9879   Phil   89


Input file2

Code:
Year Amt1 SSN   Amt2 M1 M2
2008 679  9879  980  12 13
2007 765  0021  987  76 32
2008 987  0076  980  87 16


The above input file1 and file2 must be compared on KEY(i.e.,SSN) and the when both the SSNs match then corresponding values M1 and M2 needs to moved to output file.If file1 SSN is not present in file2 ,then only file1 record needs to be written to be output file.

The Output file should look something like below.

Data in Output file .

Code:
Indicator SSN  NAME Dept M1 M2
     6    0021 Bill  87  76 32
     2    0076 Nick  09  87 16
     5    0089 Leo   23
     7    7898 Neo   08
     1    9879 Phil  89  12 13
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Sat Feb 16, 2008 9:45 pm    Post subject: Reply to: How to capture data from 2 files "column wise
Reply with quote

Quote:
facing problem in last SORT card SRT4CNTL.


if You had posted the job sysout it would have been easier for everybody to help You
Back to top
View user's profile Send private message
n.kirankumar
Warnings : 1

New User


Joined: 14 Feb 2008
Posts: 16
Location: Hyderabad

PostPosted: Sun Feb 17, 2008 5:48 pm    Post subject:
Reply with quote

Hi Parag,

I m just getting the ouput in sorted order but not as per my requirement and a character "B" is appended in the 1st column of the record in the output file.

Can please tell me how sort card#3 SRT3CNTL works.

I tried finding it on the forum and manuals.But I am not able to find one such case.

Is comparison done on column names in different files?
If I want to compare fields in different files and which are at different positions.That is, field name RR-SSN(Positioned at 2) of file1 and field MM-SSN(Positioned at 5) of file2.How do we put this in the condition "IFTHEN=(WHEN=(condition))".And If there is no match,how condition can be?

I just want to confirm one thing below:

Code:
//TOOLIN  DD *
COPY FROM(IN1) TO(TEMP1) USING(SRT1)
COPY FROM(IN2) TO(TEMP1) USING(SRT2)
SPLICE FROM(TEMP1) TO(TEMP2) ON(1,14,CH) WITH(15,17) -
    KEEPNODUPS USING(SRT3)
SORT FROM(TEMP2) TO(OUT1) USING(SRT4)


Per above, are we copying IN1 and IN2 to same temp file (TEMP1).


ParagChouguley wrote:
Hi Kiran,
Could you please use Code tag to show your Input and output, which will show exact position and length of the fields. And also you can give a try to modify the jcl given by me to suit your requirements. The forum members are always there to help you in that !
icon_biggrin.gif

--Parag
Back to top
View user's profile Send private message
ParagChouguley

Active User


Joined: 03 Feb 2007
Posts: 175
Location: PUNE(INDIA)

PostPosted: Mon Feb 18, 2008 10:58 am    Post subject:
Reply with quote

Hi Kiran,
Its a good thing that you are using proper tags for code part and also you did some research ! Great ! icon_biggrin.gif

Now, you say
Quote:

Per above, are we copying IN1 and IN2 to same temp file (TEMP1)


Yes, we are copying data from both the input files to a single temporary file and rearranging it so as SPLICE operator can compare it !

You also have many other questions, but it's tough for me to answer them.

Could you please use COLS command before you copy your input and expected output here. So that the forum members can get exact idea about the position and length of fields.

--Parag
Back to top
View user's profile Send private message
n.kirankumar
Warnings : 1

New User


Joined: 14 Feb 2008
Posts: 16
Location: Hyderabad

PostPosted: Mon Feb 18, 2008 11:55 am    Post subject: Can we perform this in IFTHEN condition
Reply with quote

Hi,

If (2,9) of file1 = (5,9) of file2 then I would like to append (93,12) of file2 to the end of first file record and that record needs to be written to output file.
If (2,9) of first file is not equal to (5,9) second file then I would like to write only the record of file1 to output file.

Can you please let me know how the above condition can be handled through IFTHEN condition.

Or do we have any other way for the above.

I am not able find any one such case in the forum or manual.
Awaiting reply.
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
Goto page 1, 2  Next
Page 1 of 2

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Is the 'prompt' data for the DSLIST p... Willy Jensen TSO/ISPF 2 Tue Dec 06, 2016 4:38 am
This topic is locked: you cannot edit posts or make replies. How to move a long alphanumeric data ... lind sh COBOL Programming 8 Mon Dec 05, 2016 7:51 pm
No new posts High CPU consumption Job using IAM fi... aswinir JCL & VSAM 8 Thu Dec 01, 2016 8:28 pm
No new posts ODPP(Optim Data privacy Provider) Iss... Rama kishore IBM Tools 1 Mon Nov 07, 2016 5:46 pm
No new posts SQZ pipe delimited file with 1 column... Rick Silvers DFSORT/ICETOOL 2 Sun Nov 06, 2016 8:11 pm


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