Match Fields from Two Datasets

prasaar · New User Joined: 11 Dec 2006 Posts: 21 Location: India

I have a flat file which has details of employee name. The longest employee name is 20 character. There are 500 records.

I have another flat file with 1500 employee name's and their date of birth. This list includes the 500 employees in the first dataset.

I want to do a comparison and get an output where I have the list of all 500 employees in the first dataset with the DOB retrieved from the 2nd Dataset.

Arun

Frank Yaeger · Posted: Wed Dec 20, 2006 10:21 pm

You can use the technique discussed in the "Join fields from two files on a key " Smart DFSORT Trick at:

www.ibm.com/servers/storage/support/software/sort/mvs/tricks/

If you need more specific help, show an example of the records in each input file and what you want for the output records. Give the starting position, length and format of each relevant field. Give the RECFM and LRECL of the input files.

prasaar · New User Joined: 11 Dec 2006 Posts: 21 Location: India

Hi Frank,

Thanks for the quick response.

The DS attributes for both the input DS's are as below:

Record format . . . : FB
Record length . . . : 132
Block size . . . . : 27984

The DS NAME.PS has the following records:

Frank Yaeger · Posted: Thu Dec 21, 2006 8:52 pm

This is actually simpler than I thought when reading your description. Here's a DFSORT/ICETOOL job that will do what you want. Be sure to concatenate AGE.PS before NAME.PS as shown.

prasaar · New User Joined: 11 Dec 2006 Posts: 21 Location: India

Hi Frank,

Thanks for the quick response & the jcl. When I try to implement the same cntl statements on other set of inputs, The sorting is not happening properly. I did change the record length and other crieteria.

Consider I have the following data in PARTS.PS(contains 50000 records)
Part-Name- Part# Qty
A50001AN 4000 02
A50001AN 0 02
A50001CT 4000 01
A50001CT 0 01
A50001MR 4000 09
A50001MR 0 09
AJ185MS912-4 0
AM50GS5014 0
892601P465 0 05
892601P466 0 05
123-456-789 1A 01

Part-Name=MaxLength=18, Some records contain "Hypen" along with letters & Numbers.
Part#=Maxlength=4, some records are alphanumeric
Qty=Maxlength=2, Number

And the DS Order.PS has the following data(Contains 10,000 records)

Part-Name
2138300-19
2138872
2162300
2241999
2281177-10
2281177-11
2281177-12
2293437-5
2293437-6

Part-Name=MaxLength=18, Some records contain "Hypen" along with letters & Numbers.

The Sorting crieteria is the same as the last example. I want all the data in ORDER.PS along with the information of part# & Qty. Some of the data in ORDER.PS may not be present in PART.PS

Cheers,
Arun Prasad

prasaar · New User Joined: 11 Dec 2006 Posts: 21 Location: India

HI Frank,

I apologize for the bad formating of record...for your convenience, in PARTS.PS.The part# seems mixed with part-name. From each string, the following values are part#. In teh actual DS, there are 4 blank spaces in between part-name & part#. What ever value follows part# pertains to Qty.
4000
0
4000
0
4000
0
0
0
0
1A

Frank Yaeger · Posted: Thu Dec 21, 2006 10:05 pm

I don't know what you mean by "The sorting is not happening properly" for the first example. The output records will be sorted by the Name field. If you want the output records sorted some other way, you have to tell me how you want them sorted.

I can't make heads or tails out of your second example. I don't see any matches between the two files on the Part-Name. In PARTS.PS the Part-Name values start with A, 8 or 1. In ORDER.PS, the Part-Name values start with 2. So where are the matches? You need to give me a good example and explain the sort order you want before I can help.

It would really help if you would use ubb code tags around your example records. Put <code> before the example lines, but use [] instead of <>. Put </code> after the example lines, but use [] instead of <>. Example:

<code>
line 1
line 2
</code>

with [ for < and ] for >.

That will let you line up the data the way it should actually look.

prasaar · New User Joined: 11 Dec 2006 Posts: 21 Location: India

Frank Yaeger · Posted: Fri Dec 22, 2006 9:35 pm

So you want the output records in the original order of the PARTS.PS file.

Here's a DFSORT/ICETOOL job for that. We just add a sequence number to the PARTS.PS records and sort the selected records on that sequence number to get them in that order.

prasaar · New User Joined: 11 Dec 2006 Posts: 21 Location: India

Hi Frank,

Merry Christmas.

Thanks a ton for the code. When I ran it, I got some errors. Currently we are running R14 DFSORT. I found that there was a PTF introduced in Dec 2004 to accept the Overlay function.

I've asked the Sys-Prog to check if PTF UQ95213 has been installed.

Again thanks a lot for the prompt response and the for the codes.

Cheers,
Arun Prasad
MVS Admin