Replace file1 column with file2 column data

lavakumar149 · New User Joined: 25 Jul 2013 Posts: 7 Location: India

Hi,

I need to replace file1 column with file2 column data. Can anyone give the jcl for this.

File1: RECFM=V,
RECLENGTH=4096

File 2: RECFM=FB,
RECLENGTH=80

o/p file: RECFM=V,
RECLENGTH=4096

File1 data:

expat · Posted: Tue Aug 06, 2013 4:36 pm

First of all, you need to explain the matching of the two files. Are there keys, if so what position, format and length of them. If not, what are the rules.

Second - which sort product and release level are you using, run the code below and post the SYSOUT segment from the ouput

Pandora-Box · Posted: Tue Aug 06, 2013 5:21 pm

Are the record numbers in Sync in File1 & File2?

Bill Woodger · Posted: Tue Aug 06, 2013 5:28 pm

Please use the Code tags.

It looks like you are alternating the File 2 values on File 1. Is that correct? Will there always be two records on File 2?

I'd generate symbols/SYMNAMES for the two values, identify odd/even records using a sequence number, (see here for an exampple of that) and just put the constant values on.

lavakumar149 · New User Joined: 25 Jul 2013 Posts: 7 Location: India

Hi expat,

1) Actually there are no keys to match in the files. file2 contains valid telephone numbers. in file1 TN's are masked. So, i need to replace these valid telephone numbers in the masking field of file1(6th-10 position).
Suppose file1 have 5 records and file2 has 2 records. i need to replace the file2 records

Bill Woodger · Posted: Tue Aug 06, 2013 5:48 pm

Please, again, use the Code tags. Please post SyncSort questions in the JCL forum, not in the DFSORT forum.

Did you read my suggestion?

lavakumar149 · New User Joined: 25 Jul 2013 Posts: 7 Location: India

Hi Bill Woodger,

you are correct. file2 values are replacing again if there are less records. Means we need to read file2 values and replace those in file1. if file2 records are finished then we need to read file2 again and start replacing.

Arun Raj · Posted: Wed Aug 07, 2013 1:11 pm

lavakumar149 · New User Joined: 25 Jul 2013 Posts: 7 Location: India

Hi Arun Raj,

it is not possible that file2 will always have only 2 records. But file2 will have less records than file1. So, i need to read file2 and replace file2 telephone numbers in file1(6:10 positon). if file2 records are finished and file1 is still have records then we need read file2 again and replace in file1.

Please look into the following for your reference.

File1: RECFM=V,
RECLENGTH=4096

File 2: RECFM=FB,
RECLENGTH=80

o/p file: RECFM=V,
RECLENGTH=4096

File1 data:
Code:
11111##########AAAAA
22222##########BBBBBBBBB
33333##########CCCCCCCCCC
44444##########DDDDDDDDDDDD
55555##########EEEEEEEEEEEEEEEEEE

File2 data:
Code:
0900111111
0900222222

I want to replace the file2 telephone numbers in file1
6th position(6-10-->##########), remaining data should be same as file1.

o/p file result should come like following.

Code:
111110900111111AAAAA
222220900222222BBBBBBBBB
333330900111111CCCCCCCCCC
444440900222222DDDDDDDDDDDD
555550900111111EEEEEEEEEEEEEEEEEE

Note: there are no keys in both files to match. need to read sequentially and replace.

Can you provide the jcl for above requirement. Or suggest me, if there is any other way to achieve this.

[/code]

Nic Clouston · Posted: Wed Aug 07, 2013 3:00 pm

The JCL is simple - the sort control records are another matter altogether. Which do you want?

lavakumar149 · New User Joined: 25 Jul 2013 Posts: 7 Location: India

Hi Nic,

Provide any, which is not complex.[/code]

Nic Clouston · Posted: Wed Aug 07, 2013 4:53 pm

Well, I could provide the JCL but it is well documented in the manual - you need a jobcard, DD cards for input file, output file and messages and a SYSIN card for where your sort control cards go. all fairly standard JCL. I cannot help with the sort control cards though.

Bill Woodger · Posted: Wed Aug 07, 2013 4:58 pm

lavakumar149,

You've finally submitted under Arun Raj's questioning.

You absolutely never "open the file again and start reading it again", even when writing a program where you could actually do that.

Look at CHANGE. Work out how to "generate" a CHANGE statement and other things necessary.

Process is to set a sequence number which will be replace by your telephone number. RESTART the sequence number according to the number of telephone numbers you have. Use CHANGE to change each successive sequence number to each successive telephone number.

expat · Posted: Wed Aug 07, 2013 5:03 pm

Perhaps a curious mind .............. but

Why would you want to propogate the same telephone number across numerous records in a file with no way of matching a number against an account or any other relevant field in the receiving file.

Arun Raj · Posted: Wed Aug 07, 2013 5:11 pm

With sort it is doable, but might take too many passes. It could be better writing some code for this.

Pandora-Box · Posted: Wed Aug 07, 2013 5:31 pm

lavakumar149 · New User Joined: 25 Jul 2013 Posts: 7 Location: India

Hi,

file2 contains valid telephone numbers which are unloaded from a table. in file1 TN's are masked(which comes from production). So, i need to replace these valid telephone numbers in the masking field of file1(6th-10 position).

Final requirement is file1 should have the valid telephone numbers similar to file2 telephone numbers.

Note: file1 should not contain masking data for telephone number position.

Suppose if file2 has 50 telephone numbers. it is not mandatory to
replace all 50 numbers in file1. if we replace first 10 telephone numbers repeatedly also fine.

File1 should have the valid telephone numbers like fil2. this is the o/p result i am expecting.
Can anyone provide any sort card or suggest me if there is any better way to achieve this. [/code]

Arun Raj · Posted: Thu Aug 08, 2013 11:19 am

lavakumar149 · New User Joined: 25 Jul 2013 Posts: 7 Location: India

Hi Arun,

I already got the result through a cobol program. But, I am looking whether any sort logic is there to do this in a smart way.

Can we do this in another way like following.
compare file1(6:10 position) values with file2(1:10) position values. if no match found then replace file2 telephone number in file1(6:10 position).
As file1 has masking data like ######### match will never found.

If this is possible, can you provide any sort/icetool logic to replace when no match found.

File1: RECFM=V,
RECLENGTH=4096

File 2: RECFM=FB,
RECLENGTH=80

o/p file: RECFM=V,
RECLENGTH=4096

File1 data:

Pandora-Box · Posted: Thu Aug 08, 2013 12:38 pm

I dont think this could be achieved in sort

If you are able to group the records for the count of records in File2

So then you could join through joinkeys

Arun Raj · Posted: Thu Aug 08, 2013 12:41 pm

Nic Clouston · Posted: Thu Aug 08, 2013 1:44 pm

As all you are trying to do is "mask the mask" why not just hard-code one set of digits - they do not have to be a real telephone number. Then it is just a find mask/replace with string.